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
InnoDB sometimes rolls back only
part of the statement, or the whole transaction. The following
items describe how
InnoDB performs error
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
roll back the entire transaction. Retry the whole transaction
when this happens.
A lock wait timeout causes
InnoDB to roll
back only the single statement that was waiting for the lock
and encountered the timeout. (Until MySQL 5.0.13
InnoDB rolled back the entire transaction
if a lock wait timeout happened. You can restore this behavior
by starting the server with the
option, available as of MySQL 5.0.32.) You should normally
retry the statement if using the current behavior or the
entire transaction if using the old behavior.
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
statement, rollback does not cancel that statement. Further
SQL statements become part of the transaction until the
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
row too long error rolls back the SQL
Other errors are mostly detected by the MySQL layer of code
InnoDB storage engine level),
and they roll back the corresponding SQL statement. Locks are
not released in a rollback of a single SQL statement.