MySQL Manual
7 MySQL Table Types
- 7.5
InnoDBTables- 7.5.9 InnoDB Transaction Model
- 7.5.9.1 Consistent Read
- 7.5.9.2 Locking Reads
- 7.5.9.3 Next-key Locking: Avoiding the Phantom Problem
- 7.5.9.4 Locks Set by Different SQL Statements in
InnoDB - 7.5.9.5 Deadlock Detection and Rollback
- 7.5.9.6 An Example of How the Consistent Read Works in
InnoDB - 7.5.9.7 How to cope with deadlocks?
- 7.5.9.8 Performance Tuning Tips
- 7.5.9.9 The
InnoDBMonitor
7.5.9.5 Deadlock Detection and Rollback
InnoDB automatically detects a deadlock of transactions and rolls back a
transaction or transactions to prevent the deadlock. Starting from
version 4.0.5, InnoDB will try to pick small transactions to roll
back. The size of a transaction is determined by the number of rows
it has inserted, updated, or deleted. Previous 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.
InnoDB cannot detect deadlocks where a lock set by a MySQL
LOCK TABLES statement is involved, or if a lock set
in another storage engine than InnoDB is involved. You have to resolve
these situations using innodb_lock_wait_timeout set in
`my.cnf'.
When InnoDB performs a complete rollback of a transaction, all the
locks of 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 SQL statement may be preserved. This is because InnoDB
stores row locks in a format where it cannot afterwards know which was
set by which SQL statement.
