Lock problem

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.

Responses

Browse pgsql-general by date

  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