Postgres Pro
Downloads
Home   >   mailing lists

Thread: Obtaining advisory lock using ORDER BY

Obtaining advisory lock using ORDER BY

From
Andreas Joseph Krogh
Date:
Hi all.
 
I have this "dequeue" query which is working:
 
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id    , qe.payload_string
from origo_queue_entry qe
WHERE
    qe.queue_id = 2
    AND pg_try_advisory_xact_lock(sequence_id)
LIMIT 1 FOR UPDATE
;
I'm not sure is this is guaranteed to lock in ASC-order on column sequence_id, is it?
 
To ensure this I've tried with an explicit ORDER BY on "sequence_id", like this:
 
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id    , qe.payload_string
from origo_queue_entry qe
WHERE
    qe.queue_id = 2
    AND pg_try_advisory_xact_lock(sequence_id)
ORDER BY qe.sequence_id ASC LIMIT 1 FOR UPDATE
;
But the latter query results in all non-locked rows being locked (but it returns only 1 row due to LIMIT 1), but I'd like the "lowest" non-loced one.
 
Is there a way to make the locking work on an custom ordered set, preserving the "LIMIT 1"?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: Obtaining advisory lock using ORDER BY

From
Tom Lane
Date:
Andreas Joseph Krogh <andreas@visena.com> writes:
> I have this "dequeue" query which is working:
>  
> select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string
> fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND pg_try_advisory_xact_lock(
> sequence_id) LIMIT 1 FOR UPDATE ;
> I'm not sure is this is guaranteed to lock in ASC-order on column sequence_id,
> is it?

No.  You could possibly do SELECT...ORDER BY...FOR UPDATE in a
subquery and do the pg_try_advisory_xact_lock call in the outer query.

It might take some fooling around to get a plan that doesn't lock
more rows than necessary; EXPLAIN is your friend.

            regards, tom lane

Re: Obtaining advisory lock using ORDER BY

From
Andreas Joseph Krogh
Date:
På mandag 13. juli 2015 kl. 16:22:28, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> I have this "dequeue" query which is working:
>
> select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string
> fromorigo_queue_entry qe WHERE qe.queue_id = 2 AND pg_try_advisory_xact_lock(
> sequence_id) LIMIT 1 FOR UPDATE ;
> I'm not sure is this is guaranteed to lock in ASC-order on column sequence_id,
> is it?

No.  You could possibly do SELECT...ORDER BY...FOR UPDATE in a
subquery and do the pg_try_advisory_xact_lock call in the outer query.

It might take some fooling around to get a plan that doesn't lock
more rows than necessary; EXPLAIN is your friend.

regards, tom lane
 
I'm unable to construct such a query.
 
This query blocks and tries to lock the same row (highest sequence_id):
 
SELECT * FROM
    (SELECT
         qe.entity_id,         qe.queue_id,         qe.sequence_id,         qe.tx_id,         qe.payload_string
     FROM origo_queue_entry qe     WHERE
         qe.queue_id = 2
     ORDER BY qe.sequence_id DESC
     LIMIT 1
     FOR UPDATE
    ) q
WHERE pg_try_advisory_xact_lock(sequence_id)
 
I'm trying this (on pg-9.4) as an alternative to 9.5's new SKIP LOCKED.
 
Is there a way to accomplish "lock next non-locked row with custom ordering" using pg-9.4?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment