13.2.9.8 Deadlock Detection and Rollback

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

Combine queries for Insert and Select always obeys Innodb locking rulesif one of the source table is based on Innodb engine.It is also possible that the INSERT activity applicable to TEMPORARYtable which is not InnoDB engine. It is also possible that in SELECTsection with INNODB, some other TEMPORARY Tables are used.Devang Modi