Understanding MySQL InnoDB Deadlocks: Concepts, Examples, and Log Analysis

Deadlock Concept

In a database deadlock, two or more transactions wait for each other's resources, forming a circular wait that prevents any of them from proceeding.

Database Handling of Deadlocks

When a deadlock occurs, MySQL InnoDB automatically detects it and sacrifices one transaction, rolling it back and returning ERROR 1213 to the client. No manual intervention is required for most cases.

Example Scenarios

Illustrative diagrams (shown below) depict typical deadlock situations.

Log Analysis – Example 1

Part 1: Transaction 1 is executing

UPDATE info_users SET mobile='18514656666' WHERE mobile='18514656620'

, requesting an exclusive (X) lock on index IDX_MOBILE, shown as lock_mode X waiting in the log.

Part 2: Transaction 2 holds an X lock on the same index (record lock) from its own UPDATE, and is waiting for an X lock on index GEN_CLUST_INDEX of table info_area caused by a DELETE FROM info_area WHERE id=1 statement.

Log Analysis – Example 2

Transaction 1 runs DELETE FROM users WHERE uid='bbb', requesting an X lock on index UID. Transaction 2 holds an X lock on the same index (record lock) from its own DELETE, and is waiting for a shared (S) lock on UID generated by an INSERT INTO users VALUES(2,'bbb') statement.

The S lock appears because the INSERT performs a duplicate‑key check on the unique uid field, which requires an S lock to prevent concurrent modifications.

The S lock cannot be granted because an earlier X lock is still pending, creating a circular wait and resulting in a deadlock.

Conclusion

By examining the deadlock log, you can identify the exact statements and lock types involved, reconstruct the circular wait chain, and understand the root cause of the deadlock, enabling targeted troubleshooting and prevention.