MySQL 9.0 Release Notes
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.
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 17.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
variable.