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.
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.
User Comments
Add your own comment.