InnoDB
automatically detects transaction
deadlocks and rolls back a transaction or transactions to break
the deadlock. Starting from MySQL 4.0.5,
InnoDB
tries to pick small transactions to
roll back, the size of a transaction being determined by the
number of rows inserted, updated, or deleted. Prior to 4.0.5,
InnoDB
always rolled back the transaction
whose lock request was the last one to build a deadlock, that
is, a cycle in the “waits-for” graph of
transactions.
Beginning with MySQL 4.0.20 and 4.1.2, InnoDB
is aware of table locks if innodb_table_locks =
1
(the default) and autocommit
= 0
, and the MySQL layer above
InnoDB
knows about row-level locks. Before
that, InnoDB
cannot detect deadlocks where a
table lock set by a MySQL LOCK
TABLES
statement is involved, or if a lock set by
another storage engine than InnoDB
is
involved. You have to 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.
User Comments
Add your own comment.