Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel:

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;


This results in deadlocks.

My understanding of what causes deadlocks is like the scenario described in this question, with "interwoven" UPDATE statements updating two different rows in a different order. I don't understand how a single UPDATE statement could result in a deadlock. I'm not able to replicate the deadlock scenario using two parallel psql sessions in my dev environment. My guesses for why I can't replicate it:

• I'm misunderstanding my code which creates the deadlock error, and there are actually multiple UPDATE statements in each transaction

• The "interwoven" aspect is happening, but "within" the UPDATE statement which covers multiple rows, so it's difficult to replicate.

Is it possible for this single UPDATE to be creating the deadlock?

Your statement modifies several rows. Each of these rows is locked when it is updated.

It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking your UPDATE. If the concurrent transaction then tries to lock one of the rows that your UPDATE has already locked, you get a deadlock.

StackExchange Database Administrators Q#257217, answer score: 16