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 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 or a timeout in a lock wait causes
InnoDB
to roll back the whole transaction.
Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. You can make them less likely by doing as little work as possible between the first change to data during a transaction and the commit, so the locks are held for the shortest possible time and for the smallest possible number of rows. Sometimes splitting work between different transactions may be practical and helpful.
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 such implicit rollbacks, as well as during the explicit
ROLLBACK
SQL
statement, SHOW PROCESSLIST
displays "Rolling back" in the State
column for
the connection (starting from MySQL 4.1.8).
User Comments
Add your own comment.