Return to Answer

Here are step-by-step instructions how to replicate a deadlock - works with plain UPDATE the same way as it does with SELECT .. FOR 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:

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

###1. Consistent order

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

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;

###3. One at a time

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

1. Consistent order

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

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;

3. One at a time

Now, how to avoid the problem? Three different approaches:

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: