Re: Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)

On Thu, 2007-02-08 at 17:47, Marc Munro wrote:
> [snip] One of the causes of deadlocks in Postgres is that its referential
> integrity triggers can take locks in inconsistent orders. Generally a
> child record will be locked before its parent, but not in all cases.
[snip]

The problem is that eliminating the deadlock is still not the complete
cake... the interlocking still remains, possibly leading to degraded
performance on high contention on very common parent rows. The real
solution would be when an update on the parent table's non-referenced
fields is not interlocking at all with updates of the child rows... and
I think there were some proposals to do that.

In fact one possibility to avoid this problem is vertical partitioning,
i.e. separating the non-key columns in a parallel table and updating
them there. However this is a choice only when you know it beforehand
and you're not inheriting the schema from other DBs...

Cheers,
Csaba.