Re: Racing DEADLOCK on PostgreSQL 9.3 - Mailing list pgsql-hackers

From Vladimir Svedov
Subject Re: Racing DEADLOCK on PostgreSQL 9.3
Date
Msg-id CADqDLE-VmwJCPf4wagg8Lh0HqKfGsxZyeZONDraNHuuCqknFzg@mail.gmail.com
Whole thread Raw
In response to Re: Racing DEADLOCK on PostgreSQL 9.3  (Jerry Sievers <gsievers19@comcast.net>)
List pgsql-hackers
try "Сombination of blocked and blocking activity" from https://wiki.postgresql.org/wiki/Lock_Monitoring - it should show you the originator.
pg_stat_activity.waiting only show affected

On 25 April 2018 at 19:56, Jerry Sievers <gsievers19@comcast.net> wrote:
Nick Dro <postgresql@walla.co.il> writes:

> Hi,
> I have a stock table.

> One of the users in the system ran this query:  update stock set
> quantity=quantity-5 where stockid=100  (from his client application).
> On the same time I ran from pg-admin this query:

> do $$
> begin
> alter table stock disable trigger stock_aftertrigger;
> update stock set stock=0 where stockid=106;
> alter table stock enable trigger stock_aftertrigger;
> end; $$

> What actualy happened is that both queries were stuck on waiting
> (after 3 minutes I decided to investagate as there quries should be
> extremly fast!).

I suspect your alter trigger job was blocked first by something else and
the more trivial update blocked behind you, which is not a *deadlock*
but a legit case of MVCC.

A real case of deadlock should have been broken in about 1s by the lock
management policy unless you are running a configuration with huge
deadlock timeout.

That your alter statement needs a heavy lock means that it can be easily
blocked and in so doing, block anything else whatsoever also requiring
access to same objects.

> I ran also this query:

> SELECT
> pid,
> now() - pg_stat_activity.query_start AS duration,
> query,
> state, *
> FROM pg_stat_activity
> WHERE waiting


> and both users were on waiting. When I stopped my query the other
> user got imiddiate result, then I reran mine which also finished
> immidiatly.
> I don't understand why both queries were stuck, the logic thing is
> that one ran and the other one is waiting (if locks aquired etc) it
> doesnt make senece that both queries are on waiting. waiting for what
> exactly?


> Any thoughts on this issue?
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


pgsql-hackers by date:

Previous
From: Etsuro Fujita
Date:
Subject: Re: Oddity in tuple routing for foreign partitions
Next
From: Amit Langote
Date:
Subject: Re: Remove mention in docs that foreign keys on partitioned tablesare not supported