In response to Bill Moseley <moseley@xxxxxxxx>: > 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. I've never gone to the trouble to isolate this to a reproducible test case, but I've seen situations where a single UPDATE statement appears to deadlock when run simultaneously. For example: UPDATE session_table SET text_field = '==some huge value==' WHERE non_unique_column = 5; Assuming this table sees frequent updates and that text_field is normally very large, if there are a sufficient number of rows where non_unique_column is 5, running this query in two independent sessions has a high chance of deadlocking. My theory is that since there is no ordering to the results returned by the WHERE clause, the two queries may acquire row locks in different orders. The result being that they may actually deadlock at the row level. I don't know 100% if this is possible, but I do know that working off that assumption, we reorganized our data structure so that we could use a unique column in the WHERE clause, and we have not seen the problem since. In any event, I hope that information is helpful to you. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
