How does MySQL InnoDB implement Read Uncommitted isolation level

Question

Oracle doesn't allow dirty reads, so Read Uncommitted is not even allowed to be set from JDBC.

PostgreSQL also falls back to Read Committed, when choosing Read Uncommitted.

SQL Server defines a Read Uncommitted isolation level, because its concurrency control model is based on locking (unless switching to the two snapshot isolation levels), so it's probably the only database which can see some performance advantage from avoiding locking for reports that don't really need strict consistency.

InnoDB is also using MVCC but unlike Oracle and PostgreSQL, it allows dirty reads. Why is it so? Is there any performance advantage from going directly to the latest version, instead of rebuilding the previous version from the rollback segments? Is the rollback segment query-time restoring such an intensive process that would call for allowing dirty reads?

Answer 1

The main advantage I'm aware of, is that if all your sessions are READ-UNCOMMITTED then house-keeping (cleaning up UNDO) will never be blocked waiting for old sessions.

There may be some other performance gains if read-view structures (example) do not need to be created for READ-UNCOMMITTED transactions themselves, but I have not confirmed this myself. Generally speaking, this is not an isolation level that the InnoDB team targets optimizations for.

Edit: In terms of performance from unrolling rollback segments, yes it is possible it can be slow with many revisions. AFAIK it is a simple link list, and many traversals could be required. The comparison to PostgreSQL is a difficult one to make here, because the architecture (mysql features UNDO) is quite different. Generally speaking I would say that UNDO works well when the relocation is "logical only + fits in working set"; i.e. it is performed in memory, but cleaned up before physical IO was required.