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
