db: recorders of result_latest still deadlock under a burst of verdicts (12 in ten minutes, 2026-11-12 22:25Z) #51

New issue

Open

opened 2026-11-12 22:42:55 +00:00 by xemmerling · 0 comments

xemmerling commented

2026-11-12 22:42:55 +00:00

Owner

Seen on t.tilo.so (86fa55c0) on 2026-11-12: PostgreSQL ended 12 transactions for a deadlock between 22:25:37Z and 22:30Z, none in the two hours before. In those ten minutes 4,128 checks were told to CI heads. The server logged twice driven again once: recording a verdict: db error: ERROR: deadlock detected (22:29:35Z, 22:37:35Z); no check was told an error for it this time.

The statements, from the database's log, both in Db::record_in (src/db/results.rs):

SELECT trust, run FROM result_latest WHERE key = $1 FOR UPDATE
INSERT INTO result_latest (key, trust, ...) VALUES (...) ON CONFLICT (key, trust) DO UPDATE SET ...

Shapes: two processes each waiting for the other's transaction, one in the SELECT and one in the INSERT; and rings of three to five processes waiting on tuples of result_latest and on each other's transactions ("while locking tuple (63282,1) in relation result_latest").

72849b76 made a batch record its keys in their order (record_many), which ended the deadlock of 2026-11-07 between two batches. What still deadlocks is not yet named. To test, not to assume: a transaction that records more than one batch, or a batch and single records, so its keys are not in one order over the whole transaction; the other writers of the table that do not go through record_many (src/ci/bisect.rs, src/db/measure.rs); and the order in which FOR UPDATE takes the rows of one key that has several trusts while another recorder inserts a new trust's row for it.

Wanted: every writer of result_latest takes its locks in one order for the whole transaction, shown by a test with two recorders that deadlocks today. No retry over it.

Seen on t.tilo.so (86fa55c0) on 2026-11-12: PostgreSQL ended 12 transactions for a deadlock between 22:25:37Z and 22:30Z, none in the two hours before. In those ten minutes 4,128 checks were told to CI heads. The server logged twice `driven again once: recording a verdict: db error: ERROR: deadlock detected` (22:29:35Z, 22:37:35Z); no check was told an error for it this time. The statements, from the database's log, both in `Db::record_in` (`src/db/results.rs`): - `SELECT trust, run FROM result_latest WHERE key = $1 FOR UPDATE` - `INSERT INTO result_latest (key, trust, ...) VALUES (...) ON CONFLICT (key, trust) DO UPDATE SET ...` Shapes: two processes each waiting for the other's transaction, one in the SELECT and one in the INSERT; and rings of three to five processes waiting on tuples of `result_latest` and on each other's transactions ("while locking tuple (63282,1) in relation result_latest"). 72849b76 made a batch record its keys in their order (`record_many`), which ended the deadlock of 2026-11-07 between two batches. What still deadlocks is not yet named. To test, not to assume: a transaction that records more than one batch, or a batch and single records, so its keys are not in one order over the whole transaction; the other writers of the table that do not go through `record_many` (`src/ci/bisect.rs`, `src/db/measure.rs`); and the order in which `FOR UPDATE` takes the rows of one key that has several trusts while another recorder inserts a new trust's row for it. Wanted: every writer of `result_latest` takes its locks in one order for the whole transaction, shown by a test with two recorders that deadlocks today. No retry over it.

xemmerling referenced this issue

2026-11-13 02:19:27 +00:00

node/admit: 7ef99e3f held CI out of the node while people used half of it on 11-12 (CI job starts 148 to 1.2 a minute); did not repeat on eeb2996a on 11-13, see the update #52

xemmerling referenced this issue

2026-11-13 05:43:51 +00:00

declared keys: two runs keeping the same builds deadlock on declared_builds, their batches lock the same rows in different orders (one in three hours, 2026-11-13 05:35Z) #55