Thread

1. Re: Locking issue

   Josh Berkus <josh@agliodbs.com> — 2016-07-27T22:18:25Z

   On 07/26/2016 01:16 AM, David Harrison wrote:
   > Hi Josh,
   > 
   > Attached is the function and below the query that calls it, below that the result of SELECT version();
   > 
   > SELECT tl_guest_list('13313880', '174880', null, '151094636600', null, null);
   > 
   > 
   > 
   > 
   > 
   > "PostgreSQL 8.4.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit”
   > 
   > We have a job queue manager (beanstalkd) that we push jobs onto for it to process, previously there was only one worker so tl_guest_list would only get called once at a time, after execution it would return the results and the next job would run firing tl_guest_list again. 
   > 
   > I’ve now upped the number of workers to 10 so it could be that 10 jobs pushed into the queue are all processed concurrently. Firing tl_guest_list up to 10 times at the same time. I expect that the update on ste_seatspersessions is locking the table and I expect the function to wait at the select on ste_seatspersessions. However the function is processing the select query and returning null. Seems like an issue with table locking or ignoring table locking.
   
   
   This is interesting:
   
     select seatid
     into seat
     from ste_seatspersessions sps join
     	ste_seats s using (seatid) join
       ste_usergroupsaccessseatsets uss using (seat_setid)
     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;
   
   You appear to be trying to implement your own "SKIP LOCKED" (which you
   should maybe use instead).
   
   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.
   
   What I suggest is that you walk this through several concurrent sessions
   yourself.  Use explicit transactions so that each concurrent session
   will hold onto its locks.
   
   -- 
   --
   Josh Berkus
   Red Hat OSAS
   (any opinions are my own)
   
   
   

2. Re: Locking issue

   Thomas Munro <thomas.munro@enterprisedb.com> — 2016-07-27T23:03:36Z

   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