In this section we will see which reads are consistent within a transaction, and how InnoDB guarantees this consistency. The consistency of queries is determined by the transaction level, by using the
WITH CONSISTENT SNAPSHOT option for
START TRANSACTION, and the
LOCK IN SHARE MODE or
FOR UPDATE options for
SELECT. Augmenting the consistency of reads can be important to be sure that applications work properly, while relaxing it improves the concurrency.
A read is called non-repeatable if repeating the same query twice within the same transaction without modifying the data within the transaction returns different results. This happens because the current transaction is not fully isolated from changes requested ...