Wechat public number: The way to grow up

In the previous document we introduced the concept of gap locking from phantom locks. And introduced the lock rule optimization. Today we continue to expand on the technical points from the previous article.

concept

The principle of

  • Principle 1: The basic unit of locking is next-key lock. I hope you remember that the next-key lock is a front-open and back-closed interval.
  • Principle 2: Only objects accessed during lookup are locked.

To optimize the

  • Optimization 1: equivalent query on index, next-key lock degenerates to row lock when unique index is locked.
  • Optimization 2: the next-key lock degrades to a gap lock when the last value does not meet the equivalence condition.

BUG

  • A range query on a unique index accesses up to the first value that does not satisfy the condition.

case

CREATE TABLE `t` (
  `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 t values(0.0.0), (5.5.5),
(10.10.10), (15.15.15), (20.20.20), (25.25.25);
Copy the code

Equivalent Query gap lock (1)

The first case is the locking rule for gap locks of equivalent queries. As shown below, let’s analyze it

sessionA

  • SessionA Modifies d data whose ID is 7. So it’s going to lock the d row. But there is no 7. According to the locking rule, it is added at 5 and 10.
  • becauseid=7Is equivalent query, so according to principle 1, lock unitsnext-key lockBecome (5, 10].
  • According to optimization 2,next-key lockIt degenerates into a gap lock, so it eventually changes to (5,10).

sessionB

  • Insert an 8. This number is between 5 and 10 (and not equal to), so sessionB will be locked by sessionA

sessionC

  • Change the value where ID =10. SessionB failed. SessionA placed a gap lock between data 5 and data 10. So sessionC can execute successfully.

Non-unique Index Equivalent Lock (2)

About overwriting locks on indexes. As shown in the figure below, let’s analyze it

sessionA

  • C =5; c=5;
  • As per principle 1, we need to add a next-key lock to (0-5).
  • Because c is a normal index, not a unique index or primary key, it scans to the right, adding an index to every row that is scanned until the next destination stops, which is 10. Add next-key-lock according to locking rule (5,10).
  • According to optimization 2, equivalent judgment, traversal to the right, the last one does not meet the degradation to a gap lock. (5, 10)

sessionB

  • According to principle 2, only access is locked, in this case overwrite index. Access to the primary key index is not required. So there’s no lock on the primary key. So sessionB can execute successfully.

Overwrite indexes if you don’t know what to look for in SQL. Select ID from index tree.

Id =5; id=5; id=5; The changed data is changed D, and D is not added

sessionC

Just lock it. Because sessionA locks (5,10). You insert a line 7 that is bound to be locked.

Conclusion:

  1. Lock in share mode locks overwrite indexes, but not for update. When you execute for update, the system assumes that you are going to update the data next, so it incidentally locks the rows that meet the criteria on the primary key index.
  2. The lock is added to the index; It also teaches us that if you want to use lock in share mode to lock rows from being updated, you must bypass the optimization of overwriting the index and add columns that do not exist in the index to the query field.

Primary key range lock

This is the scope locking rule. Like >=5 <10 or something like that. As shown in the picture below, let’s analyze it together

select * from t where id=10 for update;
select * from t where id> =10 and id<11 for update;
Copy the code

Are these two SQL statements the same?

Semantically the same, but logically different locking rules.

sessionA

  • To find the data with id 10 first, next-key-lock to (5,10].
  • According to optimization 1, degenerate to row locking. So only the lock on the record with ID 10 will be added.
  • Range search continues to the right, finds 15 and then decides if it’s less than 11. Next-key-lock (10,15) was added.

So sessionA locks two rows with id 10 and next-key-lock (10,15).

sessionB

  • Insert 8 without conflicting sessionA locks.
  • SessionA’s next-key-lock (10,15) lock was triggered when 13 was inserted, so it was locked

sessionC

  • Update the data 15. In case 3 of the lock index tree mentioned above, there is an overwrite index lock only tree, here is different. So it’s all locked. SessionC also failed to execute and was locked.

Non-unique index range lock (4)

We continue our analysis as shown in the figure below

sessionA

  • Find c=10 and lock it. Because index C is an ordinary index and not the only one, rule optimization is not used. (5, 10]
  • I’m going to go to the right and find 15 and see if it’s less than 11. Not satisfied. Add next – the key – lock (10, 15]

sessionB

  • Insert record 8, sessionA locked.

sessionC

  • SessionA is a normal index, not a unique index, so it does not degenerate into a gap lock, so (10,15) is not (10,15). So the session is also locked

Unique index range lock bug (5)

Let’s make it clear that case 5 is completely copied from Dinky’s MySQL45 lecture. All my articles are handwritten by myself, learning way is dinky teacher. So before the troll spray, please read the article, I am afraid that the troll can not see, I give bold

We continue our analysis as shown in the figure below

sessionA

Session A is A range query. As per rule 1, only (10,15) next-key lock should be placed on the index id, and since id is the only key, the loop should stop when id=15.

But implementationally, InnoDB scans forward to the first behavior that does not meet the criteria, which is id=20. And since this is a range scan, the next-key lock on the index id (15,20) is also locked.

So you can see that session B is going to update the row where ID =20 is going to be locked. Similarly, session C will be locked if it inserts a row with ID =16.

In theory, I don’t need to lock the line id=20. Since id=15 is scanned, we can be sure that we don’t need to look for it later. But the implementation does it anyway, so I think it’s a bug.

I also discussed with experts in the community, and the bug was also mentioned in the official system, but it was not verified. So I’m just saying it’s a bug, and if you have any other ideas, you’re welcome to come up with them.

Examples of “equivalence” on non-unique indexes (6)

To illustrate the concept of gap. Insert a new record into table T.

mysql> insert into t values(30.10.30);
Copy the code

So this new row, c is equal to 10, so now I have two rows that are equal to 10. Since a non-unique index contains the value of the primary key, it is impossible to have two rows that are “identical.”

As you can see, there are two records with c=10, but their primary key ids are different (10 and 30 respectively), so there is a gap between the two records with C =10.

I’ve drawn the primary key ID on index C. To distinguish it from the open interval form of a gap lock, I use the form (c=10,id=30) to represent a row on the index. As shown in the figure below

sessionA

  • Where delete and select… For Update is similar. First access to the first record with c=10. Similarly, according to principle 1, we add (c=5,id=5) to (c=10,id=10) the next-key lock.
  • Search right until you hit the line (c=15,id=15) and the loop ends. According to optimization 2, this is an equivalent query. The right row is found that does not meet the condition, so it degenerates into a gap lock between (c=10,id=10) and (c=15,id=15).

The above five cases come up. You should be able to deduce the reason why sessionB and sessionC results. I’m not going to introduce you here. 12 is locked, 15 is the open interval is not contained.

Limit statement lock (7)

The delete example from Example 6 is shown below.

sessionA

  • Find the record with c = 10 and add next-key-lock to (5,10). The most important thing is limit 2 here, which changes the whole fate.
  • Because there are only two values of 10. And when you do, you know it, you don’t have to go back.

And here’s why I’m talking about changing destiny, if you continue to circle the judgment. It is possible to lock the value of the next iteration as well. So imagine if the next traversal was c=40, which happens to be a lot of c=40. That creates a large area of locking.

Kopp: So when you write a delete statement, make sure it’s a record. You can add limit 1. Performance savings here. This is a good habit.

sessionB

  • This verifies that the above locking was successful when the record 12 was inserted. It means it stopped without finding 15.

A deadlock example (8)

Next-key lock is actually the result of a gap lock and a row lock. As shown in the figure below

  • SQL > alter table c add next-key lock(5,10) and gap lock(10,15);

C =10; c=10; c=10; c=10; This is degraded so it’s 15 open.

  • Insert next-key lock(5,10) on index c;
  • Session A inserts (8,8,8), which is locked by session B’s gap lock. InnoDB rolls back session B due to deadlock.

Blocked in sessionB is explained here.

Session B’s “next-key lock(5,10)” operation is actually divided into two steps. First, a gap lock(5,10) is added, and the lock is successfully added. And then I add the row lock of c=10, and that’s when it gets locked. In other words, we can use next-key lock to analyze locking rules. But it should be known that the specific execution of the time, is to be divided into gap lock and line lock two sections to execute.

conclusion

Here we illustrate locking rules at repeatable isolation levels, lock rule optimization, and two-phase commit for next-key-lock.

I believe I have a new understanding of next-key-lock

But that’s not enough, so let’s go on.

Wechat public number: Huanshao growth of any do not understand the place to contact me, we exchange discussion