From: | Arun Gokule <arun(dot)gokule(at)gmail(dot)com> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg advisory locks |
Date: | 2014-10-13 02:57:01 |
Message-ID: | CAEAJEXdey15VQrqjn4wDfRGP0o3v0yeySJZRmr601RFJpBTzKw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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?
>
> Thanks,
> Arun
>
--
Thanks,
Arun
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Davies | 2014-10-13 04:11:34 | 9.3 migration issue |
Previous Message | Arun Gokule | 2014-10-12 16:41:27 | pg advisory locks |