Re: can we add SKIP LOCKED to UPDATE?

On 10.11.2015 07:23, Craig Ringer wrote:
> On 10 November 2015 at 01:38, Jeff Janes <jeff(dot)janes(at)gmail(dot)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