Re: 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 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)