Where does InnoDB store transaction data before committing it?

Question

I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology.

I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not yet committed (could perform an UPDATE-query).

Questions

• Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?
• Why isn't it possible for a READ_COMMITTED transaction to read uncommitted data, i.e. performing a "dirty read"? What mechanism enforces this restriction?

Answer 1

"Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?"

The new uncommitted record (clustered PK) versions are treated as the "current" version of the record on page. So they can be stored in the buffer pool and/or in the tablespace (e.g. tablename.ibd). Transactions that then need to build a snapshot/view in anything other than READ-UNCOMMITTED, need to construct a previous version of the row (following the history list) using the UNDO records (stored in the system tablespace). When reading the uncommitted record, InnoDB may also need to read some uncommitted secondary index records from the Change Buffer and apply them before presenting the record back to the user.

It's this behavior that can make rollbacks in InnoDB relatively expensive. It's the big factor that can also lead to potential performance issues from long running idle transactions that are holding updated records, as those transactions will block purge operations and the history list of old record versions grows, and the UNDO records needed to rebuild those old versions on-demand, will continue to grow. It slows down new transactions that need to read an older/committed version of the record, as they need to traverse a longer and longer history list--which is a singly linked list of UNDO records--and do more work in order to reconstruct the old version of the record. So you end up using a lot of CPU cycles (not to mention internal locking primitives: mutexes, rw_locks, semaphores, etc.) on non-user visible work that slows down query processing.

Hopefully that makes sense? :)

As an FYI, in MySQL 5.7 you can move the UNDO tablespace and logs out of the system tablespace, and have them automatically truncated. They can grow quite large if you have a long running transaction that prevents purge operations, resulting in a very long and ever growing history list length. Having them stored in the system tablespace was the single most common cause of a huge/growing ibdata1 file, which in turn cannot be truncated/shrunk/vacuumed in order to later reclaim that space.

Answer 2

You asked

where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?

In order to answer your question, you need to know what the InnoDB Architecture looks like.

The following picture was created years ago by Percona CTO Vadim Tkachenko

According to MySQL Documentation on The InnoDB Transaction Model and Locking

A COMMIT means that the changes made in the current transaction are made permanent and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

Since COMMIT and ROLLBACK govern data visibility, READ COMMITTED and READ UNCOMMITTED would have to rely on structures and mechanisms that record changes

1. Rollback Segments / Undo Space
2. Redo Logs
3. Gaps Locks Against the Table(s) Involved

Rollback Segments and Undo Space would know what changed data looked like before changes are applied. Redo Logs would know what changes are to be rolled forward to have data appear updated.

You also asked

why isn't it possible for a READ_COMMITTED transaction to read uncommitted data, i.e. performing a "dirty read"? What mechanism enforce this restriction?

Redo Logs, Undo Space and Locked rows are come into play. You must also considert he InnoDB Buffer Pool (where you can measure dirty pages with innodb_max_dirty_pages_pct, innodb_buffer_pool_pages_dirty and innodb_buffer_pool_bytes_dirty).

In light of this, READ COMMITTED would know what data appears like permanently. Therefore, there is no need to look for dirty pages that were not committed. READ COMMITED would be nothing more that a dirty read that has been committed. READ UNCOMMITTED would have continue to know what rows are to be locked and what redo logs has be read or ignored to make the data visible.

To fully understand the Locking of Rows to Manage Isolation, please read The InnoDB Transaction Model and Locking