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

The following bug has been logged online:

Bug reference:      6207
Logged by:          Eduardo Piombino
Email address:      drakorg@gmail.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

On 15.09.2011 06:55, Eduardo Piombino wrote:
> 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.
>
> ...
>
> 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);
>
> [updates using two sessions]

Those first updates on the tables are unnecessary, this test case can be
simplified into just:

-- session T1
begin transaction;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;

(now switch to the second terminal)

-- session T2
select * from b where id = 1 for update nowait;

As Tom suggested, this an artifact of the way foreign key triggers are
queued. The first update takes advantage of this optimization (in
AfterTriggerSaveEvent function):

> /*
>  * If this is an UPDATE of a PK table or FK table that does not change
>  * the PK or FK respectively, we can skip queuing the event: there is
>  * no need to fire the trigger.
>  */

The second UPDATE, however, falls into this exception later in that
function:

> /*
>  * If this is an UPDATE of a PK table or FK table that does not change
>  * the PK or FK respectively, we can skip queuing the event: there is
>  * no need to fire the trigger.
>  */
>  ...
> /*
>  * Update on FK table
>  *
>  * There is one exception when updating FK tables: if the
>  * updated row was inserted by our own transaction and the
>  * FK is deferred, we still need to fire the trigger. This
>  * is because our UPDATE will invalidate the INSERT so the
>  * end-of-transaction INSERT RI trigger will not do
>  * anything, so we have to do the check for the UPDATE
>  * anyway.
>  */

We can't distinguish a row that was UPDATEd earlier in the same
transaction from a row that was INSERTed earlier in the same
transaction. So on the second UPDATE, the foreign-key trigger is fired,
and the trigger locks the tuple in table b.

That's not ideal, of course, but I don't see any easy way to fix that.
Perhaps we could add some extra checks there, like whether any
INSERT-triggers have actually been queued. But it would just narrow the
issue, I don't see a way to completely eliminate it.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com