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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
