Deadlocks
A deadlock occurs when two transactions each wait for a lock held by the other. InnoDB detects and resolves deadlocks by rolling back the transaction with fewer row locks.
MySQL
Advanced
5 min read
Deadlocks
Example
-- Session 1 -- Session 2
START TRANSACTION; START TRANSACTION;
UPDATE a SET x=1 WHERE id=1; UPDATE a SET x=2 WHERE id=2;
-- waiting for id=2 lock... -- waiting for id=1 lock...
UPDATE a SET x=1 WHERE id=2; UPDATE a SET x=2 WHERE id=1;
-- InnoDB detects deadlock → rolls back one session
-- Prevention: always lock rows in the same order across transactions Pro Tip
Check the last deadlock: SHOW ENGINE INNODB STATUS — look for the LATEST DETECTED DEADLOCK section.