14.7.5.2 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.
When 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.
If a 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.
If the 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.