MySQL :: MySQL 8.0 Reference Manual :: 17.7.5.2 Deadlock Detection (original) (raw)

17.7.5.2 Deadlock Detection

When deadlock detection is enabled (the default),InnoDB automatically detects transactiondeadlocks 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 ifinnodb_table_locks = 1 (the default) andautocommit = 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 thanInnoDB is involved. Resolve these situations by setting the value of theinnodb_lock_wait_timeout system variable.

If the LATEST DETECTED DEADLOCK section ofInnoDB 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”.

Disabling Deadlock Detection

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 theinnodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using theinnodb_deadlock_detect variable.