No.
I have tested all cases, the code I quoted is complete and minimal. All operations are non-blocking (count incrementation is non-blocking, insertion with a foreign key is non-blocking too), but it still generates a deadlock time to time. Deletion of the foreign key constraint completely solves the problem.
I am using the latest version of Postgres.
You said "I'm pretty sure that recent versions check to see if the key actually changed", but how could it be if Postgres uses a row-level locking, not field-level locking? Seems it cannot check what fields are changed, it locks the whole row.
On 8/15/07, Decibel! <decibel@decibel.org> wrote: 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@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
