Re: Strange deadlock in foreign key check

On 08/06/2015 07:19 AM, Sophia Wright wrote:
> I am seeing some odd locking behaviour when deleting a parent record
> (Postgres 9.4.4).
>
> Setup:
> create table pk_rel (id int primary key);
> create table fk_rel (pk_id int references pk_rel (id), x text unique);
> insert into pk_rel values (1), (2);
> insert into fk_rel values (1, 'a');
>
>
> This example works as I expected.
>
> Session 1:
> =>begin;
> =>update fk_rel set pk_id = 2;
>
> Session 2:
> =>delete from pk_rel where id = 1;
> [Fails with FK violation]
>
>
> But the following case, I do not understand.
>
> Session 1:
> =>begin;
> =>update fk_rel set x = 'b';
>
> Session 2:
> =>delete from pk_rel where id = 1;
> [Blocks waiting for Session 1]
>
> Session 1:
> =>insert into fk_rel values (1, 'a');
> [Blocks waiting for Session 2]
>
> At this point, Session 1 fails with a deadlock, and Session fails with a
> FK violation.
>
> So, why is this happening? Why doesn't Session 2 fail the FK check
> immediately, like in the first case? And why is it that updating
> fk_rel.x introduces a lock conflict, but updating fk_rel.pk_id does not?

Because fk_rel.x has a UNIQUE index on it. If you try the above using
this table definition:

create table fk_rel (pk_id int references pk_rel (id), x text);

it works as in your first case.

See here:

http://www.postgresql.org/docs/9.4/interactive/explicit-locking.html

FOR UPDATE

....

The FOR UPDATE lock mode is also acquired by any DELETE on a row,
and also by an UPDATE that modifies the values on certain columns.
Currently, the set of columns considered for the UPDATE case are those
that have a unique index on them that can be used in a foreign key (so
partial indexes and expressional indexes are not considered), but this
may change in the future.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com