Racing DEADLOCK on PostgreSQL 9.3

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

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: