I've a problem occurring daily for me, I get quite a few deadlocks every day,
and the concurrency is not so high. Happens with postgresql 8.0 and 8.1. as
well...
Here's a self-contained testcase, which I think it might be the problem I have
in our production database. While it might be some sort of theoretical
problem, it happens, the referenced tables are never really updated, but are
just lookup-tables. In the production systen it's a lot more complicated,
there are at least 10 different lookup tables, and not all table contain
references to all lookup-tables:
create table lookup1 ( id int primary key, t text
);
create table lookup2 ( id int primary key, t text
);
insert into lookup1 values (1, 'test1');
insert into lookup1 values (2, 'test2');
insert into lookup2 values (3, 'test3');
insert into lookup2 values (4, 'test4');
create table master1 ( id int primary key, l1_id int references lookup1(id), l2_id int references lookup2(id),
t text
);
create table master2 ( id int primary key, l2_id int references lookup2(id), l1_id int references lookup1(id),
t text
);
insert into master1 values (1000, 1, 3);
insert into master2 values (1001, 3, 1);
T1: BEGIN;
T2: BEGIN;
-- these are the queries similar to those from the foreign key code
T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
-- DEADLOCK OCCURS!
T1: UPDATE master1 set t='foo' where id=1000;
T2: UPDATE master2 set t='foo' where id=1001;
IMO it should be possible to solve this IF the foreign key code reorders the
"for update" queries in a well-defined order, maybe ordered by the oid of the
pgclass entry.
In my case, it always happens on INSERT activity (no updates on those tables,
just inserts), but I hope the above problem might be the solution for the
insert deadylock too.
Does this sound reasonable?
Regards,Mario Weilguni
p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." are
logged? Maybe this could help me finding out which queries the foreign key
code really issues.
