Re: Deadlocks caused by a foreign key constraint

On Aug 15, 2007, at 1:27 PM, Dmitry Koterov wrote:
> 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.

Code? Got a reproducible test case?

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

You already have the child row that's being updated; both versions of
it. So you don't have to lock anything to see if the FK field has
changed or not.

But... taking a quick look at RI_FKey_check in backend/utils/adt/
ri_triggers.c, I don't see it checking to see if the FK has changed,
which seems odd. I would think that if the FK fields haven't changed
that there's no need to perform the check.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)