gap lock/next-key lock浅析 Basic-Paxos协议日志同步应用

当InnoDB在判断行锁是否冲突的时候, 除了最基本的IS/IX/S/X锁的冲突判断意外, InnoDB还将锁细分为如下几种子类型:

• record lock (RK)
  记录锁, 仅仅锁住索引记录的一行
• gap lock (GK)
  区间锁, 仅仅锁住一个区间(开区间)
• insert intention lock (IK)
  意向插入锁
• next key lock (NK)
  record lock + gap lock, 半开半闭区间, 且下界开, 上界闭

以下锁兼容矩阵:

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   |

---

下面构造集中场景简单描述下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
• 构造数据
  insert into reno select 5, 'aa';insert into reno select 7, 'bb'; insert into reno select 9, 'cc'; insert into reno select 18, 'dd'; insert into reno select 23, 'ee'; insert into reno select 30, 'ff'; insert into reno select 40, 'gg'; insert into reno select 45, 'hh'; insert into reno select 99, 'ii';
• 查看结果
  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)
• 查看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只有在repeatable-read级别下才有意义, 防止出现幻读

• 设置tx_isolation级别为REPEATABLE-READ级别:
  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 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 9 for update;   start transaction;  insert into reno select 8,'jj';ok insert into reno select 10,'kk';ok insert into reno select 3,'ll';ok insert into reno select 111,'mm';ok rollback rollback  

• 加record lock, id = 9

• case 2:

sesion 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 15 for update;   start transaction;  insert into reno select 8, 'jj';ok insert into reno select 10, 'kk';block insert into reno select 16, 'll';block insert into reno select 19, 'mm';ok rollback rollback  

• 加next-key lock, (9, 18]
• innodb lock info:
  ------------TRANSACTIONS ------------ Trx id counter 2990040255 Purge done for trx's n:o < 2990040253 undo n:o < 0 state: running but idle History list length 323 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 38753, OS thread handle 0x7f377c68f700, query id 140937 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 9, OS thread handle 0x7f370817d700, query id 140906 127.0.0.1 root cleaning up ---TRANSACTION 2990040254, ACTIVE 8 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 38773, OS thread handle 0x7f377c60e700, query id 140924 localhost root executing insert into reno select 10, 'kk' ------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64257 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno` trx id 2990040254 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000012; asc ;; 1: len 6; hex 0000b238648b; asc 8d ;; 2: len 7; hex d70001c00c0110; asc ;; 3: len 2; hex 6464; asc dd;; ------------------ ---TRANSACTION 2990040253, ACTIVE 17 sec 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
  从上面的trx lock信息里看到此时等待的锁是: lock_mode X locks gap before rec

• case 3:

sesion 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 200 for update;   start transaction;  insert into reno select 1, 'jj';ok insert into reno select 88, 'kk';ok insert into reno select 100, 'll';block insert into reno select 500, 'mm';block rollback rollback  

• 加next-key lock, (99, ~)
• innodb lock info:
  ------------TRANSACTIONS ------------ Trx id counter 2990040257 Purge done for trx's n:o < 2990040253 undo n:o < 0 state: running but idle History list length 323 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 38753, OS thread handle 0x7f377c68f700, query id 141561 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 9, OS thread handle 0x7f370817d700, query id 141535 127.0.0.1 root cleaning up ---TRANSACTION 2990040256, ACTIVE 137 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 38773, OS thread handle 0x7f377c60e700, query id 141548 localhost root executing insert into reno select 500, 'kk' ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64257 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno` trx id 2990040256 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION 2990040255, ACTIVE 153 sec 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
  从上面的trx lock信息里看到此时等待的锁是: lock_mode X insert intention waiting

简单总结下:

• 在case 1中, 实际上加的next-key lock是(9,9], 也就是id=9这一条记录被lock住, 其他所有的插入都没有关系.
• 在case 2中, 因为id=15记录不存在, 且记录中上下两个边界是id=9, id=18, 因此加的next-key lock是(9, 18], 在这个区间内插入的数据都会被block, 此区间外的数据写入则不受影响.
• 在case 3中, id=200的记录不存在, 并且比表中所有的记录都大, 因此innodb则认为next-key lock是(99, ~), 任何大于99的id记录插入都会被block, 小于99的id记录写入则不受影响.

next-key lock是为防止幻读的发生,而只有REPEATABLE-READ以及以上隔离级别才能防止幻读, 所以在READ-COMMITTED隔离级别下面没有next-key lock这一说法.

Love truth but pardon errors

当InnoDB在判断行锁是否冲突的时候, 除了最基本的IS/IX/S/X锁的冲突判断意外, InnoDB还将锁细分为如下几种子类型:

• record lock (RK)
  记录锁, 仅仅锁住索引记录的一行
• gap lock (GK)
  区间锁, 仅仅锁住一个区间(开区间)
• insert intention lock (IK)
  意向插入锁
• next key lock (NK)
  record lock + gap lock, 半开半闭区间, 且下界开, 上界闭

以下锁兼容矩阵:

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   |

---

下面构造集中场景简单描述下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
• 构造数据
  insert into reno select 5, 'aa';insert into reno select 7, 'bb'; insert into reno select 9, 'cc'; insert into reno select 18, 'dd'; insert into reno select 23, 'ee'; insert into reno select 30, 'ff'; insert into reno select 40, 'gg'; insert into reno select 45, 'hh'; insert into reno select 99, 'ii';
• 查看结果
  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)
• 查看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只有在repeatable-read级别下才有意义, 防止出现幻读

• 设置tx_isolation级别为REPEATABLE-READ级别:
  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 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 9 for update;   start transaction;  insert into reno select 8,'jj';ok insert into reno select 10,'kk';ok insert into reno select 3,'ll';ok insert into reno select 111,'mm';ok rollback rollback  

• 加record lock, id = 9

• case 2:

sesion 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 15 for update;   start transaction;  insert into reno select 8, 'jj';ok insert into reno select 10, 'kk';block insert into reno select 16, 'll';block insert into reno select 19, 'mm';ok rollback rollback  

• 加next-key lock, (9, 18]
• innodb lock info:
  ------------TRANSACTIONS ------------ Trx id counter 2990040255 Purge done for trx's n:o < 2990040253 undo n:o < 0 state: running but idle History list length 323 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 38753, OS thread handle 0x7f377c68f700, query id 140937 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 9, OS thread handle 0x7f370817d700, query id 140906 127.0.0.1 root cleaning up ---TRANSACTION 2990040254, ACTIVE 8 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 38773, OS thread handle 0x7f377c60e700, query id 140924 localhost root executing insert into reno select 10, 'kk' ------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64257 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno` trx id 2990040254 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000012; asc ;; 1: len 6; hex 0000b238648b; asc 8d ;; 2: len 7; hex d70001c00c0110; asc ;; 3: len 2; hex 6464; asc dd;; ------------------ ---TRANSACTION 2990040253, ACTIVE 17 sec 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
  从上面的trx lock信息里看到此时等待的锁是: lock_mode X locks gap before rec

• case 3:

sesion 1sesion 2sesion 2 insert statusstart transaction;  select * from reno where id = 200 for update;   start transaction;  insert into reno select 1, 'jj';ok insert into reno select 88, 'kk';ok insert into reno select 100, 'll';block insert into reno select 500, 'mm';block rollback rollback  

• 加next-key lock, (99, ~)
• innodb lock info:
  ------------TRANSACTIONS ------------ Trx id counter 2990040257 Purge done for trx's n:o < 2990040253 undo n:o < 0 state: running but idle History list length 323 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 38753, OS thread handle 0x7f377c68f700, query id 141561 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 9, OS thread handle 0x7f370817d700, query id 141535 127.0.0.1 root cleaning up ---TRANSACTION 2990040256, ACTIVE 137 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 38773, OS thread handle 0x7f377c60e700, query id 141548 localhost root executing insert into reno select 500, 'kk' ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 64257 page no 3 n bits 80 index `PRIMARY` of table `test`.`reno` trx id 2990040256 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION 2990040255, ACTIVE 153 sec 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
  从上面的trx lock信息里看到此时等待的锁是: lock_mode X insert intention waiting

简单总结下:

• 在case 1中, 实际上加的next-key lock是(9,9], 也就是id=9这一条记录被lock住, 其他所有的插入都没有关系.
• 在case 2中, 因为id=15记录不存在, 且记录中上下两个边界是id=9, id=18, 因此加的next-key lock是(9, 18], 在这个区间内插入的数据都会被block, 此区间外的数据写入则不受影响.
• 在case 3中, id=200的记录不存在, 并且比表中所有的记录都大, 因此innodb则认为next-key lock是(99, ~), 任何大于99的id记录插入都会被block, 小于99的id记录写入则不受影响.

next-key lock是为防止幻读的发生,而只有REPEATABLE-READ以及以上隔离级别才能防止幻读, 所以在READ-COMMITTED隔离级别下面没有next-key lock这一说法.