From: | Eduardo Piombino <drakorg(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Lock problem |
Date: | 2011-09-08 00:15:28 |
Message-ID: | CAGHqW79xUQSYpgZv=C3i=padCguAj_PK7CicwgPSmr5oFQcEAQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-09-08 00:29:07 | Re: Lock problem |
Previous Message | Steve Crawford | 2011-09-08 00:09:22 | 8.4 -> 9.0 upgrade difficulties |