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! <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > >
