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.
