Thread: Locking issue

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
workerso tl_guest_list would only get called once at a time, after execution it would return the results and the next
jobwould 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
lockingthe table and I expect the function to wait at the select on ste_seatspersessions. However the function is
processingthe 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)

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