18.104.22.168 Deadlock Detection and Rollback
When deadlock detection is enabled (the default),
InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.
InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
InnoDB is aware of table locks if
innodb_table_locks = 1 (the default) and
autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise,
InnoDB cannot detect deadlocks where a table lock set by a MySQL
LOCK TABLES statement or a lock set by a storage engine other than
InnoDB is involved. Resolve these situations by setting the value of the
innodb_lock_wait_timeout system variable.
InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because
InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
SELECT calls a stored function in a transaction, and a statement within the function fails, that statement rolls back. Furthermore, if
ROLLBACK is executed after that, the entire transaction rolls back.
LATEST DETECTED DEADLOCK section of
InnoDB Monitor output includes a message stating, “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,” this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.
For techniques to organize database operations to avoid deadlocks, see Section 14.7.5, “Deadlocks in InnoDB”.
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the
innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the
innodb_deadlock_detect configuration option.