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. You must 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.
As of MySQL 5.1.24, 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. Before
5.1.24, the failed statement did not roll back when it failed
(even though it might ultimately get rolled back by a
ROLLBACK
later
that rolls back the entire transaction).
User Comments
Add your own comment.