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

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

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Eduardo Piombino <drakorg(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6207: fali to get lock on parent table after two consecutive updates to the same row in child table
Date: 2011-09-15 07:25:42
Message-ID: 4E71A876.7000507@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Thomas KellererDate: 2011-09-15 07:29:54
Subject: Re: Problem with the 9.1 one-click installer Windows7 64bit
Previous:From: Sachin SrivastavaDate: 2011-09-15 07:11:10
Subject: Re: BUG #6204: Using plperl functions generate crash

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