Re: do foreign key checks lock parent table ?

On Tue, 2 Apr 2002 swalker(at)iglou(dot)com wrote:

> If you try the example I have below - the second transaction will block
> waiting for the first one to complete. This doesn't seem correct to me.
> Is this a known problem / feature? If I create the table w/ a deferrable
> intially deferred foreign key - I don't get the problem. But this is a
> very unexpected default behavior ( At least to me :) ).
>
> create table parent
> (key integer primary key,
> description varchar(50) not null);
> create table child
> (key integer primary key,
> child_key integer not null references parent(key),
> description varchar(50) not null);
>
> insert into parent values (1,'Parent Record #1');
> insert into child values (101,1,'Child Record #1');
> insert into child values (102,1,'Child Record #2');
>
>
> // From one psql
> begin;
> update child set child_key = 1 where key = 101;
>
>
> //second session
> begin;
> update child set child_key = 1 where key = 102;

The locks being grabbed are a bit stronger than they need to be
(if you want more info, there was a bit of discussion on -hackers
recently). The updates should block updates on parent of the
referenced row but don't need to block other child updates but there's
no current lock level that is quite right.