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
statement or a lock set by a storage engine other than
InnoDB is involved. Resolve these situations
by setting the value of 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”.