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, andinnoDBsolves 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 introducesgap lockandnext-key lockmechanisms 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 passfor 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 lockin 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 lockwill 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 > 4To lock under this condition, it needs to lock multiple index ranges, so in this case InnoDB introduces a mechanism callednext-key lock,next-key lockis 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, whilenext-key locklocks 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 Whennext-key lockis 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 asid > 4, InnoDB will add anext-key locklock. The lock range is (4, 7] (7, 10] (10, ♾️]. The difference betweenGap lockandnext-key lockis in the locking range.Gap locklocks the gap between two indexes, whilenext-key lockwill lock multiple index intervals, which includesrecord lockandgap 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 isnext-key lockIt 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!