pg advisory locks

From: Arun Gokule <arun(dot)gokule(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: pg advisory locks
Date: 2014-10-12 16:41:27
Message-ID: CAEAJEXevTD_t2Umd80Q7GahWcrU7Nqc01UPGf6i7vdhJ7uzFnQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am executing pg_advisory_locks using the following set of statements:

SELECT pg_advisory_lock(317,2);
UPDATE posts SET dislikers = array_remove(dislikers, 7) WHERE id = 317;
update posts set num_dislikes = icount(dislikers), updated_at = now()
where id = 317;
WITH update_likers AS (SELECT pg_advisory_unlock(317,2)) select num_likes,
num_dislikes, (7 IN (select(unnest(likers)))) as liked, (7 IN
(select(unnest(dislikers)))) as disliked from posts where id = 317 LIMIT 1;

These are issued from a multithreaded app. One in 1000 queries, I get a
deadlock after the execution of the above set of statements. i.e. SELECT
try_pg_advisory_lock(317,2) returns false. Is there something obvious that
I am doing wrong?

Thanks,
Arun

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Arun Gokule 2014-10-13 02:57:01 Re: pg advisory locks
Previous Message Bruce Momjian 2014-10-11 18:24:19 Re: Shared memory changes in 9.4?