On Thu, Jul 28, 2016 at 10:18 AM, Josh Berkus <josh@agliodbs.com> wrote:
> On 07/26/2016 01:16 AM, David Harrison wrote:
> where sps.sessionid = ses and
> sps.rankid = rank and
> ...
> pg_try_advisory_lock(seatid)
> order by s.row_number, s.seat_number_in_row
> limit 1
> for update of sps;
Don't you want pg_try_advisory_xact_lock (note "xact") here?
Otherwise you hold the advisory lock for the rest of the session,
unless you explicitly release it later.
> You appear to be trying to implement your own "SKIP LOCKED" (which you
> should maybe use instead).
+1
One difference between WHERE foo = 42 AND
pg_try_advisory_xact_lock(...) ... FOR UPDATE and WHERE foo = 42 ...
FOR UPDATE SKIP LOCKED is that the order of evaluation of the bits of
the WHERE clause linked by AND is probably undefined, so you could in
theory be acquiring advisory locks corresponding rows that don't have
foo = 42, depending on how the evaluator finished up processing that.
That could mess things up a bit for concurrent sessions. SKIP LOCKED
on the other hand operates after any WHERE clause filtering.
> I'm not sure this works as-is; SELECT FOR UPDATE with LIMIT/ORDER is
> always fairly tricky, and tends to block the whole set, not just the
> LIMITed row.
There are complications with ORDER BY <something that might be
concurrently updated> FOR UPDATE, because row locking happens later
than ordering and causes you to see newer values that still match the
WHERE clause. It gives you an isolation level more similar to READ
COMMITTED in non-snapshot based databases, except that the plan
implementing the ORDER BY didn't get the memo, and you'd have to fix
that with an outer query that sorts. But I don't see why it would
block the whole set: LockRows sits on top and only gets its hands on
tuples emitted by nodes below it, so if there is a LIMIT then how
could it lock anything outside the limited set of rows that are
returned?
--
Thomas Munro
http://www.enterprisedb.com