奋斗日报

MySQL——后码锁（Next-Key Block）

2023-08-30 21:10 由 啊哈哈哈哈312 发表于 #数据库

众所周知，Mysql的事务隔离级别分为4个，分别是READ-UNCOMMITED，READ-COMMITED，REPEATABLE-READ，SERIALIZABLE，在常规数据库概论中，前三种事务隔离级别会带来脏读、不可重复读、幻读的问题，对应关系如下：

脏读 不可重复读 幻读
READ-UNCOMMITED √ √ √
READ-COMMITED × √ √
REPEATABLE-READ × × √
SERIALIZABLE × × ×

但是在Mysql中使用了Next-key Block解决了幻读问题,下面我们通过讨论该问题来详细讨论Next-key Block，这里考虑一个常见的幻读情况，首先创建示例表：

create database test;
use test;
CREATE TABLE `t` (
  `t1` int(11) NOT NULL,
  `t2` int(11) DEFAULT NULL,
  PRIMARY KEY (`t1`),
  KEY `t2` (`t2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

将其中加入几条示例数据：

insert into t values(1,0),(2,10),(3,20),(4,30),(5,40);

接下来考虑一个常见的幻读情况，我们可以先将mysql的Next-key Block关闭，可以采用如下两种方式对其进行关闭：

  1. 将事务隔离级别设置为READ-COMMITTED
  2. 将参数innodb_locks_unsafe_for_binlog设置为1，注意这里设置为1是关闭Next-key Block

由于innodb_locks_unsafe_for_binlog参数需要重启服务器才能进行配置，因此我们采用第一种方式，将session的事务隔离级别设置为READ-COMMITTED。下面考察一般的幻读情况，我们的实验方式如下：

事务1 事务2
begin;
select * from t where t2=20;（查到一条记录，（3，20））
begin;
insert into t value(6,20);
commit;
select * from t where t2=20;（查到两条记录（3，20），（6，20））
commit;

事务1实验过程如下：

mysql> set session transaction isolation level read committed; # 设置当前session的事务隔离级别为READ-COMMITED
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit = 0; # 取消自动Commit
Query OK, 0 rows affected (0.00 sec)

mysql> begin; # 开始一个新事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where t2=20;  # 首次查询t2为20的数据，查询点1
+----+------+
| t1 | t2   |
+----+------+
|  3 |   20 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from t where t2=20; # 事务2未提交时查询t2为20的数据，查询点2
+----+------+
| t1 | t2   |
+----+------+
|  3 |   20 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from t where t2=20; # 事务2提交后查询t2为20的数据，查询点3（出现幻读）
+----+------+
| t1 | t2   |
+----+------+
|  3 |   20 |
|  6 |   20 |
+----+------+
2 rows in set (0.00 sec)

mysql> commit; # 提交事务1
Query OK, 0 rows affected (0.00 sec)

事务2执行过程如下：

mysql> set session transaction isolation level read committed; # 设置当前session的事务隔离级别为READ-COMMITED
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit = 0; # 取消自动Commit
Query OK, 0 rows affected (0.00 sec)

mysql> begin; # 开始一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t value(6,20); # 调用点1、调用点2之间进行插入新数据  这里同时也是为了营造t2列的索引是非唯一索引的情况，否则会简化为Record Lock，为下一步的讨论做准备
Query OK, 1 row affected (0.00 sec)

mysql> commit; # 调用点2、调用点3之间进行提交
Query OK, 0 rows affected (0.00 sec)

可以看到，这种情况下幻读正常发生。

接下来，考察使用Next-key Block防止出现幻读的情况时，会发生的情况。这里我们再次强调一下我对幻读的理解，考虑当前有事务A、B，事务A中具有两条一模一样的查询语句执行（例如上述例子的调用点1和3，注意，我们不考虑调用点2），在两条查询语句执行的中间，事务B提交了会影响到事务A两条查询语句结果的插入请求（事务2的插入语句），这时，事务A的查询语句的执行结果会和第一条的查询结果不同，就好似出现了幻觉。那么接下来真正开始讨论Next-key Block。

Next key Block

讨论Next-key Block之前，我们需要对一些基本概念进行解释，Mysql的锁算法有3种：

  1. 记录锁（Record Lock），该锁锁的是一条索引记录（注意是索引记录）
  2. 间隙锁（GAP Lock），该锁锁的是一个范围，但是该范围是(X,Y)类型的，注意是两边都是开区间
  3. Next-key Block，有人将其称之为后码锁，不过我还是感觉英文名更贴近其意思，他将记录锁和间隙锁组合应用，幻读就是通过它解决的。

介绍完基础概念之后我们继续开始探究，基本的查询语句显而易见有3种，大于、小于、等于、不等于，这里我们主要讨论这四种情况，接下来对其进行一一讨论，不过首先要都把事务隔离级别设置为REPEATABLE-READ。

1. 大于的情况

考虑查询语句更改为如下语句：

select * from t where t2>20 for update;

在这种情况下，我们猜想应该给大于20的t2列的索引全部加锁，而对于插入的方面又可以分为3类：

  1. 插入b列小于20的数据

    insert into t value(7,19);

    胡乱猜想也可以知道，这种情况并不会导致插入语句锁住的情况，因为上述的锁并没有涉及到t2列为19的情况，事实证明也是如此。

    这里给出实验结果

    事务1 事务2
    begin;
    select * from t where t2>20 for update;（查到两条记录，（4，30），（5，40））
    begin;
    insert into t value(7,19);
    commit;
    select * from t where t2=20 for update;（查到两条记录，（4，30），（5，40））
    commit;

    为了下面的实验，我们将数据库还原，即删除t1=7的数据。

  2. 插入b列等于20的数据

    insert into t value(7,20);

    首先，我们猜想，如此情况插入数据不会被事务1中的查询语句锁住，因为没有涉及到会更改查询结果的部分，接下来进行实验；

    事务1 事务2
    begin;
    select * from t where t2>20;（查到两条记录，（4，30），（5，40））
    begin;
    insert into t value(7,20); # 阻塞了

    这时我们考虑是哪个锁阻塞掉了该插入操作，查询information_schema.innodb_locks表。结果如下：

    lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
    '1371:23:4:5' '1371' 'X,GAP' 'RECORD' 'test.t' 't2' '23' '4' '5' '30, 4'
    '1370:23:4:5' '1370' 'X' 'RECORD' 'test.t' 't2' '23' '4' '5' '30, 4'

    其中第一行是事务2导致的，第二行是事务1导致的。可以看到事务1的查询语句还对t2为30的索引列加了写锁。而事务2请求的也是t2为30的写锁，我明明插入的是20为什么是请求t2为30的写锁呢？

    根据我们的猜想，我们了解对于t2>20的索引列都被加上了锁，那么为什么插入的是20，却锁的是30呢？考虑之前的数据，我们发现30是20后面的一个索引值。这里我们先给标记起来（mark 1）。

    这里我们直接rollback就好了，还是恢复数据库。

  3. 插入b列大于20的数据

    insert into t value(7,20);

    该情况与第二种插入等于20的数据加锁一致，此处不再赘述。

2.小于的情况

考虑查询语句更改为如下语句：

select * from t where t2<20 for update;

  1. 插入b列大于20的数据

    insert into t value(7,21);

    这种情况其实和1.1情况类似，我们猜想插入数据与查询数据无关，必定不会锁住，实际上也是这样。

  2. 插入b列等于20的数据

    insert into t value(7,20);

    这里我们猜想，应该也和1.2情况类似，会直接锁住，但是实际上你错了，这里直接插入成功了，查看实验结果：

    事务1 事务2
    begin;
    select * from t where t2<20 for update;
    begin;
    insert into t value(7,20);# 注意没有阻塞
    commit;
    select * from t where t2<20 for update;
    commit;

    这是为什么呢？明明上一个加锁了啊，为什么这个没有加锁，直接就添加上了，我们考察上一个加的锁是大于20的间隙锁，我们插入20时，锁住的是t2为30的索引，而30正是20的下一个索引，这是否意味着：

    索引的下一个值其实是用来锁住上一个值到下一个值的区间的。简单来讲就是t2=30这个索引的锁会锁住[20,30)这个范围。

    这里我们继续考察，恢复数据库。

  3. 插入b列小于20的数据

    insert into t value(7,19);

    这种情况下执行结果与1.3的情况类似，插入操作也被阻塞了，这里列出加锁情况。

    lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
    '1373:23:4:4' '1373' 'X，GAP' 'RECORD' 'test.t' 't2' '23' '4' '4' '20， 3'
    '1372:23:4:4' '1372' 'X' 'RECORD' 'test.t' 't2' '23' '4' '4' '20， 3'

    这里刚刚符合我们说的索引的下一个值其实是用来锁住上一个值到下一个值的区间的。结论，这里应该锁住的就是[10,20)的区间，所以该区间内的插入都不会成功。那么此时我如果把他变为插入(7,9)这条数据呢？我猜想会锁住10,2吧，这里试验一下。

    lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
    '1373:23:4:3' '1373' 'X，GAP' 'RECORD' 'test.t' 't2' '23' '4' '4' '10， 2'
    '1372:23:4:3' '1372' 'X' 'RECORD' 'test.t' 't2' '23' '4' '4' '10， 2'

    事实证明这里我蒙对了。

3.等于的情况

考虑查询语句更改为如下语句：

select * from t where t2=20 for update;

  1. 插入小于20的数据

    这里需要考虑多种情况，例如插入(10,20)范围内的数据和插入 (0,10)范围的数据，即(查询条件中出现的索引之前的一个索引,查询条件中出现的索引)和(查询条件中出现的索引之前的第二个索引,查询条件中出现的第一个索引)。

    其中第二种情况是与查询条件中出现的索引相邻的索引值，第二种情况代表与查询条件中出现的索引不相邻的索引值，这里我们分别考察：

    1. 考虑第一种情况

      这种情况下新插入的数据需要在[10,20)之间，这里我们尝试插入(7,19)、(8,10)两条数据。

      实验结果均如下所示：

      事务1 事务2
      begin;
      select * from t where t2=20 for update;
      begin;
      插入语句 # 阻塞

      这里我们猜想，是因为select语句锁住了t2=20的索引，导致无法添加上述两条记录。

      我们考察一下此时的事务加锁情况：

      lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
      '4887:36:4:4' '4887' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '4' '20， 3'
      '4886:36:4:4' '4886' 'X' 'RECORD' 'test.t' 't2' '36' '4' '4' '20， 3'

      其中4887是事务2，4886是事务1。可以看到这里对索引t2=20的记录加了X锁，而插入语句请求的是X锁和间隙锁。

      还原数据库，继续进行实验。

    2. 考虑第二种情况

      这种情况下我们考虑插入(0,10)范围内的数据，这里我们尝试插入(8,9)这一条数据，成功插入了没有被阻塞。

      可以发现t2=10的索引并没有被锁住。

  2. 插入等于的数据

    这里必定是会被阻塞的，毕竟我们的查询操作都给t2=20加入了写锁，关键是到底是如何加锁的。

    现在进行试验考察实验过程中的加锁信息：

    lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
    '4887:36:4:5' '4887' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '5' '30， 4'
    '4886:36:4:5' '4886' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '5' '30， 4'

    注意这里锁住的索引并不是我们想的t2=20，而是t2=30的索引。而且这里有个细节，3.1中的事务1中的select语句给t2=20加的锁仅仅是一个X锁，而这里给t2=30不仅仅加了写锁，而且加了间隙锁。

  3. 插入大于20的数据

    这里同样要考虑两种情况，第一种是插入(20,30)范围内的数据，第二种是插入(30,40)范围内的数据。

    1. 第一种情况

      这里我们选择插入(8,21)，(9,30)两条数据，发现在插入第一条数据时进行了阻塞，插入第二条时没有阻塞。查看插入第一条数据时的加锁信息：

      lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
      '4887:36:4:5' '4887' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '5' '30， 4'
      '4886:36:4:5' '4886' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '5' '30， 4'

      可以看到此处事务1的select语句加的是X锁、间隙锁。事务2的insert语句加的也是X锁、间隙锁。

    2. 第二种情况

      这里我们选择插入(10,31)数据，可以发现是正常插入，这里证明没有对t2=40加锁。

小结

上面我们仅仅讨论了大于、小于、等于的查询情况下进行了一系列实验，现在我们对上述实验结果进行总结。

可以看到在进行类似于>A的查询同时，另一条事务插入<A的数据都不会加锁，但是插入>=A的数据时都会加锁，而且加锁类型也相同。

在进行>A的讨论中，事务1在进行select查询时，锁住了(A,+无穷)中的所有的索引，注意 这里锁住的是索引，即记录锁，不是间隙锁。结合上面讨论的例子，也就是进行>20的讨论时对30,40，无穷大进行了加锁，由于使用的是select ... for update因此加的是X锁，当进行插入数据的时候，例如插入t2=20的数据时，查找下一个索引即t2=30的索引，发现其被锁住了，因此无法插入。插入>20的数据时同理。

在进行<A的查询同时，另一条数据插入<A的数据会加锁，但是在插入>=A的数据时都不会加锁。

在进行<A的讨论中，事务1在进行select查询时，锁住了[最小的索引，A)范围中的所有索引，等价于(-无穷,A)范围内的所有索引，注意这里也是记录锁，对于试验中我们的<20的条件，锁住的是0,10两个索引，具体可以在<20的试验中进行插入(8,-1)，可以发现锁住的是0,1。正因如此，在我们插入(7,20)、(7,21)时可以正常插入，因为t2=30没有被锁住，而插入(7,19)时被阻塞了，因为t2=20被锁住了，所以无法正常插入。

最后在进行=A的查询同时，另一条数据插入<(A前一个索引)和>=(A下一个索引)的数据时能正常插入，但是在插入该范围以内的数据时都会被阻塞。

在进行=A的讨论中，事务1在进行select查询时，对A索引加锁，同时给(A,A下一个索引)这部分加了一个间隙锁。对A加锁是select语句显示要求的，而间隙锁是因为无法让你插入=A的数据，但是不能对A的下一个索引加锁，因为=(A的下一个索引)的数据应该正常插入。结合上述的讨论，也就是在=20的讨论中，select语句给20，(20,30)加了锁，t2=20的索引导致无法插入[10,20)的数据，而(20,30)间隙锁导致了无法插入(20,30)范围内的数据。这样也就能解释为何明明查询条件是等于，却要锁住一个范围了。

对于之前总结的索引的下一个值其实是用来锁住上一个值到下一个值的区间的。也因此是错误的，应该是存在列t，是非唯一辅助索引，其有索引值A，我们将A的下一个索引值命名为B，如果在t列的B索引值上存在记录锁，或者(A,B)区间存在间隙锁，那么将无法插入[A,B)区间内的数据。例如，存在列t，A为20，B为30，那么如果有t上有(20,30)间隙锁或者t上有30的记录锁，无法插入t属于[20,30)的数据。

验证

这里我们使用尚未讨论的不等于查询进行验证。查询sql如下：

select * from t where t2 != 20 for update;

这里会对t2列不为20的所有索引加锁即（-无穷，20），（20，+无穷）区间内所有的索引均加锁。注意这里加锁加的也是记录锁。考虑讨论=A的情况，间隙锁的作用在此处只是禁止=A的数据插入罢了，这里其实并无这种情况，因此，这里使用的是记录锁。

这里我们插入>20、<20、=20的数据发现其加锁状态有两种情况，插入负无穷到最大索引范围内的数据，即（-无穷，40）,加锁情况类似于下表：

lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
'4897:36:4:1' '4897' 'X，GAP' 'RECORD' 'test.t' 't2' '36' '4' '1' '20， 3'
'4896:36:4:1' '4896' 'X' 'RECORD' 'test.t' 't2' '36' '4' '1' '20， 3'

大于最大索引的数据，加锁情况则会改变：

lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
'4897:36:4:1' '4897' 'X' 'RECORD' 'test.t' 't2' '36' '4' '1' 'supremum pseudo-record'
'4896:36:4:1' '4896' 'X' 'RECORD' 'test.t' 't2' '36' '4' '1' 'supremum pseudo-record'

至于为何会这样，就不得而知了，不过Next-key block的基本情况已经得到了论证。

总结

Next-key block的名字给人以太多误解，让人总以为是加锁只会在(X,Y]范围内加锁，但是实际上其实是使用Next-key进行判断是否应该锁住。

热门相关：我的治愈系游戏   未来兽世：买来的媳妇，不生崽   异世修真邪君   不科学御兽   不科学御兽