Fun MySQL fact of the day: implicit rollbacks

Last Thursday, I suggested that you may not always want your transactions automatically rolled-back on an ACID violation, and, on Monday, I hinted that transaction rollbacks are nearly the most expensive thing you can do in MySQL/InnoDB. And, well, it's because of the very same "undo logs" about which we have only considered as a means to implement MVCC. Well, they weren't poorly named: they are, in fact also used to "undo" a transaction when it rolls-back.

We've discussed that when a DML (Data Manipulation Language) statement is executed, the changes are immediately applied to the InnoDB index (in InnoDB, a "table" is an index) and an undo log record is created with enough information to "undo" the change. Should, at this point, a transaction commit, the consequences are trivial: some bookkeeping work is performed and we move on to the next transaction. However, on a roll-back, InnoDB must undo the changes in the opposite order of which they were performed, essentially doubling the amount of work it took to make the original changes. As such, roll-backs are a fantastic way to destroy your database's performance. For the most part, InnoDB helps you optimise for this by documenting its error handling approach:

• On a duplicate key error, only the statement is rolled-back.
• If an inserted row is too large, only the statement is rolled-back.
• If you run out of disk space, only the statement is rolled-back.
• If a deadlock occurs, the entire transaction is rolled-back.
• If a lock wait takes too long, the behaviour depends on it depends on the setting of innodb-rollback-on-timeout.
• If the server stops or crashes before a commit (or before a statement run in autocommit completes), the entire transaction is rolled-back when the server starts up again.

As such, a rollback can happen at any time, even if an application doesn't explicitly ROLLBACK. Isn't that fun?