Hello list, I'm having a locking problem and I'm not sure what is causing it. I have two pgsql concurrent transactions, running each in a separate connection to postgres (I can reproduce it from pgadmin).
T1) operates only on table A
begin transaction; select id from A where id = 100 for update nowait; update A set x = x + 15 where id = 100; update A set x = x + 15 where id = 100; commit;
T2) operates only on table B
begin transaction; select x from B where id = 116 for update nowait; update B set x = x + 1; commit;
If I run transaction T1 up to the beginning of the second update, and then i stall there, transaction T2 is allowed to do the select for update with no problem at all.
However, if transaction T1 goes a step further, and does the second update, from that point on, transaction T2 is not able to get the lock on B.
I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B;
This behaviour is backed up with a consistent increase in the locks from the server status views.
I don't see how:
select * from A for update nowait; update A set x = x + 1;
has a different effect than (locks-wise)
select * from A for update nowait; update A set x = x + 1; update A set x = x + 1;
PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates.
I don't see how a second update (identical to the previous one if you wish) to A on T1 will prevent T2 from getting a row level lock on B.
Does anyone have an explanation on why this happens? Thank you, Eduardo.