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

MySQL how to repeat the gap lock, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Gap Lock is a lock mechanism introduced by Innodb to solve the problem of phantom reading under repeatable read submission. (all the following cases do not specifically emphasize the use of repeatable read isolation level.) the problem of phantom reading exists because of new or update operations. At this time, if you do a range query (lock query), there will be inconsistencies, and there is no way to meet the requirements by using different row locks. It is necessary to lock the data within a certain range, and gap locking is to solve this kind of problem. At the repeatable readable isolation level, the database is implemented through a combination of row locks and gap locks (next-key lock).

Locking rules have the following characteristics, which we will explain one by one in later cases:

1. The basic unit of locking is (next-key lock), which is the principle of opening before and closing after.

two。 Objects accessed during insertion will increase the lock

3. Equivalent query on index-when a unique index is locked, next-key lock is upgraded to a row lock

4. Equivalent query on the index-when the last value of the traversal to the right does not meet the query requirements, the next-key lock is reduced to a gap lock

5. The range query on the unique index accesses the first value that does not meet the condition

Case data

In order to solve the problem of phantom reading, the above data is updated not only by adding row locks to the above five pieces of data, but also by adding 6 gap locks to the value range in the middle, (- ∞, 5] (5] (5) (10) (10) (15] (20) (20) (25) (where supernum is the largest value maintained by the database. In order to ensure that the gap lock is left open and right closed principle.)

Case 1: a simple case of gap lock

When there are transaction An and transaction B below, transaction A will add the interval lock to the database table (10mem15), and then the data with insert id = 12 will be locked because of the interval lock (10mem15) and cannot be executed.

Case 2: gap lock deadlock problem

Unlike the principle that write locks are mutually exclusive, gap locks are not mutually exclusive. If one transaction An acquires the gap lock between (510), another transaction B can also acquire the gap lock (510). At this point, deadlock problems may occur, as in the following case.

Transaction An acquires the gap lock between (5j10) and does not allow other DDL operations. Transaction B also acquires the gap lock before transaction commits and the gap lock is released, when the two transactions are deadlocked.

Case 3: equivalence query-unique index

1. The range of the lock is the range lock of (5pc10)

two。 Because the data is an equivalent query, and the last data id=10 in the table does not meet the query requirements of id= 7, the row-level lock of id=10 is reduced to a gap lock.

3. So id=8 will be locked in transaction B, but not in id=10.

Case 4: equivalence query-General Index

1. The range of locks is (0re5), (5pd10).

two。 Since c is a general index, according to principle 4, after searching to 5, it continues to traverse backwards until it is searched to 10, so the locking range is (5 ~ 10).

3. Because the query is an equivalent query, and the last value does not meet the query requirements, the gap lock is reduced to (5d10)

4. Because locking is locking the ordinary index c, and because the index is overwritten, the primary key is not locked, so transaction B executes normally

5. Transaction C executes blocking because of locking range (5p10)

6. It should be noted that lock in share mode does not lock the primary key index because it overrides the index. If you use the for update program, you will feel that the update operation will be performed later, so the primary key index will be locked together.

Case 5: scope query-unique Index

Next-key lock added range lock (5pc10)

According to principle 5, the range query of the unique index will go to the first value position that does not match, so it will be increased (10d15)

Because the equivalent query has id = 10, the gap lock is upgraded to a row lock according to principle 3, so the remaining lock [101.15]

Because the query is not an equivalent query, [10mem15] will not degenerate to [10mem15).

Therefore, transaction B (13513) blocking, transaction C blocking

Case 6: scope query-General Index

Next-key lock increased range lock (5, 10), (10, 15)

Because c is a non-unique index, it will not degenerate to 10.

Because the query is not an equivalent query, [10mem15] will not degenerate to [10mem15).

So transaction B and transaction C are all blocked.

Case 7: general index-equivalence problem

Add a row to the above data (30, 10, 10, 30), so that there are two records for clocks 10 that exist in the database.

Next-key lock increased range lock (5, 10), (10, 15)

Transaction B blocks and transaction C executes successfully because it is an equivalent query and degenerates to (5d10), (10d15).

The scope of the lock is shown below

Case 8: general index-equivalent Limit problem

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.