Re: Deadlock with single update statement?

Rob Nikander <rob(dot)nikander(at)gmail(dot)com> writes:
> I’m trying to track down a deadlock happening in a live app. I’m wondering about statements like this, which select more than one row to update:
> update t set num = 1 where name = ‘foo’;
> It appears to be causing a deadlock, but I can’t reproduce it on my test database. Could two threads, each running this update, get in a deadlock? In other words, are both of the following true:
> 1. The update locks each row in sequence, not all at once.
> 2. The order of the row locking could vary from one thread to the next.

Yes and yes. I can think of at least two explanations for (2):

A. Different sessions are picking different plans for the query. This
seems unlikely if the queries are really exactly identical in each
session, but if there are additional WHERE conditions that could vary,
then it seems entirely plausible.

B. The query selects enough rows-to-be-modified that the plan ends up
being basically a seqscan, and the table is large enough that the
"synchronized scan" logic kicks in. In that case each session will
scan the table circularly from an essentially-random start point,
producing a different row locking order.

If it's (B) you could ameliorate the problem by disabling syncscan,
but it'd be better to adjust the query to ensure a deterministic
update order.

regards, tom lane