Avoiding deadlocks when locking multiple rows without using NOWAIT

Question

In a couple of answers I've been advised that NOWAIT is necessary to prevent deadlocks, which has surprised me because it's not mentioned in the Postgres documentation on locking and deadlocks

Given the following 3 sessions:

CREATE TABLE my_table (id int primary key);
-- Session 1
BEGIN;
SELECT id FROM my_table
WHERE id IN (1,2,5)
ORDER BY id
FOR UPDATE
-- Session 2, started after the select in session 1 (before it is committed)
BEGIN
SELECT id FROM my_table
WHERE id IN (4,5,6)
ORDER BY id
FOR UPDATE
-- Session 3, started after the select in session 2 (before 1 or 2 are committed)
BEGIN
SELECT id FROM my_table
WHERE id IN (5,6,7)
ORDER BY id
FOR UPDATE

I had thought that each query would wait once it reached a locked row and continue locking in the order specified by ORDER BY. However, based on those answers, it would seem that locking order is not guaranteed even with an ORDER BY and it's possible that these statements could deadlock?

I'm hoping to avoid using NOWAIT because the scenario I'm working on is part of a rather large transaction and presumably the resulting error would invalidate the transaction and I'd have to roll it back and start the whole thing over?

The transactions are relatively fast so I don't mind waiting if I'm able to avoid deadlocks.

If it's true that the above will not guarantee ordering, then the only way I can see to do this would be

SELECT id FROM my_table WHERE id = 1 FOR UPDATE;
SELECT id FROM my_table WHERE id = 2 FOR UPDATE;
...etc

Which seems tedious. Is there a better way?

Erwin Brandstetter · Accepted Answer · 2023-02-05T03:05:20.750

Like we discussed in this related answer, rows can be locked out of order if, and only if...

columns contributing to that ORDER BY might be updated by concurrent transactions

That should not apply to your example with ORDER BY id. Your "id" column looks like a surrogate PK column that's never updated in the first place. Then you are safe.

Why can there still be deadlocks?

The recommended defense against deadlocks is to take locks in a consistent, deterministic sort order.

But there is a loophole in this otherwise reliable regime: SELECT ... FOR UPDATE locks after applying filters (WHERE) and sorting (ORDER BY). If a concurrent UPDATE has locked one or more of the qualifying rows, the first query has to wait (unless instructed otherwise with NOWAIT).
If that UPDATE eventually commits, the first query now proceeds and takes row locks as planned. But it does not sort rows again. If any column value involved in ORDER BY has been changed disruptively, a row that previously would have been locked in order, is effectively locked out of order now, hence re-introducing the possibility for deadlocks. Typically, that's still extremely unlikely to happen. But some access patterns are more susceptible than others.

Consider this minimal test-case. Run steps in three separate sessions to reproduce the effect:

CREATE TABLE tbl (
  id int PRIMARY KEY
, sort real NOT NULL
);
INSERT INTO tbl (id, sort) VALUES (1,1), (2,2), (3,3);
-- Session 1
BEGIN;
UPDATE tbl
SET    sort = 4
WHERE  sort = 2;
           -- Session 2
           BEGIN;
           SELECT sort
           FROM   tbl
           ORDER  BY sort
           FOR    UPDATE;

                       -- Session 3
                       BEGIN;
                       SELECT sort
                       FROM   tbl
                       WHERE  sort = 3
                       FOR    UPDATE;
                       -- wait

COMMIT;
                       SELECT sort
                       FROM   tbl
                       WHERE  sort = 4
                       FOR    UPDATE;

Session 1 updates row 2, thereby locking it.
Session 2 tries to lock rows in order but, after locking row 1, has to wait for row 2.
Session 3 locks row 3.
Session 1 commits. This triggers session 2 to resume. Row 2 has now sort = 4, but is locked anyway. Then it waits for row 3, locked by session 3.
Session 3 tries to lock row 4, upholding the required sort order for locks, but now conflicts anyway.
Boom ... deadlock!

I get this from session 3:

ERROR:  deadlock detected
DETAIL:  Process 9700 waits for ShareLock on transaction 4412; blocked by process 9663.
Process 9663 waits for ShareLock on transaction 4413; blocked by process 9700.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,4) in relation "tbl"

("Works" with just two transactions, too. It's just easier to pull off with a third one.)

How to simulate deadlock in PostgreSQL?

But we cannot reliably predict whether session 2 or session 3 gets the boot.

Analyze whether you can avoid the problem altogether. Ideally, you don't update columns involved in the sort order - or sort by columns you don't update (like a surrogate PK). Then you are safe. Else you have to weigh costs and consequences. You speak of a "rather large" transaction and "relatively fast" transactions ...

For the "rather large" transaction you might take a SAVEPOINT in an SQL transaction. Or use an EXCEPTION clause in a PL/pgSQL code block. Then try to obtain the required locks with NOWAIT, catch the SQLSTATE code (error code) 55P03 (lock_not_available), in a loop until successful. Or leave out NOWAIT and catch 40P01 (deadlock_detected). This adds considerable overhead, but may be worth it to protect a "rather large" transaction. Generally, if deadlocks happen with your workload, be prepared to repeat transactions that fail.

Avoiding deadlocks when locking multiple rows without using NOWAIT

1 Answers1

Why can there still be deadlocks?

Linked