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

Question

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:

1. I'm misunderstanding my code which creates the deadlock error, and there are actually multiple UPDATE statements in each transaction
2. 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?

Answer 1

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.

Answer 2

Laurenz explained the mechanism that can lead to deadlocks, and you already included a link yourself to a more detailed explanation by Kevin:

• Deadlocks in PostgreSQL when running UPDATE

Here are step-by-step instructions how to reproduce a deadlock - works with plain UPDATE the same way as it does with SELECT .. FOR UPDATE:

• How to simulate deadlock in PostgreSQL?

Now, how to avoid the problem?
If you are going to update a substantial share or all of the table - and you can afford to - just write-lock the table. Typically, this is not the way to go. Else, three different approaches:

1. Consistent order

The manual has this advise in the chapter on deadlocks:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Not sure why there is still no ORDER BY for UPDATE. But that's what we have to work with. Lock rows with SELECT ... FOR UPDATE in the same transaction instead - like you already tried, as your earlier question indicates. You just forgot the essential deterministic ORDER BY:

BEGIN;
SELECT FROM foos WHERE owner_id = 123 AND unread
ORDER  BY ???  -- any deterministic order, PK would be an obvious candidate
FOR    UPDATE;

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
END;

Obviously, all potentially competing transactions have to acquire locks in the same order.

2. Skip locked rows

Only process unlocked rows:

BEGIN;
SELECT FROM foos WHERE owner_id = 123 AND unread
-- ORDER BY ???  -- optional in this case
FOR    UPDATE SKIP LOCKED;

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
END;

If you are certain that skipped rows have been processed by a competing transaction doing the same, you are done here. (Are you sure?)
Else, to make sure, follow up with a check:

SELECT EXISTS (SELECT FROM foos WHERE owner_id = 123 AND unread);

Writers don't block readers and readers don't block writers, so this returns TRUE until every last row has been updated successfully. Loop the above UPDATE block followed by this (with appropriate delay) until you get FALSE. Then you are done.

May be cheaper for big sets where ORDER BY would add significant cost. OTOH, it still can make sense to add ORDER BY if there is a matching index ...

3. One at a time

Similar to the above, except that only a single row is updated at a time. Typically more expensive but any deadlock potential is eliminated - if done right. Consider this when processing a single row already takes a long time.

Detailed explanation (mostly also applicable to the above) and instructions:

• Postgres UPDATE ... LIMIT 1