Reads consistency

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.

The non-repeatable reads

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 by other connections.

Of course, this improves the overall performance in an environment where concurrency exists. But the application developers should be aware that this can happen, and if this represents a problem, it must be avoided.

The mechanisms...