Re: Racing DEADLOCK on PostgreSQL 9.3

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