Обсуждение: Lock problem
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.
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.
Eduardo Piombino <drakorg@gmail.com> writes: > 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; I think it's probably explained by this: > 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. IIRC there are some optimizations in the FK stuff that don't apply once a single transaction has updated a relevant row more than once. You haven't given enough details (not even a PG version) to be sure about it, but that's what I'd bet on. regards, tom lane
I'm sorry Tom.
This happens (at least) both in 8.4 and 9.0. I've just tested it in 2 different databases with the same results.
I have workarounds to this, but I was wondering what could be the actual root of the problem, in order to (try to) achieve the best solution possible.
Following is a test case with which you can reproduces the issue.
Statements should be executed in the following order.
-- session T1 means that the following lines should be executed from pg session 1.
-- session T2 means that the following lines should be executed from pg session 2.
create table b (
id bigint not null,
x double precision,
constraint pk_b primary key (id));
create table a (
id bigint not null,
id_b bigint,
x double precision,
constraint pk_a primary key (id),
constraint fk_b foreign key (id_b) references b (id));
insert into b (id, x) values (1, 0);
insert into a (id, id_b, x) values (1, 1, 0);
-- session T1
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- Query returned successfully: 1 row affected, 47 ms execution time.
rollback;
-- session T1
rollback;
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;
-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- ERROR: could not obtain lock on row in relation "b". SQL state: 55P03
Regarding my original question, I would like to know if this is a known issue/feature/bug/unwanted optimization consequence/or is it just a normal behavior that I should've had predicted.
Best regards,
Eduardo.
This happens (at least) both in 8.4 and 9.0. I've just tested it in 2 different databases with the same results.
I have workarounds to this, but I was wondering what could be the actual root of the problem, in order to (try to) achieve the best solution possible.
Following is a test case with which you can reproduces the issue.
Statements should be executed in the following order.
-- session T1 means that the following lines should be executed from pg session 1.
-- session T2 means that the following lines should be executed from pg session 2.
create table b (
id bigint not null,
x double precision,
constraint pk_b primary key (id));
create table a (
id bigint not null,
id_b bigint,
x double precision,
constraint pk_a primary key (id),
constraint fk_b foreign key (id_b) references b (id));
insert into b (id, x) values (1, 0);
insert into a (id, id_b, x) values (1, 1, 0);
-- session T1
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- Query returned successfully: 1 row affected, 47 ms execution time.
rollback;
-- session T1
rollback;
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;
-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- ERROR: could not obtain lock on row in relation "b". SQL state: 55P03
Regarding my original question, I would like to know if this is a known issue/feature/bug/unwanted optimization consequence/or is it just a normal behavior that I should've had predicted.
Best regards,
Eduardo.
On Wed, Sep 7, 2011 at 9:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eduardo Piombino <drakorg@gmail.com> writes:I think it's probably explained by this:
> 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;IIRC there are some optimizations in the FK stuff that don't apply once
> 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.
a single transaction has updated a relevant row more than once. You
haven't given enough details (not even a PG version) to be sure about
it, but that's what I'd bet on.
regards, tom lane