Re: "deadlock detected" documentation

Tom Lane wrote:
> You are probably getting deadlocks on the rows referenced by the foreign
> keys, then. As of the current release, each UPDATEd row will cause the
> foreign-key triggers to grab a row lock (SELECT FOR UPDATE lock) on the
> referenced row in the other table. If you have concurrent updates
> touching rows that reference the same other-table rows, it's possible to
> get a deadlock depending on the order in which the updates happen to
> occur.

> There is a patch in CVS tip that alleviates the problem substantially
> by not taking a foreign-table lock when the foreign key value isn't
> being changed by the UPDATE, which is commonly the case (although I'm
> not entirely sure that it will help you, since you do seem to be
> updating the referencing column). If you want to try it, I believe Jan
> Wieck posted a 7.3 version of the patch a month or so ago.

I see. One item I forgot to mention ... Both update statements have a
where clause containing txn.terminalid=38. Txn.terminalid is a foreign
key column in that table (txn.terminalid->terminal.terminalid). Could
this be the source of the trouble?

If so, I can work around this for now, but the patch you mention sounds
like it would work in almost all situations where this might adversely
(and unexpectedly) affect us.

Thanks!

--
Matt Mello