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.
