Re: Racing DEADLOCK on PostgreSQL 9.3

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