In response to Bill Moseley <moseley@hank.org>:
> 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/