How does innoDB solve phantom reading?

How does innoDB solve phantom reading?

1. Mysql transaction isolation level

These four isolation levels, when there are multiple transactions concurrent conflict, may appear dirty read, unrepeatable read, magic some problems, and innoDB in repeatable read isolation level mode to solve a magic problem.

2. What is hallucination

Phantom reading is when two queries of the same range in the same transaction yield inconsistent results

As shown in the figure, in the first transaction, we execute a range query, at this time only one data meets the condition, while in the second transaction, it inserts a row of data and commits, and then when the first transaction queries again, the result is one more data than in the first query. Note that the first and second queries of the first transaction are in the same transaction, so phantom readings can cause data consistency problems

3. How does InnoDB solve the phantom problem

InnoDB introduces gap locking and next-key lock mechanisms to solve phantom reading problems

Select * from B+Tree; select * from B+Tree; select * from B+Tree; select * from B+Tree

A record lock, or row lock, is created to lock the index with id=1

Locked record before the lock is released, this one record other transactions do any operation, we have the definition of a phantom reads, in front of the phantom read is to point to in the same transaction, the two queries the same range of inconsistent results, pay attention to is broken range queries, that is to solve the problems of the phantom read, must ensure that a point

If a transaction is locked by such a statement, another transaction executes

Such an INSERT statement needs to be blocked until the previously acquired transaction is released, so a gap lock is designed in innonDB. Its main function is to lock index records within a certain range

Select * from B+ where id > 4 and id < 7; select * from B+ where id > 4 and id < 7

InnoDB uses a next-key lock, which is equivalent to a gap lock and a record lock. The record lock locks the row where the record exists. A gap lock locks the gaps between record rows, while a next-key lock locks the sum of the two

A next-key lock exists for each non-unique index column. When a transaction holds a next-key lock for this row, it will lock a segment of data in the open and close ranges. Therefore, when a transaction locks a segment of data in the range id > 4, it will lock a segment of data in the open and close ranges. InnoDB will add a next-key lock on the range of (4,7] (7,10) (10, +♾️]. The difference between a Gap lock and a next-key lock is in the range of the lock. A Gap lock locks a Gap between two indexes. A next-key lock locks multiple index intervals, including Record locks and Gap locks. In this case, we use a neighboring key lock, or next-key lock which is the default row lock algorithm in Mysql

4. To summarize

Although InnoDB through the gap lock to solve the phantom problem, but after the lock must affect the concurrency performance, so for some business scenarios with high performance, we can set the isolation level cannot be repeated, so there is no gap lock at this level and there is no such a performance impact