On Thu, Jul 28, 2016 at 10:18 AM, Josh Berkus <josh@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
