GenerateSuiteIndexes task results in error due to deadlock

Describe the bug

Running the update_suites workflow fails because of a sub task.

Error on web ui:

Execution failed: deadlock detected
DETAIL:  Process 190650 waits for ShareLock on transaction 30101; blocked by process 190610.
Process 190610 waits for ShareLock on transaction 30052; blocked by process 190650.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (9,15) in relation "db_workrequest"
SQL statement "SELECT 1 FROM ONLY "public"."db_workrequest" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

In /var/log/debusine/server/debug.log:

2025-09-26 10:37:59,627 [celery/185390/140611923660864] ERROR: Error running work request Server/generatesuiteindexes (76): Execution failed: deadlock detected
DETAIL:  Process 190650 waits for ShareLock on transaction 30101; blocked by process 190610.
Process 190610 waits for ShareLock on transaction 30052; blocked by process 190650.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (9,15) in relation "db_workrequest"
SQL statement "SELECT 1 FROM ONLY "public"."db_workrequest" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

2025-09-26 10:37:59,709 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 76 as completed
2025-09-26 10:37:59,735 [work_requests/185378/140611923660864] DEBUG: Marked WorkRequest 71 as pending
2025-09-26 10:37:59,736 [work_requests/185388/140611923660864] DEBUG: Marked WorkRequest 74 as pending
2025-09-26 10:37:59,735 [work_requests/185395/140611923660864] DEBUG: Marked WorkRequest 59 as pending
2025-09-26 10:37:59,736 [work_requests/185397/140611923660864] DEBUG: Marked WorkRequest 62 as pending
2025-09-26 10:37:59,739 [work_requests/185361/140611923660864] DEBUG: Marked WorkRequest 68 as pending
2025-09-26 10:37:59,740 [work_requests/185399/140611923660864] DEBUG: Marked WorkRequest 65 as pending
2025-09-26 10:37:59,743 [work_requests/185394/140611923660864] DEBUG: Marked WorkRequest 56 as pending
2025-09-26 10:37:59,766 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 54 as aborted (from status aborted)
2025-09-26 10:37:59,772 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 78 as aborted (from status aborted)
2025-09-26 10:37:59,773 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:37:59,788 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 77 as aborted (from status aborted)
2025-09-26 10:37:59,799 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 78 as aborted (from status aborted)
2025-09-26 10:37:59,825 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:37:59,870 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:37:59,916 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:37:59,966 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,012 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,058 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,105 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,160 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,236 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,284 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,331 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,378 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,429 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,481 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,527 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,573 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,621 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,667 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,714 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,762 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,813 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,860 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,905 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:00,957 [scheduler/185386/140229826584640] DEBUG: [SCHEDULER] Failed to lock WorkRequest 56
2025-09-26 10:38:01,969 [work_requests/185390/140611923660864] DEBUG: Marked WorkRequest 54 as aborted (from status aborted)
2025-09-26 10:38:02,033 [work_requests/185002/140485262456512] DEBUG: Marked WorkRequest 56 as running
2025-09-26 10:38:02,403 [work_requests/185002/140485262456512] DEBUG: Marked WorkRequest 56 as completed

journalctl -u debusine-server-scheduler.service:

Link

journalctl -u debusine-server-celery.service:

Link

SELECT * FROM db_workrequest WHERE id >= 54;:

Link

How to reproduce the bug

Could not reproduce. I have a script which sets up multiple workspaces, with the last thing done being creating an update_suites workflow in each of them. Upon trying to reproduce by running again, the error did not occur.

Runtime environment

Running debusine-server, debusine-worker, and debusine-signing locally in a virtual machine.

Operating system

Debian bookworm

Versions of debusine and its dependencies

Debusine 0.12.2+salsaci+20250925+8459