Re: Reproducable deadlock situation (possibly with foreign keys)

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