Gap Lock/next-key Lock Analysis Basic-paxos protocol log Synchronization application

When InnoDB determines whether a row lock is in conflict, in addition to the most basic is/ix/s/x lock collision judgment, InnoDB also subdivides the lock into the following seed types:

• Record Lock (RK)

  Record locks, lock only one row of index records

• Gap Lock (GK)

  Interval lock, lock only one interval (open interval)

• Insert intention Lock (IK)

  Intent Insert Lock

• Next key lock (NK)

  Record lock + gap lock, half open half-closed interval, and lower bound, upper bounds closed

The following lock-compatible matrices:

request与granted之间的兼容矩阵:         | Type of active  | Request |  lock (granted) |  lock   | RK   GK  IK  NK |---------+-----------------+  RK     |  0   1 1 0 | GK | 1 1 1 1 | IK | 1 0 1 0 | NK | 0 1 1 0 |

The following tectonic set scenario simply describes the next record Lock/gap Lock/next-key lock

• Table schema

  CREATE TABLE `reno` (    `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

• Construct data

  InsertInto RenoSelect5,' AA ';InsertInto RenoSelect7,' BB ';InsertInto RenoSelect9,' CC ';InsertInto RenoSelect18,' DD ';Insertinto Reno select 23,  ' EE '; insert into Reno select 30,  ' FF '; into Reno select 40,  ' GG '; insert into Reno select 45,  ' hh '; into Reno select 99,  ' II ';              

• View Results

  select * from reno;+----+------+| id | name |+----+------+|  5 | aa   ||  7 | bb   ||  9 | cc   || 18 | dd   || 23 | ee || 30 | ff || 40 | gg || 45 | hh || 99 | ii |+----+------+9 rows in set (0.00 sec)

• View Tx_isolation

  SELECT @@GLOBAL.tx_isolation, @@tx_isolation;+-----------------------+----------------+| @@GLOBAL.tx_isolation | @@tx_isolation |+-----------------------+----------------+| READ-COMMITTED        | READ-COMMITTED |+-----------------------+----------------+1 row in set (0.00 sec)

  Next-key lock only makes sense at the repeatable-read level, preventing phantom reads from appearing

  set the tx_isolation level to the Repeatable-read level :

  SET @@GLOBAL.tx_isolation = ‘REPEATABLE-READ‘;Query OK, 0 rows affected (0.00 sec)SET @@SESSION.tx_isolation = ‘REPEATABLE-READ‘;Query OK, 0 rows affected (0.00 sec)SELECT @@GLOBAL.tx_isolation, @@tx_isolation;+-----------------------+-----------------+| @@GLOBAL.tx_isolation | @@tx_isolation |+-----------------------+-----------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+-----------------+1 row in set (0.00 sec)

• Case 1:

  sesion 1	sesion 2	sesion 2 Insert Status
  Start transaction;
  SELECT * FROM reno where id = 9 for update;
  	Start transaction;
  	INSERT INTO Reno Select 8, ' JJ ';	Ok
  	Insert into Reno Select Ten, ' KK ';	Ok
  	INSERT into Reno Select 3, ' ll ';	Ok
  	INSERT INTO Reno Select 111, ' mm ';	Ok
  	Rollback
  Rollback

  • Plus record lock, id = 9

• Case 2:

  sesion 1	sesion 2	sesion 2 Insert Status
  Start transaction;
  SELECT * FROM reno where id = n for update;
  	Start transaction;
  	INSERT INTO Reno Select 8, ' JJ ';	Ok
  	Insert into Reno Select Ten, ' KK ';	Block
  	Insert into Reno Select, ' ll ';	Block
  	Insert into Reno Select, ' mm ';	Ok
  	Rollback
  Rollback

  • Plus Next-key Lock, (9, 18]

  • InnoDB Lock Info:

    ------------Transactions------------Trx ID Counter2990040255Purge done for Trx ' s N:o <2990040253 Undo N:o <0 state:running But idleHistory list Length323LISTOfTransactionsForeachSESSION:---TRANSACTION 0, not startedMySQL thread ID38753,OS thread Handle0x7f377c68f700, Query ID140937 localhost root initShow Engine InnoDB Status---TRANSACTION 0, not startedMySQL thread ID9,OS thread Handle0x7f370817d700, Query ID140906127.0.0.1 Root Cleaning up---TRANSACTION 2990040254, ACTIVE 8 sec insertingMySQL TablesIn use1, Locked1LOCKWAIT2 lock struct (s), Heap size360,1 row lock (s)MySQL thread ID38773,OS thread Handle0x7f377c60e700, Query ID140924 localhost root executingINSERT INTO Reno SelectTen, ' KK '-------TRX have BEEN waiting 8 SEC for this LOCK to be granted:RECORDLOCKS Space ID64257 Page No3 N BitsIndex 'PRIMARY 'of table ' Test '. ' Reno ' Trx ID2990040254 Lock_modeX Locks Gap beforeRec Insert Intention waitingRecord Lock, Heap No5PhysicalRecord:n_fields4; Compact format; Info bits00:len4; Hex80000012; ASC; 1:len 6; hex 0000B238648B; ASC 8d;; 2:len 7; hex d70001c00c0110; ASC; 3:len 2; hex 6464; ASC DD;; ---------------------TRANSACTION 2990040253, ACTIVE sec< Span class= "Hljs-number" >2 lock struct (s), Heap size 360, 1 row Lock (s) mysql thread ID 38758, os thread Handle 0x7f370807b700, Query ID 140919 localhost root cleaning up< /span>                

    From the TRX Lock information above, you can see that the waiting lock is: Lock_mode X locks Gap before Rec

• Case 3:

  sesion 1	sesion 2	sesion 2 Insert Status
  Start transaction;
  SELECT * FROM Reno where id = $ for update;
  	Start transaction;
  	INSERT into Reno Select 1, ' JJ ';	Ok
  	Insert into Reno Select, ' KK ';	Ok
  	Insert into Reno Select, ' ll ';	Block
  	Insert into Reno Select, ' mm ';	Block
  	Rollback
  Rollback

  • Plus Next-key Lock, (99, ~)

  • InnoDB Lock Info:

    ------------Transactions------------Trx ID Counter2990040257Purge done for Trx ' s N:o <2990040253 Undo N:o <0 state:running But idleHistory list Length323LISTOfTransactionsForeachSESSION:---TRANSACTION 0, not startedMySQL thread ID38753,OS thread Handle0x7f377c68f700, Query ID141561 localhost root initShow Engine InnoDB Status---TRANSACTION 0, not startedMySQL thread ID9,OS thread Handle0x7f370817d700, Query ID141535127.0.0.1 Root Cleaning up---TRANSACTION 2990040256, ACTIVE 137 sec insertingMySQL TablesIn use1, Locked1LOCKWAIT2 lock struct (s), Heap size360,2 row lock (s)MySQL thread ID38773,OS thread Handle0x7f377c60e700, Query ID141548 localhost root executingINSERT INTO Reno SelectK, ' KK '-------TRX have BEEN waiting SEC for this LOCK to be granted:RECORDLOCKS Space ID64257 Page No3 N BitsIndex 'PRIMARY 'of table ' Test '. ' Reno ' Trx ID2990040256 Lock_modeX Insert Intention waitingrecord lock, Heap no 1 physical record:n_fields 1; compact format; info bits 00:len 8; hex 73757072656D756D; ASC supremum ;; ---------------------TRANSACTION 2990040255, ACTIVE 153 sec< Span class= "Hljs-number" >2 lock struct (s), Heap size 360, 1 row Lock (s) mysql thread ID 38758, os thread Handle 0x7f370807b700, Query ID 141432 localhost root cleaning up< /span>                

    From the TRX Lock message above, you see that the lock waiting is: Lock_mode X insert intention waiting

Briefly summarize below:

• In case 1, the actual addition of Next-key lock is (9,9], that is, id=9 this record is locked, all other insertions are not related.
• In case 2, since the id=15 record does not exist, and the top and bottom two boundaries of the record are id=9, id=18, the added Next-key lock is (9, 18], the data inserted within this interval will be block, the data written outside this interval is not affected.
• In case 3, the id=200 record does not exist and is larger than all the records in the table, so innodb that Next-key Lock is (99, ~), any ID record that is greater than 99 is inserted into the block, and the ID record of less than 99 is not affected.

Next-key Lock is designed to prevent Phantom reads, and only Repeatable-read and the above isolation levels prevent phantom reads, so there is no next-key lock under the Read-committed isolation level.

Reference:

• https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
• Http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

Gap Lock/next-key Lock Analysis Basic-paxos protocol log Synchronization application