Re: Obtaining advisory lock using ORDER BY
От | Andreas Joseph Krogh |
---|---|
Тема | Re: Obtaining advisory lock using ORDER BY |
Дата | |
Msg-id | VisenaEmail.d7.67fda7cc7634a982.14e87d6d881@tc7-visena обсуждение исходный текст |
Ответ на | Re: Obtaining advisory lock using ORDER BY (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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
Вложения
В списке pgsql-general по дате отправления: