Thread: Reproducable deadlock situation (possibly with foreign keys)

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.

Mario Weilguni wrote:

Hi,

> 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;

Actually, in 8.1 the FK code issues queries like

T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x;

which takes only a share lock on the tuple, not an exclusive lock, which
solves the blocking and deadlocking problem.  If you have a test case
where it fails on 8.1 I certainly want to see it.


> 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.

Hmm, actually, those queries should be logged normally, because AFAIK
they are issued just like any other query, via SPI.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Ok, this my fault, and you're right.

I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1 
DB on a testing system without thinking too much.

Still I think reordering those queries might prevent a deadlock.

Best regards

Am Mittwoch, 16. November 2005 12:21 schrieb Alvaro Herrera:
> Mario Weilguni wrote:
>
> Hi,
>
> > 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;
>
> Actually, in 8.1 the FK code issues queries like
>
> T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x;
>
> which takes only a share lock on the tuple, not an exclusive lock, which
> solves the blocking and deadlocking problem.  If you have a test case
> where it fails on 8.1 I certainly want to see it.
>
> > 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.
>
> Hmm, actually, those queries should be logged normally, because AFAIK
> they are issued just like any other query, via SPI.

Mario Weilguni wrote:
> Ok, this my fault, and you're right.
> 
> I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1 
> DB on a testing system without thinking too much.
> 
> Still I think reordering those queries might prevent a deadlock.

Well, if we could reorder them, we could have solved the problem long
ago.  I'm not totally sure it can't be done, but we tackled the problem
in a different way so it's moot now.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)