Скачать или смотреть Sneaky MySQL Repeatable Read Explained

Mysql InnoDB prevents Phantom Reads from plain select queries, and discourages using locking selects under Read Repeated. If you chose to use locking selects, you will soon run into cases where snapshot isolation breaks. The explanation for this is even more technical, and even more confusing. I decided to move it to the description instead.

Most new devs assume MySQL's default isolation level (Repeatable Read) handles consistency for them. It doesn't. Because concurrent updates are hidden by snapshot isolation, applications can easily violate their own invariants. For example, in a booking or seat-selection system, two transactions can both see a valid state and still commit conflicting changes resulting in two users booking the same seat at the same time. That’s an example of data inconsistency.

Also, the terminology here is very confusing. A non-repeatable read is an ISO concurrency anomaly, while Repeatable Read is an isolation level. Repeatable Read bans non-repeatable reads (obviously!), but also bans most phantom reads. The authors couldn’t have have come up with more confusing terminology!