Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

Question

The MySQL doc for START TRANSACTION says:

The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

I'm surprised, as I thought that the SERIALIZABLE isolation level, being above REPEATABLE READ, would also offer a consistent read.

What prevents WITH CONSISTENT SNAPSHOT from working with SERIALIZABLE transactions?

I even found this bug report from 2012 where the doc originally said that this worked with both REPEATABLE READ and SERIALIZABLE, and they explicitly changed it to REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with SERIALIZABLE.

Answer 1

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it.

consistent read A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time.

With SERIALIZABLE isolation level you don't need to use a snapshot because all necessary data is already locked by your transaction.

SERIALIZABLE isolation level should be more ACID compliant than REPEATABLE READ because the second one allows non-locking reads which may lead to a violation of the I (Isolation) rule of ACID.

Answer 2

This is mainly a conjecture on my part, but the technical reason stems from when a SERIALIZABLE transaction goes into effect.

According to Paragraph 4 of the MySQL 5.7 Documentation on Transaction Isolation Levels

You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important.

...

SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.

How can you make SERIALIZABLE enforce consistency rules stricter than REPEATABLE READ ? Note the definition of SERIALIZABLE from the same Documentation Page:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

So, you have to experiment with autocommit to see the differences.

With autocommit disabled used in SERIALIZABLE, SELECT ... LOCK IN SHARE MODE is summoned to bring about some readable locking but the locks would appear preemptively in a REPEATABLE READ at certain stages before a read takes place.

With autocommit enabled, SERIALIZABLE can be viewed as being like REPEATABLE READ because nonlocking reads can run wild and the data being read would be essentially be the same.

When you look at transactions from this perspective, SERIALIZABLE can never be trusted to be consistent because of the stagnating shared locks. This really disqualifies it from being consistent. That is why REPEATABLE READ is the only isolation level used for ACID compliance.

Therefore, any snapshot made SERIALIZABLE could look different at different points in time as opposed to REPEATABLE READ .