Re: Tracking down a deadlock

On Sun, May 03, 2009 at 09:08:29PM -0400, Tom Lane wrote:
> > There are no other updates to that account table in the transaction, so I'm
> > confused how that is causing a deadlock.
>
> Is there more than one row with the target id?

No. It's a single SERIAL primary key.

> Does the account table have any foreign-key references to or from it?

Many. I had dropped all constraints, except foreign keys, from the account
table to see if that would help. (Didn't). One CHECK constraint involved
checking the sum of two columns in the account table which seemed like a
potential place for a deadlock. But I didn't think the foreign keys would be a
problem.

About 8 tables reference the account table, and the account table has about 5
columns that reference other tables.

> It's sometimes possible to get a deadlock associated with trying to lock
> FK-referenced rows that several updated rows have in common.

The transaction has a number of selects and inserts not related to the
account table. There is an insert into a log table that references the account
table, though, that happens right before the update to the account table.
I can't picture the deadlock, but I'm only familiar with the obvious examples.

What's the approach for dealing with this kind of deadlock (assuming the
FK-related as you suggest)? I assume at this point I need to try explicit
locking earlier in the transaction. Any suggestions which lock to use and on
what? SHARE ROW EXCLUSIVE on the account table before issuing the update?

--
Bill Moseley.
moseley(at)hank(dot)org
Sent from my iMutt