Re: Deadlocks caused by a foreign key constraint

On Fri, Aug 10, 2007 at 09:38:36PM +0400, Dmitry Koterov wrote:
> Hello.
>
> I have a number of deadlock because of the foreign key constraint:
>
> Assume we have 2 tables: A and B. Table A has a field fk referenced to
> B.idas a foreign key constraint.
>
>
> -- transaction #1
> BEGIN;
> ...
> INSERT INTO A(x, y, fk) VALUES (1, 2, 666);
> ...
> END;
>
>
> -- transaction #2
> BEGIN;
> UPDATE B SET z = z + 1 WHERE id = 666;
> ...
> UPDATE B SET z = z + 1 WHERE id = 666;
> ...
> UPDATE B SET z = z + 1 WHERE id = 666;
> END;
>
>
> You see, table A is only inserted, and table B is only updated their field z
> on its single row.
> If we execute a lot of these transactions concurrently using multiple
> parellel threads, sometimes we have a deadlock:
>
> DETAIL: Process 6867 waits for ShareLock on transaction 1259392; blocked by
> process 30444.
> Process 30444 waits for ShareLock on transaction 1259387; blocked by
> process 6867.
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE "id" = $1
> FOR SHARE OF x"
>
> If I delete the foreign key constraint, all begins to work fine.
> Seems Postgres thinks that "UPDATE B SET z = z + 1 WHERE id = 666" query may
> modify B.id field and touch A.fk, so it holds the shareable lock on it.

What version are you running? I'm pretty sure that recent versions check
to see if the key actually changed.

> The question is: is it possible to KEEP this foreign key constraint, but
> avoid deadlocks?

I'm pretty sure that the deadlock is actually being caused by your
application code, likely because you're doing multiple updates within
one transaction, but not being careful about the id order you do them
in.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)