Movatterモバイル変換

Whendeadlock 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 MySQLLOCK 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 theLATEST DETECTED DEADLOCK section ofInnoDB Monitor output includes a message statingTOO 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, seeSection 17.7.5, “Deadlocks in InnoDB”.