Re: FK locking still too strong

Csaba Nagy wrote:
> Hi all,
>
> While postgres 8.x improved a lot the locking issues related to foreign
> keys, the locking used is still stronger than needed.
>
> The following test case deadlocks on postgres but goes through on
> oracle:
>
> preparation of tables:
>
> create table test1(col_fk integer primary key, col_1 integer);
> create table test2(col_2 integer primary key, col_fk integer references
> test1(col_fk));
> insert into test1 (col_fk, col_1) values (1, 1);
> insert into test1 (col_fk, col_1) values (2, 2);
>
> session_1:
>
> begin;
> update test1 set col_1 = 10 where col_fk = 1;
>
> session_2:
>
> begin;
> insert into test2 (col_2, col_fk) values (1, 2);
>
> session_1:
>
> -- this locks on postgres, does not on oracle
> update test1 set col_1 = 20 where col_fk = 2;
>
> session_2:
>
> -- deadlock on postgres, goes through on oracle
> insert into test2 (col_2, col_fk) values (2, 1);
>

Purely out of interest I just tried this on MySQL 5.0.26 and found
almost the same results. In MySQL session 1 was rolled back, on pg 8.1.5
session 2 was rolled back.