Rob Nikander <rob.nikander@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
