An InnoDB deadlock occurs when transactions form a cycle of incompatible lock requests. With deadlock detection enabled, InnoDB chooses a victim and rolls back that entire transaction so another participant can proceed. The application receives error 1213 with SQLSTATE 40001. Retrying only the statement that reported the error omits earlier work that was rolled back with it.

When it breaks

Do not assign one rollback rule to every lock error. Under the default innodb_rollback_on_timeout=OFF, a lock-wait timeout rolls back the waiting statement while earlier statements in the transaction remain pending. Enabling that server option changes timeout rollback to the whole transaction. A deadlock already rolls back the whole victim transaction regardless of that default timeout behavior.

Victim selection is not a contract that a particular session always loses. Applications must inspect the actual error and retry the complete logical transaction with bounded attempts. Keep irreversible external effects out of an automatically replayed transaction unless they have a separate deduplication contract. A disconnected commit response is also not equivalent to a confirmed deadlock rollback.

Check it quickly

Use three fresh administrative MySQL 8.4 client sessions, A, B, and observer O, against a disposable instance. Start the clients in autocommit mode. Disable client automatic reconnection so session identity remains stable. The recorded run used MySQL 8.4.11. In O, create an unused fixture schema and verify detection is enabled and timeout rollback uses its default statement scope:

CREATE DATABASE xbcatm_deadlock_lab;
USE xbcatm_deadlock_lab;
CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1,100),(2,100);
SELECT @@innodb_deadlock_detect,@@innodb_rollback_on_timeout;

The settings should be one and zero. In A, start an operation adding ten to both accounts, initially locking account one:

USE xbcatm_deadlock_lab;
SET SESSION innodb_lock_wait_timeout=30;
SET @increment=10;
START TRANSACTION;
UPDATE accounts SET balance=balance+@increment WHERE id=1;

In B, start an operation adding one hundred, initially locking account two:

USE xbcatm_deadlock_lab;
SET SESSION innodb_lock_wait_timeout=30;
SET @increment=100;
START TRANSACTION;
UPDATE accounts SET balance=balance+@increment WHERE id=2;

In A, request the row B holds. This statement waits:

UPDATE accounts SET balance=balance+@increment WHERE id=2;

Before A’s thirty-second lock timeout, run the opposing request in B:

UPDATE accounts SET balance=balance+@increment WHERE id=1;

One session receives error 1213; identify it as the victim. The other update completes. Run this only in the surviving session:

COMMIT;

In O, inspect the committed state:

SELECT id,balance FROM accounts ORDER BY id;

Both balances are 110 if A survived, or 200 if B survived. The victim’s earlier update is absent too. The recorded run selected B, leaving 110 in both rows. In the victim’s existing session, its session variable still records the original increment. Retry the whole operation, now acquiring rows in consistent order:

SELECT @increment;
START TRANSACTION;
UPDATE accounts SET balance=balance+@increment WHERE id=1;
UPDATE accounts SET balance=balance+@increment WHERE id=2;
COMMIT;

In O, verify the combined outcome:

SELECT id,balance FROM accounts ORDER BY id;

Both balances must now be 210. Next, contrast a timeout. In O, reset the fixture:

UPDATE accounts SET balance=100;

In A, hold account one:

START TRANSACTION;
UPDATE accounts SET balance=balance+1 WHERE id=1;

In B, change account two and set a short wait:

SET SESSION innodb_lock_wait_timeout=1;
START TRANSACTION;
UPDATE accounts SET balance=balance+5 WHERE id=2;

Still in B, request A’s row:

UPDATE accounts SET balance=balance+5 WHERE id=1;

This produces error 1205 with SQLSTATE HY000. In B, inspect its own pending work:

SELECT id,balance FROM accounts ORDER BY id;

B reads 100 and 105. In O, committed readers still see 100 and 100:

SELECT id,balance FROM accounts ORDER BY id;

The timeout did not undo B’s earlier five-unit change. Explicitly discard the remaining transactions, first in B:

ROLLBACK;

Then in A:

ROLLBACK;

Finally, in O, confirm both original balances and remove the fixture:

SELECT id,balance FROM accounts ORDER BY id;
DROP DATABASE xbcatm_deadlock_lab;

The check distinguishes rollback scope through actual values, rather than relying only on an error message. Stop on unexpected results and roll back open fixture transactions before cleanup.

Reduce cycles without assuming they disappear

Acquire shared business resources in consistent order, keep transactions short, and use appropriate indexes to avoid locking more records than the operation needs. Range operations and secondary-index maintenance can add lock interactions beyond the two primary-key rows shown here. Consistent application ordering reduces a known cycle; it is not proof that every possible deadlock has been removed.

Use SHOW ENGINE INNODB STATUS to examine the latest detected deadlock, and capture broader history when incident frequency warrants it. Correlate the involved statements and lock order with the application transaction, not just the final failed statement. Retry complete work within a deadline, distinguish timeout semantics, and verify the business result before treating recovery as successful.

Sources