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

In this issue, the editor will bring you about the scope of NEXT-KEY LOCK in RR mode. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

We know that MYSQL NEXT-KEY LOCK is used to prevent illusions, and there is an opportunity to use your talents in RR mode.

It is actually a range before and after the current row lock +, but how big is this interval?

Is it a simple closed interval on an auxiliary index column?

Tests are all in RR mode. RC mode does not exist.

Set up a test table:

CREATE TABLE `test` (

`a`int (11) NOT NULL DEFAULT'0'

`b` int (11) DEFAULT NULL

PRIMARY KEY (`a`)

KEY `b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 inserts several rows of data mysql > insert into test values (10Jing 2)

Query OK, 1 row affected (0. 01 sec) mysql > insert into test values (15 sec 2)

Query OK, 1 row affected (0.02 sec) mysql > insert into test values (20jade 4)

Query OK, 1 row affected (0. 01 sec) mysql > insert into test values (25. 6)

Query OK, 1 row affected (0. 02 sec) mysql > insert into test values (99. 8)

Query OK, 1 row affected (0.00 sec) mysql > commit

Query OK, 0 rows affected (0.00 sec) mysql > select * from test

+-+ +

| | a | b | |

+-+ +

| | 10 | 2 |

| | 15 | 2 |

| | 20 | 4 |

| | 25 | 6 |

| | 99 | 8 |

+-+ +

5 rows in set (0.00 sec) session A:

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test where baked 4 for update

+-+ +

| | a | b | |

+-+ +

| | 20 | 4 |

+-+ +

1 row in set (0.00 sec)

Session B:

Mysql > select * from test where bread2 for update

+-+ +

| | a | b | |

+-+ +

| | 10 | 2 |

| | 15 | 2 |

+-+ +

2 rows in set (0.00 sec)

Mysql > select * from test where baked 6 for update

+-+ +

| | a | b | |

+-+ +

| | 25 | 6 |

+-+ +

There is no problem with 1 row in set (0.00 sec), so obviously these columns do not have an X lock. Is it possible to simply understand that the lock is

What if a range of 2-6 doesn't contain 2 and 6?

Look at the following statement:

Mysql > insert into test values (16Pol 2)

^ CCtrl-C-sending "KILL QUERY 3" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

Mysql > insert into test values (16pr. 6)

^ CCtrl-C-sending "KILL QUERY 3" to server...

Ctrl-C-- query aborted.

ERROR 1317 (70100): Query execution was interrupted

It's all locked.

But

Mysql > insert into test values (145.2)

Query OK, 1 row affected (0.21 sec)

Mysql > insert into test values (26Pol 6)

Query OK, 1 row affected (0.02 sec)

Is enforceable.

This also proves that the conclusion we just made is not correct. Let's analyze it.

| | 15 | 2 |

| | 20 | 4 |

| | 25 | 6 |

This is the original record, and we for update 4 in order to narrow it down.

In fact, INNODB defined the location of the lock to

B column 2 a (15 to positive infinity) b column 4 all b column 6 a (negative infinity to 25)

The full range between, which does not seem to be a continuous interval, but if you understand the Btree index

At the same time, when INNODB deals with the same value, it is arranged in ascending order of the primary key.

Interval, let's draw it, and suppose the leaf nodes are arranged as follows.

In fact, this way we can see such a range, if we insert

Values (16Pol 2) obviously in this range it should be inserted between 2 15 and 4 20, so lock

Values (16pr 6) is obviously in range, it should be inserted between 4 20 and 6 25, so lock

Values is obviously not in this range, he should insert between 2 10 and 2 15, so OK

Of course, it is also possible for values (26Pol 6) to be between 6 25 and 8 99.

It obviously won't work if you want to insert (3p3), because it must be in this range if it is sorted by key first.

Finally, we come to our conclusion:

B column 2 a (15 to positive infinity)

B column 4 all

B column 6 a (negative infinity to 25)

Insertion of such a range is not allowed, of course 2 15 6 25 itself is not included because it can be for update.

In fact, this is also done to minimize locking scope and increase concurrency, so the gap lock on the secondary index not only depends on

The secondary index column also depends on the value of the primary key column, but note that the lock is on the secondary index, not on the

On the primary key.

There is one more thing to remind:

If the lock is a boundary record as shown above

Baux2 for update

And

Bread8 for update

Then the scope of the lock will be larger.

Back2 for update locks column b negative infinity to column b column 4a (negative infinity to 20)

As shown in the figure:

Here, the virtual line infimum is written to represent negative infinity.

Back8 for update locks column b 6a (25 to positive infinity) to column b positive infinity

As shown in the figure:

Here, the virtual ranks of supremum represent positive infinity.

In fact, you can understand it by looking at the picture.

Finally, you need to verify:

Verification from two aspects

1. Analyze the linked list in the page of the secondary index. If the linked list in the secondary index page is sorted first by KEY and then sorted by PRIMARY KEY with the same KEY, then our argument is basically verified.

This can be made up later.

2, source code view, the source code is too large is the establishment of the B+ tree index data structure, search, insert, delete are very difficult to understand, if to the evidence we need is very difficult, try our best later.

The above is the scope of NEXT-KEY LOCK under the RR mode shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.