can we add SKIP LOCKED to UPDATE?

=?GBK?B?tcK45w==?= <digoal@126.com> writes:

PostgreSQL 9.5 added skip locked to select for update to improve concurrency performance, but why not add it to update sql?

Seems like you'd have unpredictable results from the update then.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 9 November 2015 at 17:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?GBK?B?tcK45w==?= <digoal@126.com> writes:

PostgreSQL 9.5 added skip locked to select for update to improve

concurrency performance, but why not add it to update sql?

Seems like you'd have unpredictable results from the update then.

True, but given the already restricted use case of SKIP LOCKED, the request
makes sense for the following

UPDATE ...
SKIP LOCKED
RETURNING xxx

would be better than

BEGIN

SELECT xxx
FOR UPDATE
SKIP LOCKED

UPDATE

COMMIT

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Nov 9, 2015 at 9:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?GBK?B?tcK45w==?= <digoal@126.com> writes:

PostgreSQL 9.5 added skip locked to select for update to improve concurrency performance, but why not add it to update sql?

Seems like you'd have unpredictable results from the update then.

But with use of RETURNING, you could at least know what those results
were and so could deal with the unpredictability.

I don't understand Digoal's use case (Google Translate does a poor job
on the linked page), but this would be handy in conjunction with LIMIT
(which also doesn't exist for UPDATE right now).

update work_queue set assigned='Jeff' where assigned is null and
skills_needed <@ '{whining,"breaking things"}' limit 1 skip locked
returning id, description

In 9.5 you will be able to do it with a subselect, but putting them
directly on the UPDATE would be easier to understand, and perhaps more
efficient to execute.

Cheers,

Jeff

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

HI,
My case is concurrency update one row(for exp 1000 client update the same row at the same time), and target is prevent waiting for waiters(quick return to client).
use advisory lock is a method, for quick return. but not good , must use function(to reduce consume between client-db network).
if update can skip locked in this case, performance can improve so much.

case exp (all session update the same row):
session a:
update tbl set x=x-1 where id=1 and x>0;
session b:
update tbl set x=x-1 where id=1 and x>0;
...
session x:
update tbl set x=x-1 where id=1 and x>0;

best regards,
digoal

At 2015-11-10 01:38:45, "Jeff Janes" <jeff.janes@gmail.com> wrote:

On 10 November 2015 at 01:38, Jeff Janes <jeff.janes@gmail.com> wrote:

this would be handy in conjunction with LIMIT
(which also doesn't exist for UPDATE right now).

... and, in turn, UPDATE ... ORDER BY ..., since LIMIT without ORDER
BY is usually not a great choice.

I'd quite like to see UPDATE ... ORDER BY for deadlock avoidance
anyway. Right now doing it really reliably seems to require a SELECT
... FOR UPDATE, then an UPDATE on the SELECTed tuples only. If you're
in READ COMMITTED you can't assume the UPDATE won't see new tuples
since the SELECT so you need to supply a key-list to the UPDATE
directly or via a wCTE.

I'm constantly surprised that people don't seem to hit deadlocks
between updates more often. I guess the number of cases where
multi-row updates on overlapping but non-identical sets of rows occur
concurrently must be fairly limited in practice.

Using SKIP LOCKED in a wCTE with an UPDATE is clunkier but not that
bad. So I don't think it's drastically important, but it would be
nice.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10.11.2015 07:23, Craig Ringer wrote:

On 10 November 2015 at 01:38, Jeff Janes <jeff.janes@gmail.com> wrote:

this would be handy in conjunction with LIMIT
(which also doesn't exist for UPDATE right now).

... and, in turn, UPDATE ... ORDER BY ..., since LIMIT without ORDER
BY is usually not a great choice.

I'd quite like to see UPDATE ... ORDER BY for deadlock avoidance
anyway. Right now doing it really reliably seems to require a SELECT
... FOR UPDATE, then an UPDATE on the SELECTed tuples only. If you're
in READ COMMITTED you can't assume the UPDATE won't see new tuples
since the SELECT so you need to supply a key-list to the UPDATE
directly or via a wCTE.

I'm constantly surprised that people don't seem to hit deadlocks
between updates more often. I guess the number of cases where
multi-row updates on overlapping but non-identical sets of rows occur
concurrently must be fairly limited in practice.

From my experience most databases are just to small. There operation
finished before there could be a deadlock. Same for race conditions -
most developer don't know them, because the never stumbled about them. I
am matching regularly discussions if a database is already to big when
holding 10.000 records in the whole cluster...

Most time it is relatively predictable if an application will hit such a
problem or not. But of course you should make it right.

Using SKIP LOCKED in a wCTE with an UPDATE is clunkier but not that
bad. So I don't think it's drastically important, but it would be
nice.

This is my opinion too.

Greetings,
Torsten

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 11 November 2015 at 16:02, Torsten Zühlsdorff <
mailinglists@toco-domains.de> wrote:

From my experience most databases are just tpo small. Their operations
finish before there can be a deadlock. Same for race conditions - most
developer don't know about them, because they never stumbled upon them. I
am matching regularly discussions if a database is already to big when
holding 10.000 records in the whole cluster...

Ha. Yes. So true.

I see Stack Overflow posts where somebody explains that their query takes
ages on their Huge!!1! database. Then it turns out the query takes 0.2
seconds on a 400MB table.

Huge. Right.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12/11/15 02:07, Craig Ringer wrote:

On 11 November 2015 at 16:02, Torsten Zühlsdorff
<mailinglists@toco-domains.de <mailto:mailinglists@toco-domains.de>>
wrote:

From my experience most databases are just tpo small. Their
operations finish before there can be a deadlock. Same for race
conditions - most developer don't know about them, because they
never stumbled upon them. I am matching regularly discussions if a
database is already to big when holding 10.000 records in the
whole cluster...

Ha. Yes. So true.

I see Stack Overflow posts where somebody explains that their query
takes ages on their Huge!!1! database. Then it turns out the query
takes 0.2 seconds on a 400MB table.

Huge. Right.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

I'll say its huge.

Don't you realize that 400MB is over 4 million of the old 100Kb floppy
disks, and even with the new big 1.44MB 3.5 " disks, you'd need about 280!!!

Though, I suspect that the people who are saying that 400MB is huge,
never actually used those floppies I mentioned above.

Now-a-days I would not regard 400GB as huge, even though when I started
programming, MainFrames often had less than 1MB of core memory, and the
big 12" tape reels could hold a max of 35MB of data. Now I'm sitting
sitting at a Linux box were even my SSD has hundreds of times the
storage (let alone the capacity of my HD's) the entire New Zealand Post
Office had in 1980! How times change...

The reality, is that people tend to compare things in their direct
experience, and don't have a feeling for the 'size' of the computers
they use in terms of storage & processing power. The above mainframe I
mentioned (an ICL 4/72) had washing machine sized boxes each with a 60MB
disk - everything was impressively sized, now you can fit a TB HD in a
match box.

Cheers,
Gavin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Nov 11, 2015 at 6:57 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

Don't you realize that 400MB is over 4 million of the old 100Kb floppy
disks, and even with the new big 1.44MB 3.5 " disks, you'd need about 280!!!

Don't be silly. It's only four thousand 100Kb floppies.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/11/15 13:52, Greg Stark wrote:

On Wed, Nov 11, 2015 at 6:57 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

Don't you realize that 400MB is over 4 million of the old 100Kb floppy
disks, and even with the new big 1.44MB 3.5 " disks, you'd need about 280!!!

Don't be silly. It's only four thousand 100Kb floppies.

You're right, of course!

So you won't mind helping me back up 400MB on 100 kB floppies then???

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers