Lock algorithm in Innodb
There are generally three locking algorithms commonly used in innodb, namely
1. Record lock
2. Gap Lock
3. next key lock
The Next key lock is the combination of Gap lock and Record lock. It locks a range and locks the Record itself.
In other words, the next key lock is not a separate lock. As far as I understand, it is actually a concept, which is a combination of the concepts of the above two locks. Record lock is well understood. It is the lock of a record. Today we mainly talk about gap lock.
Introduction to clearance lock
Gap lock is to lock the gap between index records, or lock the gap between the first and last records. Organize other transactions to insert values into this gap. The gap may span a single index value, multiple index values, or it may be empty.
There will be gap lock only at the repeatable reading level!!!!
There will be gap lock only at the repeatable reading level!!!!
There will be gap lock only at the repeatable reading level!!!!
It is also important that there is no conflict relationship between gap locking. If a transaction locks a gap, other transactions can also lock the gap. These operations do not conflict. He exists only to prevent other transactions from inserting records in this gap.
Example of clearance lock
In order to realize gap lock, we can use the following example to check. First, we create a table containing two fields of id and age, create a general index on age, and the creation statement is as follows. Then we insert some records:
mysql--dba_admin@127.0.0.1:yeyztest 10:12:12>>show create table child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 10:12:21>>truncate table child; Query OK, 0 rows affected (0.01 sec) mysql--dba_admin@127.0.0.1:yeyztest 10:13:31>>insert into child values (10,10); Query OK, 1 row affected (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 10:13:38>>insert into child values (11,11); Query OK, 1 row affected (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 10:13:42>>insert into child values (13,13); Query OK, 1 row affected (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 10:13:48>>insert into child values (20,20); Query OK, 1 row affected (0.00 sec)
Create the table child and insert the four records 10, 11, 13 and 20. Then we start to test the gap locking as follows:
We can find that records with an insertion age equal to 9 or 21 can be inserted, but other records cannot be inserted. The reason is that the original records in our table are 10, 11, 13 and 20, so the gap of this table is divided into:
(negative infinity，10]
(10,11],
(11,13],
(13,20],
(20,positive infinity)
It should be noted that negative infinity and positive infinity are the maximum and minimum records. If you are not clear about these two records, it doesn't matter. You can check our article on April 9, which talks about the minimum and maximum records in the innodb data page structure.
Because we have locked the record with id=13 on session 1, we can't access the records between 11 ~ 13 and 13 ~ 20 on session B, that is, the gap lock is (11,20), and both records 10 and 11 exist. Therefore, the largest record that can be inserted in front of the gap is 9, and the smallest record that can be inserted behind the gap is 21
Now we know that locks occur during the whole insertion process. We use show engine innodb status to view the lock information, as follows:
------------ TRANSACTIONS ------------ Trx id counter 1329883 Purge done for trx's n:o < 1329883 undo n:o < 0 state: running but idle History list length 2305 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1329878, ACTIVE 19 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 3938833, OS thread handle 140584442394368, query id 105210346 127.0.0.1 dba_admin update INSERT INTO child (id,age) VALUES (12,12) ------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 168 page no 4 n bits 72 index idx_age of table `yeyztest`.`child` trx id 1329878 lock_mode X locks gap before rec insert intention waiting ------------------ ---TRANSACTION 1329859, ACTIVE 52 sec 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 3938791, OS thread handle 140584421222144, query id 105210793 127.0.0.1 dba_admin starting show engine innodb status
You can see that the red part indicates the existence of gap lock information, and gives the id value of trx. We can also use the information mentioned earlier_ InnoDB in schema_ trx and InnoDB_ Use the locks table to view the relevant lock information, which will not be repeated here.
Deadlock caused by gap lock
Because there is no impact between gap locks and they can exist at the same time, there is a possibility of deadlock. Let's look at the following example. First, through the above operation, the data in the table now becomes:
mysql:yeyztest 11:29:19>>select * from child; +----+-----+ | id | age | +----+-----+ | 9 | 9 | | 10 | 10 | | 11 | 11 | | 13 | 13 | | 20 | 20 | | 21 | 21 | +----+-----+ 6 rows in set (0.00 sec)
Then we do the following:
It can be seen that when we lock the value of age=15, the interval (13,20) is locked because the value does not exist, and the interval is also locked on session B. since there is no conflict in the gap lock, the statement of Session B is executed successfully. In other words, both sessions have locked this interval. At this time, we insert the record with age=15 on session A, and we can find that it is locked, but there is no operation result for a long time. When we insert the record with age=15 on Session B, we will directly prompt deadlock found. At this time, the sentence on session A is executed successfully, that is to say, the insert operations on the two sessions become deadlock with each other, innodb is optimized at this time, so it can output the final result.
To sum up
1. The gap lock locks a gap range before and after an index record.
2. Gap locks do not affect each other. You can add gap locks again in the locked section.
3. Clearance lock may cause deadlock
4. Clearance locks are unique at the RR isolation level
5. Gap locks only affect general indexes. The situation is slightly different for unique indexes and primary keys. We will focus on analysis in the next article.
Reprint: Research on innodb lock mechanism (II) -- gap lock (1)