14.22.4 InnoDB Error Handling
The following items describe how
InnoDB performs error handling.
InnoDB sometimes rolls back only the statement that failed, other times it rolls back the entire transaction.
If you run out of file space in a tablespace, a MySQL
Table is fullerror occurs and
InnoDBrolls back the SQL statement.
A lock wait timeout causes
InnoDBto roll back only the single statement that was waiting for the lock and encountered the timeout. (To have the entire transaction roll back, start the server with the
--innodb-rollback-on-timeoutoption.) Retry the statement if using the current behavior, or the entire transaction if using
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 a
BEGINstatement, rollback does not cancel that statement. Further SQL statements become part of the transaction until the occurrence of
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
IGNOREoption in your statement.
row too long errorrolls back the SQL statement.
Other errors are mostly detected by the MySQL layer of code (above the
InnoDBstorage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement.