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
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
stores row locks in a format such that it cannot know afterward
which lock was set by which statement.
SELECT calls a stored
function in a transaction, and a statement within the function
fails, that statement rolls back. Furthermore, if
executed after that, the entire transaction rolls back.
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.8.5, “Deadlocks in InnoDB”.