Bug: Reorder and remove-ticked on the same shopping list can deadlock #27

New issue

Open

opened 2026-10-03 17:38:04 +00:00 by araemer · 0 comments

araemer commented

2026-10-03 17:38:04 +00:00

Owner

A reorder and "Remove ticked items" on the same shopping list can deadlock. PostgreSQL then aborts one of the two, and that request fails with a 500. The code is on stage since spec 001. The window is very narrow, and the problem was found by reading the code, not by reproducing it.

How it happens

All three steps must fall within the time one reorder takes:

A reorder takes the list lock (SELECT … FOR UPDATE on the list row), reads the open items, e.g. Milk and Bread, and renumbers them one after the other.
Both items are ticked. Tick takes no list lock (docs/adr/2026-10-02-shopping-list-item-order.md), so this goes through.
"Remove ticked items" deletes Milk and Bread in one statement, also without the list lock. If it locks Bread first while the reorder has locked Milk first, each waits for the other's row.

Possible fixes

Remove-ticked takes the list lock before it deletes. That changes docs/adr/2026-10-02-shopping-list-item-order.md, which names the writes that take the lock, so it needs a new ADR that supersedes that part.
Or both writes lock the item rows in one fixed order (e.g. SELECT … ORDER BY id FOR UPDATE first).

Context

Found by the backend planner for spec 002 (shopping list overview previews). It does not involve the change row that spec 002 adds, so it is not fixed there. The integration test matrix of spec 002 (removeTicked × reorder) is the place to cover it once it is fixed; until then that pair may fail there with 40P01.

A reorder and "Remove ticked items" on the same shopping list can deadlock. PostgreSQL then aborts one of the two, and that request fails with a 500. The code is on `stage` since spec 001. The window is very narrow, and the problem was found by reading the code, not by reproducing it. ## How it happens All three steps must fall within the time one reorder takes: 1. A reorder takes the list lock (`SELECT … FOR UPDATE` on the list row), reads the open items, e.g. Milk and Bread, and renumbers them one after the other. 2. Both items are ticked. Tick takes no list lock (`docs/adr/2026-10-02-shopping-list-item-order.md`), so this goes through. 3. "Remove ticked items" deletes Milk and Bread in one statement, also without the list lock. If it locks Bread first while the reorder has locked Milk first, each waits for the other's row. ## Possible fixes - Remove-ticked takes the list lock before it deletes. That changes `docs/adr/2026-10-02-shopping-list-item-order.md`, which names the writes that take the lock, so it needs a new ADR that supersedes that part. - Or both writes lock the item rows in one fixed order (e.g. `SELECT … ORDER BY id FOR UPDATE` first). ## Context Found by the backend planner for spec 002 (shopping list overview previews). It does not involve the change row that spec 002 adds, so it is not fixed there. The integration test matrix of spec 002 (`removeTicked` × `reorder`) is the place to cover it once it is fixed; until then that pair may fail there with `40P01`.

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

araemer/recipe-backend#27

No description provided.

Rows
Columns