Thread: pg advisory locks

pg advisory locks

From
Arun Gokule
Date:
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

Re: pg advisory locks

From
Arun Gokule
Date:
Also I noticed that executing the following query:

  SELECT *
FROM
(
    SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.pid
) AS foo
WHERE age > '30 seconds'
ORDER BY age DESC;


I get 2 entries for the pid 78573 with one as granted = true and the other as granted =false but they have the same timestamps. See below:

       age       |  pid  | granted | application_name |         backend_start         |          xact_start           |         state_change          | waiting | state  |          query_start          |                             left
-----------------+-------+---------+------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------------------------+--------------------------------------------------------------
 10:53:25.394862 | 78261 | t       | bin/rails        | 2014-10-12 05:20:04.543242-07 |                               | 2014-10-12 08:57:22.950505-07 | f       | idle   | 2014-10-12 08:57:22.949693-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET dislikers =
 10:53:23.50218  | 78573 | t       | bin/rails        | 2014-10-12 05:37:58.120879-07 | 2014-10-12 08:57:24.842375-07 | 2014-10-12 08:57:24.842379-07 | t       | active | 2014-10-12 08:57:24.842375-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET likers = arr
 10:53:23.50218  | 78573 | f       | bin/rails        | 2014-10-12 05:37:58.120879-07 | 2014-10-12 08:57:24.842375-07 | 2014-10-12 08:57:24.842379-07 | t       | active | 2014-10-12 08:57:24.842375-07 | SELECT pg_advisory_lock(317,2);UPDATE posts SET likers = arr
 00:57:53.495221 | 90360 | t       | bin/rails        | 2014-10-12 18:43:20.09202-07  |                               | 2014-10-12 18:52:54.849397-07 | f       | idle   | 2014-10-12 18:52:54.849334-07 | SELECT "posts"."id" AS t0_r0, "posts"."content" AS t0_r1, "p



On Sun, Oct 12, 2014 at 9:41 AM, 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 of statements. i.e.  SELECT try_pg_advisory_lock(317,2) returns false. Is there something obvious that I am doing wrong?

Thanks,
Arun



--
Thanks,
Arun

Re: pg advisory locks

From
Alban Hertroys
Date:
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.