How to solve phantom reading in innoDB in Mysql

1.Mysql transaction isolation level

#These four isolation levels, when there are multiple concurrent transaction conflicts, dirty reads and non-repeatable reads may occur , some problems of phantom reading, and innoDB solves a problem of phantom reading in the repeatable read isolation level mode,

2. What is phantom reading

phantom Reading means that in the same transaction, the results obtained when querying the same range twice are inconsistent.

As shown in the figure, in the first transaction, we execute a range query , at this time there is only one piece of data that meets the conditions, and in the second transaction, it inserts a row of data and submits it. Then when the first transaction queries again, the results obtained are more than the results of the first query. For a piece of data, please note that the first and second queries of the first transaction are both in the same thing. Therefore, phantom reading will bring data consistency issues

3. How does InnoDB solve phantom reading? The problem

InnoDB introduces gap lock and next-key lock mechanisms to solve the phantom read problem

Suppose there is such a B Tree index structure. This structure has 4 index elements, namely 1, 4, 7, 10. When we query a record through the primary key index, and pass for update## to this record #When locking

At this time, a record lock will be generated, which is a row lock, locking

id=1this index

Before the lock is released, other transactions cannot perform any operations on the locked record. As we have mentioned above, phantom reading refers to phantom reading in the same transaction. , the results obtained when querying the same range twice before and after are inconsistent. Note that the range query is knocked out here, which means that to solve the problem of phantom reading, one point must be guaranteed

That is, if a transaction is locked through such a statement, another transaction is executed

Such an insert statement needs to be blocked until the previously obtained transaction is Release, so design a

gap lock in innonDB. Its main function is to lock index records within a certain range

When querying the range

id > 4 and id When this range is locked, a gap lock will be added to the open interval range of (4, 7) in the B number, which means that in this In this case, other transactions that insert, update, or delete the data in this range will be locked, but there is another situation, such as this

This query statement is for

id > 4 To lock under this condition, it needs to lock multiple index ranges, so in this case InnoDB introduces a mechanism called next-key lock, next-key lock is equivalent to the combination of gap lock and record lock. Record lock locks the row where the record exists. Gap lock locks the gap between the record rows, while next-key lock locks the two rows. The sum

# of each data row’s non-unique index column will have a

next-key lock. When a transaction holds this When next-key lock is used for a row of data, a section of data in the left open and right closed range will be locked. Therefore, when locking through a range such as id > 4, InnoDB will add a next-key lock lock. The lock range is (4, 7] (7, 10] (10, ♾️]. The difference between Gap lock and next-key lock is in the locking range. Gap lock locks the gap between two indexes, while next-key lock will lock multiple index intervals, which includes record lock and gap lockWhen we use range query, it not only hits the Record record, but also includes the Gap gap When, in this case, the next-key lock is used, which is next-key lock It is the default row lock algorithm in Mysql

The above is the detailed content of How to solve phantom reading in innoDB in Mysql. For more information, please follow other related articles on the PHP Chinese website!