Rechercher dans le manuel MySQL
15.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 15.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 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.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-deadlock-detection.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.