Racing DEADLOCK on PostgreSQL 9.3

<div dir='rtl'><div dir="ltr">Hi,</div>
<div dir="ltr">I have a stock table.</div>
<div dir="ltr">&nbsp;</div>
<div dir="ltr">One of the users in the system ran this query:&nbsp; update stock set quantity=quantity-5 where stockid=100&nbsp; (from his client application).</div>
<div dir="ltr">On the same time I ran from pg-admin this query:</div>
<div dir="ltr">&nbsp;</div>
<div dir="ltr">do $$<br />begin<br />alter table stock disable trigger stock_aftertrigger;<br />update stock set stock=0 where stockid=106;<br />alter table stock enable trigger stock_aftertrigger;<br />end; $$</div>
<div dir="ltr">&nbsp;</div>
<div dir="ltr">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!).</div>
<div dir="ltr">I ran also this query:</div>
<div dir="ltr">&nbsp;</div>
<div dir="ltr">
<div>SELECT<br /> pid,<br /> now() - pg_stat_activity.query_start AS duration,<br /> query,<br /> state, *<br />FROM pg_stat_activity<br />WHERE waiting</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>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.</div>
<div>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?</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>Any thoughts on this issue?</div>
</div></div>

On 25/04/18 09:21, Nick Dro wrote:
> 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 does stock_aftertrigger do?

> 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 ran also this query:
> SELECT
> pid,
> now() - pg_stat_activity.query_start AS duration,
> query,
> state, *
> FROM pg_stat_activity
> WHERE waiting

What was the full output of this query?

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

"SELECT * FROM pg_locks;" might give a clue.

- Heikki

Nick Dro <postgresql(at)walla(dot)co(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800

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(at)comcast(dot)net> wrote:

> Nick Dro <postgresql(at)walla(dot)co(dot)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(dot)consulting(at)comcast(dot)net
> p: 312.241.7800
>
>