Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Torsten Zühlsdorff]

On 10.11.2015 07:23, Craig Ringer wrote:

On 10 November 2015 at 01:38, Jeff Janes  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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Craig Ringer]

On 11 November 2015 at 16:02, Torsten Zühlsdorff <
mailingli...@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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Greg Stark]

On Wed, Nov 11, 2015 at 6:57 PM, Gavin Flower
 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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Gavin Flower]

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

On Wed, Nov 11, 2015 at 6:57 PM, Gavin Flower
 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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Gavin Flower]

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



On 11 November 2015 at 16:02, Torsten Zühlsdorff 
> 
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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[德哥]

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"  wrote:
>On Mon, Nov 9, 2015 at 9:06 AM, Tom Lane  wrote:
>> =?GBK?B?tcK45w==?=  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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Jeff Janes]

On Mon, Nov 9, 2015 at 9:06 AM, Tom Lane  wrote:
> =?GBK?B?tcK45w==?=  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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Simon Riggs]

On 9 November 2015 at 17:06, Tom Lane  wrote:

> =?GBK?B?tcK45w==?=  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 Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

[HACKERS] can we add SKIP LOCKED to UPDATE?

[德哥]

HI, 
   PostgreSQL 9.5 added skip locked to select for update to improve concurrency 
performance, but why not add it to update sql?
   this is an application case, some body will update a tuple at the same time, 
so the RT for waiter is big, I use function and select for update nowait or 
advisory lock , can improve concurrency , but it's not very pure for developer.
 http://blog.163.com/digoal@126/blog/static/16387704020158149538415/


--
公益是一辈子的事,I'm Digoal,Just Do It.

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Tom Lane]

=?GBK?B?tcK45w==?=  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

Re: [HACKERS] can we add SKIP LOCKED to UPDATE?

[Craig Ringer]

On 10 November 2015 at 01:38, Jeff Janes  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