Fun MySQL fact of the day: nothing isn't free

Nothing isn't free in InnoDB. When a transaction rolls-back in MySQL/InnoDB, the "or-nothing" part of "all-or-nothing" is close to being the most expensive thing you can do in MySQL. While we can dig deep into the internals to find out why, we've already gone over all the building blocks, and we just need to put them together.

When I introduced the concept of how MySQL uses MVCC, I didn't elaborate on where the "new" and "old" record versions are stored. Now I will. When a DML (Data Manipulation Language) statement is executed, the "new" version of a record is simply stored in the index itself and the record is write-locked so no other changes can occur until the transaction is committed or rolled-back. But before this happens, InnoDB first copies the original record into something called an "undo log", which is dedicated to the current transaction. Within the "new" record version, a pointer to the "old" version is recorded. And then when the "new" version becomes the "old" version, the same thing happens, creating a linked list of old versions n entries long (where n is the number of versions that are still referenced by any query). The collection of these undo logs is called the "history list".

As you may imagine, this can get pretty expensive having to keep around multiple copies of a single record. But it gets even more expensive in a not-so-obvious way: if a record, r was changed hours ago in an uncommitted transaction, r.trx, InnoDB needs to keep the old version of r around until r.trx is committed. However, InnoDB still must honour the isolation of r.trx, meaning it must also keep all versions of all records changed by all subsequent transactions since there is no way to know what r.trx will read prior to committing. Worse, still: if another transaction, s is gigantic, all queries must traverse the history graph of any queried row, causing marked latency on read queries.

There are a lot more details to cover on the topic, but for now: keep your transaction small and short-lived because nothing isn't free.