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

This article mainly explains "how to solve the problem of innodb phantom reading". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn how to solve the problem of innodb fantasy reading.

1. Illusory reading of CREATE TABLE 'test_ 20` (`id` int (11) NOT NULL, `c` int (11) DEFAULT NULL, `d` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ENGINE=InnoDB;insert into test_20 values (0Ling 0), (5 from t where 5 5), (10 10), (15 15), (20 20), (25 25); begin;select * from t where dudes 5 for update;commit

This statement hits this line of dashed 5, corresponding to the primary key id=5, so after the execution of the select statement, the line id=5 adds a write lock, and because of the two-phase locking protocol, this write lock is released when the commit statement is executed.

What happens if only the id=5 line is locked and the other lines are not locked?

Executable at the READ COMMITTED level

At the REPEATABLE READ level, session B will block

1) Q1 only returns the line id=5.

2) at T2, session B changed the d value of id=0 to 5, so Q2 detected id=0 and id=5 at T3.

3) at T4, session C inserts another row (1Jing 1jue 5), so Q3 finds out the three lines of id=0, id=1 and id=5 at T5.

Among them, the phenomenon that Q3 reads the line of id=1 is called "phantom reading". In other words, phantom reading means that when a transaction queries the same range twice, the latter query sees rows that the previous query did not see.

Description:

1) under the repeatable readable isolation level, ordinary queries are snapshot reads and do not see data inserted by other transactions. Therefore, illusory reading only appears under "current reading".

2) the modification result of session B above is seen by the select statement after session A with "current read", which cannot be called phantom reading. Phantom reading only refers to "newly inserted rows".

two。 Problems of Phantom Reading (updates and additions) 2.1 semantically

Session A declared at T1, "I want to lock all the lines of dumb5, and no other transactions are allowed to read or write." In fact, this semantics is broken.

2.2 data consistency issues

Locks are designed to ensure data consistency. This consistency is not only the consistency of the internal data state of the database at this moment, but also includes the logical consistency of data and logs.

Wait for the contents of the binlog:

1) at T2, the session B transaction commits and two statements are written

2) at T4, the session C transaction commits and two statements are written

3) at T6, the session A transaction commits, and the statement update t set dumb100 where dail5 is written.

Put it together as follows:

Update t set dagger 5 where id=0; / * (0 where 5) * / update t set censor 5 where id=0; / * (0 meme 5) * / insert into t values (1 update t set 5) / * (1 update t set 5 where id=1; / * (1 min 5) * / update t set dagger 100 where dagger 5 * all dagger 5 lines, d changed to 100 minutes /

This sequence of statements, whether taken to the standby library for execution, or later cloned a library with binlog, the result of these three lines is (0meme5100), (1meme5100) and (5meme5100).

The reason is:

This is caused by the assumption that the statement "select * from t where dumped 5 for update only locks the line of dumb5, that is, this line of id=5."

Further assume:

Add a write lock to all the lines encountered during the scan, and then take a look at the execution effect.

Execution sequence in binlog:

Insert into t values (1 set 5); / * (1 set 5) * / update t set censor 5 where id=1; / * (1 minus 5) * / update t set dudes 100 where dudes 5 where id=0; * all dice 5 rows, d changed to 100*/update t dagger 5 where id=0; / * (0memo 5) * / update t set cymes 5 where id=0; / * (0min 5) * /

The problem of session B is solved here, but the new problem of session C is not solved!

At T3, when we lock all rows, the id=1 line does not exist and cannot be locked if it does not exist. In other words, even if all records are locked, newly inserted records will not be prevented.

How to solve the problem of Phantom Reading by 3.InnoDB

The reason for the illusion is that row locks can only lock rows, but the action of inserting new records updates the "gap" between records. Therefore, in order to solve the problem of phantom reading, InnoDB has to introduce a new lock, namely gap lock (GapLock).

Gap lock, lock is the gap between two values. Table test_20, for example, initializes the insertion of six records, which creates seven gaps.

When you execute select * from t where dail5 for update, you not only add row locks to six records already in the database, but also add seven gap locks. This ensures that no new records can be inserted.

A data row is an entity that can be locked, and the gap between data rows is also an entity that can be locked. But the gap lock is quite different from the locks we've encountered before.

The conflict with the row lock is "another row lock".

The conflict with the gap lock is the operation of "insert a record into the gap". There is no conflict between gap locks.

As follows: session B will not be blocked here. Because there is no record of session 7 in table test_20, session An adds a gap lock (5pm 10). And session B also adds a gap lock in this gap. They have a common goal, that is, to protect this gap and not to insert values. However, there is no conflict between them.

The gap lock and the row lock are called next-key lock, and each next-key lock is a front open and back closed interval. That is to say, after the table test_20 is initialized, if you use select * from t for update to lock up all the records of the entire table, you will form seven next-key lock, namely (- ∞, 0], (0score 5], (5 next-key lock 10], (10) 15], (15) 20], (20, 25), and (25).

Record the gap lock as the open interval and the next-keylock as the front open interval and the closed interval.

3.1 problems brought about

1) session An executes select... For update statement, because the line id=9 does not exist, a gap lock will be added (5d10)

2) session B executes select... The for update statement will also add a gap lock (5d10), and there will be no conflict between the gap locks, so this statement can be executed successfully

3) session B tried to insert a row (9 # 9) and was blocked by the gap lock of session A, so he had to enter and wait

4) session A tried to insert a row (9 Magi 9), which was blocked by the gap lock of session B.

At this point, the two session wait for each other, forming a deadlock. The introduction of gap locks may cause the same statements to lock a larger range, which actually affects the degree of concurrency.

Solution:

The gap lock takes effect only at the repeatable readable isolation level. So, if you set the isolation level to read commit, there is no gap lock. But at the same time, to resolve possible data and log inconsistencies, you need to set the binlog format to row. This is also the configuration combination used by many companies now.

At this point, I believe you have a deeper understanding of "how to solve the problem of innodb phantom reading". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.