Fun MySQL fact of the day: InnoDB read-only optimisation

The I in ACID stands for Isolation, which is a property that determines whether or not a transaction can see the progress of other transactions. Transaction isolation is a complex system generally defined by the allowance of certain "read phenomena".

InnoDB employs MVCC (Multi-Version Concurrency Control) to implement isolation, and while its implementation has some really fun and potentially severe glitches we can discuss at a later time, it is lock-free and tends to work pretty well. In the simplest way, when a transaction starts, InnoDB issues it a 64-bit monotonic transaction ID (TRX_ID) that is used to "version" a record when it makes a change. The very same transaction ID is "noted" in other transactions to specify which record "versions" a transaction is allowed to see: everything younger, nothing older, and nothing else in progress (depending on the chosen isolation). This can start getting pretty costly when you have tens-of-thousands of transactions per second across possibly tens-of-thousands of connections, as each transaction has a full list of all other in-flight transactions it isn't allowed to see.

To reduce the rather substantial overhead of possibly writing gigabytes of transaction IDs per second in memory, InnoDB optimizes for transactions that are "read-only" (explicitly enabled prior to MySQL 5.7, and on-by-default in 5.7.7+). This optimization is simple: it does not issue a transaction ID since it knows no writes will happen. And, as such, gigabytes become kilobytes all because you told MySQL your transaction (or better yet, session) was read-only.