Re: pg advisory locks

On 12 Oct 2014, at 18:41, Arun Gokule <arun.gokule@gmail.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
ofstatements. 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.