Transient MySQL deadlocks are now retried automatically
Shipped 2026-08-03
Concurrent approve-manually requests could fail with ER_LOCK_DEADLOCK
(errno 1213). The cause is inside APP_LOGBOOK_CREATE: its self-referential
INSERT ... SELECT ... WHERE NOT EXISTS against channel_logbook takes shared
gap locks under REPEATABLE READ, so two approvals landing at once can deadlock.
InnoDB resolves this by rolling back the losing transaction whole and expects the
caller to re-issue it — which the API never did, so the error reached the member
as a 500.
executeQuery in api/src/shared/utils/database/mysql.js now wraps
pool.execute in a bounded retry:
- Retries
ER_LOCK_DEADLOCK(1213) only, up to 3 attempts. - Exponential backoff with jitter between attempts (~50ms, ~100ms base, randomised) so contending requests don’t collide again on the retry.
- Each retry logs a warning against the query hash.
- Lock-wait timeouts (1205) are deliberately not retried. With
innodb_rollback_on_timeout=OFFa timeout rolls back only the last statement, not the transaction, so blindly replaying it is unsafe.
This is safe to apply at the shared query layer because a deadlock rolls back the
entire transaction, and every transaction reaching executeQuery is
self-contained inside a stored procedure — there is no partially-applied state to
replay onto.
The change is platform-wide: any query issued through executeQuery inherits the
retry, not just the logbook path.