Obtaining advisory lock using ORDER BY

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

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

andreas@xxxxxxxxxx

www.visena.com