About the Next-Key lock and innodbnext-key of InnoDB

About the Next-Key lock and innodbnext-key of InnoDB

Recently, I was preparing materials for new employee training. I was planning to introduce the concept. But since I have written the transaction chapter, I would like to introduce the lock and the lock, I can't help but want to introduce Next-Key Lock.

As you know, the standard transaction isolation levels include read uncommitted, read committed, repeated read, and SERIALIZABLE. InnoDB implements the repeated read level by default. This level can solve the non-consistent READ problem, but cannot solve the phantom READ problem. However, InnoDB uses the Next Key Lock algorithm, phantom read protection is implemented at this level.

As for what is phantom reading, this concept can be found in the official documentation, and I will not go into details here.

First, let's look at an example to create a table according to the following rules:

CREATE TABLE `test` (  `id` int(11) DEFAULT NULL,  KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test values (1), (3), (5), (8), (11);

Note that there is an index on the id here, because the algorithm always locks the index record.

Now, the index may be locked in the following range:

(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, + ∞)

In this case, enable two sessions in the order shown in the following table. Both sessions can be successfully started before Session B is executed in Step 6. The blocking is started in Step 6 and the execution is normal in Step 8. In this way, we can know that the SQL statement in Session A actually locks A range. In addition to locking the range (5, 8] Where 8 is located, it also locks the next range: (8, 11], therefore, inserting 12 is out of the lock range.

I have not figured out why inserting 5 will be blocked. If anyone knows, please leave a message. Thank you. I will also look for information and research.

Order	Session	Session B
1	Begin;
2	Select * from test where id = 8 for update;
3		Begin;
4		Insert into test select 1;
5		Insert into test select 4;
6		Insert into test select 5;
7		Insert into test select 9;
8		Insert into test select 12;

In the above case, the lock is secondary index and is not unique. What if it is a unique index?

If you change the id column to a primary key, in the table above, Session B conflicts with the primary key in step 4 and step 6, and other steps can be successfully performed, (5, 8], (8, 11] All values in these two intervals (primary keys do not conflict with each other) can be successfully inserted into the table. This is because the index is unique. InnoDB will downgrade the Lock to Record Lock and Lock only one Record. This can improve the concurrency.

With Next Key Lock, InnoDB can implement phantom READ protection at the repeatable read level.

 

Reference: http://www.cnblogs.com/zhoujinyi/p/3435982.html