Re: trying to make sense of deadlocks

Richard Yen <dba(at)richyen(dot)com> writes:
> It seems like all the deadlocks are for tuple (3,60), but strangely,
> tuple (3,60) on the account table doesn't exist. Perhaps it was
> deleted? According to the account table, the account with id = 39271
> (which the UPDATE statements call for) corresponds to tuple (3,15):

Is this PG 8.3?

What I'm guessing is happening is that (3,60) is where the updated
version of (3,15) gets put. It never becomes visible to you because
the updating transaction never manages to commit due to the deadlock.
It would be possible/likely for the same CTID to be assigned over and
over in repeated trials if you're using 8.3 --- in prior versions this
theory gets a lot weaker because a vacuum pass would have to occur to
clean out the failed update tuple.

In any case what you seem to be looking at is updates of the same set
of two or more rows, but done in different orders by different
transactions.

regards, tom lane