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
row locks in a format such that it cannot know afterward which
lock was set by which statement.
As of MySQL 5.1.24, if a
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. Before
5.1.24, the failed statement did not roll back when it failed
(even though it might ultimately get rolled back by a
that rolls back the entire transaction).
For techniques to organize database operations to avoid deadlocks, see Section 18.104.22.168, “How to Cope with Deadlocks”.