Re: [GENERAL] Locking issue
On Thu, Jul 28, 2016 at 10:18 AM, Josh Berkus 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking issue
Hi everyone,
I am running 'PostgreSQL 9.1.4, compiled by Visual
C++ build 1500, 64-bit' on a Windows Server and I am having some Locking
issues. Maybe anyone can let me know what is wrong with my example:
Imagine that we have two tables (t_users and t_records)
t_users contains 1 row per each user
t_records is a regular transactional table which contains a
field userid with the user that created/updated the row.
CREATE TABLE
t_users(useridVARCHAR(10),
loginattempts INTEGER,
CONSTRAINT pk_t_users PRIMARY KEY (userid));
CREATE TABLE
t_records(recordidINTEGER,
description VARCHAR(100),
userid VARCHAR(10)
REFERENCES
t_users(userid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION);
INSERT INTO t_users (userid,
loginattempts) VALUES ('andrew',0);
The user logs in and starts a
background process that contains a long TRANSACTION which
updates/inserts rows in t_records with the user's
id. This process keeps the transaction
open for 1 hour while it works with code like:
/* Session #1 */
BEGIN TRANSACTION
/* Big loop */
INSERT INTO t_records (recordid,
description, userid) VALUES (1,'Record #1','andrew');
/*… SOME CODE HERE */
/* */
/* Once the loop ends, it will COMMIT/ROLLBACK the transaction */
ROLLBACK
/ COMMIT
/* END of Session #1 */
The user logs out and then tries
to log back in after 30 minutes. The login hangs because we are not be able to
update records on t_user (for
userids used on Session#1 ) until the transaction on Session#1 is done:
/* SESSION #2 */
UPDATE t_users SET
loginattempts = 1 WHERE userid = 'andrew'
/*END SESSION #2*/
Any comments or feedback will be appreciated.
Regards,
Andrew Jaimes
