An AI service may commit database state before triggering payments, video rendering or external agent actions. Concurrent workers can deadlock over the same job or account rows. MySQL documents how InnoDB handles these conflicts. A reliable retry protocol needs to distinguish whole-transaction rollback, lock wait timeout and side effects that already happened outside the database.
Understand rollback semantics
InnoDB can detect a deadlock and roll back the entire victim transaction. Its default handling of a lock wait timeout differs, so recovery code must distinguish the error cases. After a deadlock, retry the whole transaction rather than only its last statement. Collect the SQLSTATE, server error and transaction identifier, then compare the expected records with the actual database state before another attempt.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
Avoid holding locks through AI inference
Never keep a write transaction open while waiting for a model response, media renderer or remote service. Acquire necessary rows, perform a short state change and commit before initiating slow external work. When transactions touch several objects, prefer stable lock ordering and indexed predicates. MySQL recommends short transactions and consistent access, but even a clean design requires a retry path.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
Separate retries from external side effects
A transaction failure does not undo an API request that already succeeded. Use durable idempotency keys or an outbox so a billing command and a social notification cannot be repeated when a database transaction retries. Reconciliation should check the public operation identity before sending again. Do not use a random new identifier on every attempt, because that turns a safety mechanism into a duplicate-job generator.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
The relevant technical rules are detailed in MySQL 8.4: InnoDB Error Handling (accessed 2026-11-04).
Cap retry budgets
Unbounded retries magnify contention and conceal broken transaction design. Use bounded attempts with randomized backoff and a visible queue for unresolved cases. Record retry counts, deadlock rates by statement family and jobs that exceed the allowed recovery window. A successful subsequent attempt should not erase the evidence that the original transaction failed. Redact private conversation data from SQL diagnostics.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
Reproduce the conflict with two workers
In a sandbox, have worker A lock rows one then two, and worker B lock rows two then one. Observe a deadlock and verify that the chosen victim retries every statement within its transaction. Add a separate lock-wait-timeout scenario. Inspect any payment, webhook or generated-media actions for duplication. Database mocks alone cannot demonstrate these concurrency properties.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
Write a decision table for incidents
For each failure, ask whether the database committed, whether an external operation executed and whether a user-facing acknowledgement was delivered. The combinations determine whether to retry, reconcile or escalate. Store enough versioned evidence to let a second engineer reach the same conclusion. Treat uncertain external status as a reconciliation task, not permission to repeat an irreversible action.
Validation should record the expected state, the observed state, the identity of the responsible component and a concrete rollback condition. Repeat the scenario with a delayed callback and a duplicate request so that a passing ordinary case does not mask an unstable failure path. Compare results against a documented baseline and preserve dated evidence for later audits.
Worked scenario and acceptance review
Imagine two workers processing the same account’s AI-video credits. One records a media job and another adjusts a remaining-credit balance. Both access the job and account tables, but in opposite order. InnoDB may pick a victim and roll back that worker’s transaction. An application that retries only the credit update can lose the job record; an application that retries the entire PHP handler might send the external rendering command twice. The safer design assigns a stable logical operation ID and writes one outbox event only when the accounting transaction commits. A separate dispatcher can retry delivery with the same identifier. This example is a testing scenario rather than a measured outage. Track the number of deadlock victims, stale workers, compensating actions and duplicate external outcomes while exercising the test under the production schema and relevant isolation settings.
Sources
- MySQL 8.4: InnoDB Error Handling (accessed 2026-11-04)
- MySQL 8.4: Deadlocks in InnoDB (accessed 2026-11-04)
All example operations and budgets are illustrative, not claimed production data.