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
.
Best Answer
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.