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