Re: Deadlocks caused by referential integrity checks

Greg Stark wrote:

> [...]
> 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.
>
> At least some versions of postgres ensure that constraint triggers will be
> executed in alphabetical order. This means if all your foreign key constraints
> are named in a consistent order the row locks should be taken in a consistent
> order. If that's true then deadlocks shouldn't happen.
>
> So you would have to take a schema dump, grep out all the foriegn key
> constraints, sort them and uniquefy them, decide on an order, and then go
> through every table renaming them to enforce that order.
>
> That could be a lot of work so hopefully someone more knowledgeable will be
> able to confirm that this should work (and which versions it would work in)
> before you go about trying it. I'm sure other people would like to hear if
> it's successful since it's a pretty frequently asked question.

I'm not sure this can work. In the example I put, the
locks occur as SQL statements are being executed; those
are asynchrnous and with timing beyond the server's
control (we have a client/server system with multiple
concurrent client requests). That would mean (I think),
that the names of the constraints play no role in the
order in which the locks are set. It would work the
way you describe it if there are multiple referential
integrity checks in the same statement. But I'm not
sure if that can cause a deadlock at all -- I mean,
aren't *single* SQL's atomic in postgres? (i.e., aren't
they fully serialized such that no two statements can
be concurrent?)

What is suggested in the documentation is to follow the
standard practice in multithreaded programing to avoid
deadlocks when multiple resources have to be locked:
ensure that the SQL's are executed maintaining a given
order in the foreign-key values (e.g., always ascending).
This way, it is impossible that one transaction locks
something that another transaction has not locked yet
if that other transaction has already locked something
(they both go in ascending order, and that's what
guarantees that it is impossible for the deadlock
condition to happen).

Now, the thing is that this is always feasable if there
is only one foreign key. If there are two foreign-keys
that are not correlated, and in some other situations,
it may not be possible to sort the statements guaranteeing
a particular order for all foreign-keys.

Fortunately (for me), in my case a preliminary analysis
tells me that this is not the case -- the transactions
that are causing deadlocks have a single foreign-key
constraint, so it looks like this solution can work.

Thanks,

Carlos
--