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.
For techniques to organize database operations to avoid deadlocks, see Section 188.8.131.52, “How to Cope with Deadlocks”.