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