Re: Deadlocks caused by referential integrity checks

On Tue, 24 Aug 2004, Greg Stark wrote:

>
> There's another poster complaining about referential integrity checks causing
> deadlocks. Unfortunately I've deleted the message so this response (and the
> archives aren't responding) isn't going to show up on the right thread.
>
> The reason the deadlock is happening is because of a known deficiency in
> Postgres that postgres has to take an exclusive lock on the records to ensure
> they aren't deleted before your insert/update commits. Unfortunately this
> isn't likely to be fixed soon, certainly not in 8.0.
>
> However I'm a bit surprised it causes deadlocks. It seems like you should be
> able to avoid deadlocks by making sure all the referential integrity checks
> are performed in a consistent order.

The general issue is when the actions causing the checks aren't in a
consistent order, or worse in cases where there can't be a consistent
order.

If transaction 1 inserts a child row that references row A, then
transaction 2 does a child row that references row B and they both then go
to do child rows that reference the other, in the current implementation,
there's no way to change the order to make that work (although deferring
the constraint often lowers the probability sufficiently).