14.2.15. InnoDB Error Handling
Error handling in InnoDB is not always the same
as specified in the SQL standard. According to the standard, any
error during an SQL statement should cause the rollback of that
statement. InnoDB sometimes rolls back only
part of the statement, or the whole transaction. The following
items describe how InnoDB performs error
handling:
If you run out of file space in the tablespace, a MySQL
Table is full error occurs and
InnoDB rolls back the SQL statement.
-
A transaction deadlock causes InnoDB to
roll back the entire transaction. In the case of a lock wait
timeout, InnoDB rolls back only the most
recent SQL statement.
When a transaction rollback occurs due to a deadlock or lock
wait timeout, it cancels the effect of the statements within
the transaction. But if the start-transaction statement was
START TRANSACTION or
BEGIN statement, rollback does not cancel
that statement. Further SQL statements become part of the
transaction until the occurrence of COMMIT,
ROLLBACK, or some SQL statement that causes
an implicit commit.
A duplicate-key error rolls back the SQL statement, if you
have not specified the IGNORE option in
your statement.
A row too long error rolls back the SQL
statement.
Other errors are mostly detected by the MySQL layer of code
(above the InnoDB storage engine level),
and they roll back the corresponding SQL statement. Locks are
not released in a rollback of a single SQL statement.
During implicit rollbacks, as well as during the execution of an
explicit ROLLBACK SQL command, SHOW
PROCESSLIST displays Rolling back in
the State column for the relevant connection.
