Re: pg advisory locks

On 12 Oct 2014, at 18:41, Arun Gokule <arun(dot)gokule(at)gmail(dot)com> wrote:

> 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?

Your last query doesn’t call the unlock statement, that WITH section is going to be ignored as the query isn’t using it.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.