Skip site navigation (1) Skip section navigation (2)

BUG #6207: fali to get lock on parent table after two consecutive updates to the same row in child table

From: "Eduardo Piombino" <drakorg(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6207: fali to get lock on parent table after two consecutive updates to the same row in child table
Date: 2011-09-15 03:55:44
Message-ID: 201109150355.p8F3tioI087016@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6207
Logged by:          Eduardo Piombino
Email address:      drakorg(at)gmail(dot)com
PostgreSQL version: 9.1.0
Operating system:   Windows XP
Description:        fali to get lock on parent table after two consecutive
updates to the same row in child table
Details: 

Hi, I ran across some behavior that I don't know if it is expected or not (I
wouldn't personally have expected pg, IMHO, to behave like that). I've read
the TODOs and the FAQs but was unable to find anything related to this
specific matter.

BUG #4401: concurrent updates to a table blocks one update indefinitely, is
the closest reference I found to my case, but it is not exactly the case
(and it was also disregarded as a bug).

The case is I have two tables.
Then I have two pg sessions, each one working exclusively on each table,
that is, session 1, working only on table a, session 2 working only on table
b.

Everything works fine, I can get a lock on each table on each session,
commit changes, etc.

Problem came when I tried to update the child table a, which holds a fk to
b, after doing two updates to the same record in a. The other session will
fail to obtain the lock after the second update (but won't fail immediately
after the first one).

Tom Lane suggested that it may have something to do to some optimization in
the management of the foreign keys after two updates to the same row in the
child table, but I got no concrete answer as if this behavior is what is
expected from the engine or not.

I'm no expert on the subject, by far less an expert on any details of the
internals of the implementations of the fks optimizations.

But apart from the logical explanation of why this actually happens (cause
it does happen, and I'm almost sure that there will be a reasonable
explanation), I ask you if you really consider this to be the expected
behavior or if it is the way you would like pg to behave.

Following is a test case with which you can very easily reproduce the
issue.
Open two pg sessions to the database.
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;

(now switch to the second terminal)

-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- Query returned
successfully: 1 row affected, 47 ms execution time.
rollback;

(now switch back to the first terminal)

-- 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;

(now switch back to the second terminal)

-- 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

(in this case, T2 fails to obtain the lock on b (?))

thanks in advance,
eduardo

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-09-15 03:58:15
Subject: Re: Dropped index on table preventing rule creation
Previous:From: Robert HaasDate: 2011-09-15 03:52:50
Subject: Re: psql doesn't reuse -p after backend fail

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group