Re: trying to make sense of deadlocks

在 Feb 9, 2009 8:52 PM 時， Tom Lane 寫到：

> 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?
>>
Yes, I'm using 8.3.5

> 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.
That makes sense. I should've thought of that earlier.

> 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.
Thanks for the tip!

--Richard