Thread: 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.
--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
德哥 <digoal@126.com> writes:
> PostgreSQL 9.5 added skip locked to select for update to improve concurrency performance, but why not add it to
updatesql?
Seems like you'd have unpredictable results from the update then.
regards, tom lane
On 9 November 2015 at 17:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
UPDATE
德哥 <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
COMMIT
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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:
> 德哥 <digoal@126.com> writes:
>> PostgreSQL 9.5 added skip locked to select for update to improve concurrency performance, but why not add it to
updatesql?
>
> 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
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
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 Mon, Nov 9, 2015 at 9:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 德哥 <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
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
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.
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
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
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???