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 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
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.