Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to solve phantom reading in innoDB in Mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Transaction isolation level of 1.Mysql

For these four isolation levels, when there are concurrent conflicts of multiple transactions, there may be some problems of dirty reading, non-repeatable reading and phantom reading, while innoDB solves a problem of phantom reading in repeatable isolation level mode.

two。 What is fantasy reading?

Phantom reading means that in the same transaction, the results obtained are inconsistent when the two queries are in the same scope.

As shown in the figure, in the first transaction, we execute a scope query, and at this time there is only one piece of data that meets the conditions, while in the second transaction, it inserts a row of data and commits it, and then when the first transaction goes to query again, the result is one more piece of data than the result of the first query. Notice that the first and second queries of the first transaction are both in the same thing, so Phantom reading brings the problem of data consistency.

3. How to solve the problem of phantom reading by InnoDB

InnoDB introduces gap lock and next-key lock mechanism to solve the problem of phantom reading.

If there is such an index structure of B+Tree, this structure has four index elements, which are 1meme 4pd7 10 when we query a record through the primary key index and lock the record through for update.

At this time, a record lock, that is, a row lock, is generated to lock the index id=1.

The locked record cannot be operated on by other transactions before the lock is released. In the previous definition of phantom reading, phantom reading means that in the same transaction, the results obtained when the two queries are in the same range are inconsistent. Note that the range query is knocked out here, that is to say, in order to solve the problem of phantom reading, a point must be guaranteed.

That is, if one 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, whose main function is to lock index records in a certain range.

When the query range id > 4 and id

4 this condition is locked, so it needs to lock multiple index intervals, so in this case InnoDB introduces a mechanism called next-key lock, which is equivalent to a collection of gap locks and record locks. Record locks lock rows that have records, gap locks lock gaps between record rows, and next-key lock locks the sum of both.

For each non-unique index column of a data row, there will be a next-key lock. When a transaction holds the next-key lock of this row of data, it will lock a piece of data in the left open and right closed interval, so when locking through the range of id > 4, InnoDB will add a next-key lock lock, and the locking range is (4jue 7] (7jue 10] (10jue + ♾️). The difference between gap locks and next-key lock is in the range of locks. Gap locks lock the gap between two indexes, while next-key lock locks multiple index intervals, which includes record locks and gap locks. When we use range queries not only to hit Record records, but also to include Gap gaps, we use key locks, that is, next-key lock, which is the default row locking algorithm in Mysql.

This is the end of the content of "how to solve Phantom Reading in innoDB in Mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.