Designing Safe Transaction Retries for MySQL Deadlocks

When code using MySQL's InnoDB storage engine encounters a deadlock, two transactions are waiting for locks held by each other. The code must rerun the entire transaction after a deadlock. A transaction is a group of related database operations that the application commits or rolls back as a unit. InnoDB rolls back the full transaction it selects as the deadlock victim, undoing earlier work along with the failed statement.

The code must also avoid repeating external actions, such as sending email or charging a payment provider, when it retries. Start a new transaction for each attempt and rerun all its database reads and writes. Perform external work only after the transaction commits, or record the intended action in the database transaction and perform it after commit.

Rerun the complete transaction

MySQL’s InnoDB error-handling guidance says to retry the entire transaction after a deadlock. InnoDB rolls back the victim transaction, including its earlier writes. Earlier reads matter too. The code may have used them to decide which later writes to make, so the next attempt must repeat those reads and decisions as well as the writes.

A retry wrapper should create a new transaction for each attempt and place the complete database operation inside it:

for each permitted attempt:
    start a new transaction
    try:
        perform all reads and writes for the operation
        commit
        return success
    catch error:
        if error is not a confirmed deadlock or no attempts remain:
            propagate the error
        otherwise:
            start the next attempt with a new transaction

This is conceptual pseudocode, not a connector-specific API. Use the transaction methods and error format your database driver provides. MySQL documents transaction control through statements such as START TRANSACTION, COMMIT, and ROLLBACK, and notes that client APIs also provide transaction-start methods in its transaction control documentation. Check how your driver reports transaction state and whether you can reuse the connection after an error.

Retry only confirmed deadlocks

For MySQL 8.4, the server identifies a deadlock with error 1213, symbol ER_LOCK_DEADLOCK, and SQLSTATE 40001. Use the structured error code or symbol exposed by the driver when possible; matching message text is less reliable. The MySQL server error reference lists these identifiers.

Do not treat every lock-related error as a deadlock. A lock-wait timeout has different rollback behavior by default: InnoDB rolls back the timed-out statement, while --innodb-rollback-on-timeout changes that behavior to roll back the transaction. MySQL’s error-handling guidance describes different retry scopes for those cases. For a timeout, check the server setting and the transaction state your driver reports before choosing how to handle it.

A connection failure during COMMIT needs separate handling. If the client loses its connection, it may not know whether the transaction committed. Do not treat this as a confirmed deadlock. Use a separate recovery strategy for cases where the commit result is unknown.

Keep replayable work inside the database boundary

A database rollback cannot undo an action that an external service has already taken. If transaction code charges a card or sends a message before the database commits, a deadlock rolls back the database changes but leaves the external action complete. A retry then repeats that action.

Instead, write a database record describing the intended external action as part of the transaction; after it commits, a dispatcher reads the record and performs the action. A transactional outbox is one way to use this pattern. Give each event or operation a stable identifier, and make the dispatcher or receiving service handle duplicate deliveries safely. This is an application-level design recommendation; MySQL does not coordinate its transaction with an external provider.

The database must also be able to undo its changes for the code to replay the transaction safely. MySQL warns that ROLLBACK does not undo changes to nontransactional tables, and that statements such as some forms of DDL cannot be rolled back. Keep writes to nontransactional tables and statements that cannot be rolled back out of any transaction the code needs to replay. See MySQL’s documentation on transaction control and statements that cannot be rolled back.

Limit attempts and reduce contention

Set a maximum number of attempts so repeated deadlocks do not keep a request running indefinitely. MySQL does not prescribe an attempt count, delay, or backoff schedule. Choose those values to meet your code's latency and load requirements. When the code reaches the limit, return the failure or pass it on to the caller.

Retries handle deadlocks that still occur, while good transaction design can reduce their frequency. Keep transactions short, commit related changes promptly, and access rows or tables in a consistent order where practical. MySQL recommends these practices in its guidance on minimizing and handling deadlocks.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com