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@xxxxxxxx Sent from my iMutt -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
