21 mysql的加锁机制（未完待续）

mysql的加锁机制（本文全程使用的mysql版本：Server version: 8.0.20 MySQL Community Server - GPL）

1. mysql的加锁机制(大佬总结)：

  1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

  2. 原则 2：查找过程中访问到的对象才会加锁。

  3. 优化 1：索引上的等值查询（注意要好好理解这条，请看等值更新间隙锁），给唯一索引加锁的时候，next-key lock 退化为行锁。

  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

  6. 锁是加在索引上的，如果有多个索引，锁住a索引不会影响b索引，回表的情况我们要仔细思考；

分三个步骤：

  1. 根据行为推测mysql加锁机制；
  2. 利用performance_schema.data_locks验证推测；
  3. 实践检验；

2.例子：

建表语句：

CREATE TABLE `t` (
    `id` int(11) NOT NULL, 
    `c` int(11) DEFAULT NULL,  
    `d` int(11) DEFAULT NULL,  
    'e' int unique key default null,
    PRIMARY KEY (`id`),  
    KEY `c` (`c`)) ENGINE=InnoDB;

数据插入语句：

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

2.1 唯一索引等值操作：

2.1.1 存在的数据唯一索引等值操作：

实验开始之前，说说自己的理解：唯一等值操作，因为数据存在，应该都是加上行锁的效果；

实现效果与预测一致，这里就不贴过程了；这里需要强调的是，有人认为锁是加载索引上的，所以：

事务A：

begin：
select * from t where e=5 for update;

事务B：

select * from t where id=5 for update;

有人认为事务B不会阻塞，虽然他们是同一行数据；这个观点是错误的，如果这个结论成立，那岂不是数据安全性都无法得到保障了？实践结果也表明，该观点是错误的，事务B是会被阻塞的；也许gap lock会有这样的情况，后面实践来检验；

2.1.2 对不存在的数据进行唯一索引等值操作锁的情况，有坑，与理论不太一致（mysql8.0）：

基础例子：

事务A：

begin；
update t set d=d+1 where id=7;（id=7的数据不存在）

事务B：

//下面的语句将会被锁住；实际上id为6-9的插入操作都会被锁住；
insert into t values(8,8,8);
对例子进行修改：

如果将事务B改成如下语句：

// delete不会被阻塞；
delete from t where id=7;

// 下面的语句不会被锁住；实际上id不为7的更新操作都不会被锁住；
update t set d=d+1 where id=8;

//select不会被锁住；
select * from t where id=7 for update;
select * from t where id=7 lock in share mode;

mysql行为分析：

update一条数据，使用的是唯一索引，且该数据不存在时：

  1. 对其他insert语句是间隙锁，例子锁住了(5，10)；
  2. 对于select和delete是无锁；
  3. 对于update是行锁；（这里再次实践时已经变成了无锁状态，应该是第一次实践触发了什么东西，导致了结果不一样，下面对应的分析及结论请略过）

行为猜测：

个人认为对于不同的操作，其表现都不一样，应该不是BUG，而是设计人员有意为之；下面是我的推测：

首先id=7这条数据不存在，那么锁还是不锁？显然mysql设计人员选择了锁，因为在同一个事务中第一次update影响了0条数据，因为别的事物中途insert了id=7的数据，而导致第二次update影响了1条数据，有点类似幻读，第一次更新没数据，第二次更新数据又出现了；那么该行数据不存在，所以不能加行锁；只能加间隙锁了，（5，10],然后优化一下，7！= 10，变成（5，10）；

其次，开发人员显然也意识到了，一条不存在的数据，别的事务对其delete select都是没有结果的，也不会影响本事务中的操作，不应让其阻塞；显然是做了优化，失去了gap lock语义；

（这条分析已经被推翻，update与update之间也是无锁状态）最后，对于update操作，其实这个比较难以理解，既然是没有数据，insert又被锁住了，别的事务的update有何影响，为何要加上行锁？我相信delete select能实现不锁，update也能实现，那这样的结果是技术决定还是实现瑕疵呢？

如果上述分析没有问题，确实是技术决定，那么：

  1. select加锁/delete 唯一索引操作不存在的数据时，也将对insert产生间隙锁效果；对update产生行锁效果或者无锁效果，个人认为是无锁效果，但实在没有想通update对其他update为什么产生行锁效果之前，不敢肯定；

  2. insert因为插入了数据，即便之前不存在该数据，所以insert应该对其他操作都是行锁；

经过实验证明：

  1. select加锁/delete 唯一索引操作不存在的数据时，对insert产生间隙锁效果,对update产生无锁效果，毕竟数据都不存在也不可能插入，完全没必要对update进行阻塞；
  2. insert对其他操作都是行锁效果，注意在这一段落里，我们都是在讨论使用唯一索引且数据不存在的情况下；
对不存在的数据进行唯一索引等值操作总结：

因为实践结果变化下面的结论作废：

  1. update、当前读、delete都将对insert产生间隙锁效果；
  2. insert因为插入了数据，对其他操作都是行锁效果；
  3. update与update操作会产生行锁效果；（其他都合理，就这里个人认为不合理！）
  4. update、select、delete之间互相是无锁效果；
  5. select与select、delete与delect之间都是无锁效果；

新结论如下：

  1. update、当前读、delete都会对insert产生间隙所效果；
  2. insert则对其他操作产生行锁效果；
  3. update、select、delete之间（包括自身与自身）之间都是无锁效果；

2.2 唯一索引范围操作：

事务A：

begin；
select * from t where e >=5 and e <= 10 for update;

事务B：

// 以下语句会阻塞：
select * from t where e=5 for update;
select * from t where e=10 for update；

insert into t values(1,1,1,1);
insert into t values(14,14,14,14);

update t set d=d+1 where e=5;
update t set d=d+1 where id=10;(该记录e也等于10)

delete from t where e=5；
delete from t where e=10；
//以下语句不会阻塞；
select * from t where e=6 for update;

insert into t values(-1,-1,-1,-1);
insert into t values(14,14,14,16);

update t set d=d+1 where e=9;

delete from t where e=9；

结果分析：

  1. 唯一索引范围操作时select对select、update、delete只会产生行锁效果；行锁的加锁范围即为访问到的数据，这里是5和10；

  2. 对于insert操作，其会产生行锁+gap lock，并且会向前、向后查找到第一个不符合要求的数据，并加上查找区间的间隙锁（不会锁上不满足要求的那一行）；如这里：

    1. 首先锁上[5,10],这个是没有争议的，因为是要访问的数据；
    2. 然后mysql不会停止，继续往左找，找到1，对(1,5)加上间隙锁；
    3. 向右找找到15，对（10，15）加上间隙锁；

原因总结：

对于非insert操作其实行锁就够了，这个很好理解；

对于insert操作：

其实这个如果过滤条件是e > 3 and e < 12就很好理解了，因为mysql找到了5满足要求，加锁结束的话，其他事务插入4怎么办？所以得锁到（3，12），猜测技术实现原因，3和12不存在，不是很好实现，那么就退而求其次，不能缩小范围，那就扩大范围吧，扩大到3左边和12右边的第一条数据，即1和15，然后加上锁(1,15);所以对insert的限制是（1，15）；至此，我开始对文章开头列出来的原则和优化 有些嗤之以鼻了，那仿佛就是为了应对 如何确定 间隙锁 范围 强行总结出来的条条框框，了解了这个思路，我根本没有利用那些原则和优化规则来判断间隙锁的范围，结果不是显而易见的吗？当然，是否鄙视这些规则还得等到做完 非 唯一索引的实践后才能下结论；

但是：过滤条件是e >= 5 and e <=10 或者是 e > 4 and e < 11，加锁的范围还是(1,15)就无法理解了，因为加上[5,10]就够了，5，10两条记录是存在的，两个行锁+(5,10)的Gap lock 完全够用，实现不存在问题，所以我认为mysql在这一块没有特殊优化，还是选择了通用实现，偷懒了？

后来补充：即便后来意识到没有行锁和next key lock混用（暂且认为没有混用，mysql不是偷懒），那只能解释（1，5]被锁住了，因为5的next key lock是(1,5],但还是无法解释为何锁住了（10，15），mysql还是偷懒了；

将事物A改为：

begin;
select * from t where e > 5 and e < 10 for update;

那么事务B：

// 以下语句会阻塞；
insert into t values(9,9,9,9);
insert into t values(6,6,6,6);

select * from t where e=10 for update;(其他select不会阻塞)
delete from t where e=10;（其他delete不会阻塞）
update t set d=d+1 where e=10;（其他update不会阻塞）

// 以下语句不会阻塞：
insert into t values(4,4,4,4);
insert into t values(11,11,11,11);

间隙锁对select、delete、update不生效,但是10被加上了行锁；

insert有了上面的分析 这个例子就好理解了，首先锁上(5,10),左边找到不满足要求的5，右边找到不满足要求的10，上锁（5，10），但实际上10貌似也被锁住了；

内心已经开始相信大佬总结的第一点：加锁的单位是Next Key Lock了；这样就解释了为什么select、delete、update会在10上面阻塞了，首先要加锁的范围是(5,10),需要对6，7，8，9行（即便不存在）加上next key Lock，就是（5，10]，10被加上锁就可以解释得通了；实现上也好理解，mysql设计时偷懒了或者确实遇到技术难题了，没有根据具体情况选择锁，而是要么就用行锁（读已提交），要么就用next key lock（可重复读）；

2.2 非唯一索引等值操作：

2.2.21 非唯一索引等值操作：

表数据如下；

id主键
c索引
e唯一索引；

id  c   d   e
0   0   0   0
2   2   2   2
5   5   5   5
10  10  10  10
15  15  15  15
20  20  20  20

事务A：

begin;
select * from t where c=5 for update;

事务B：有了上面的推论，可以推测如下：

因为c=5的记录存在，加上next key lock，（2，5],间隙锁对select\delete\update不生效，那么仅操作5时被阻塞；

insert操作在（2，5]范围内被阻塞；

实践证明：推测基本正确；

但不得忽视一个问题：
// 字段顺序是id c d e;
当我操作insert into t values(5,5,5,5);的时候被阻塞；
当我操作insert into t values(4,5,5,5);的时候提示e的值是5，与数据冲突了；
当我操作insert into t values(4,5,5,4);的时候，阻塞了；

可以看出数据库在操作数据时，是一列一列检查的，第一次插入5，5，5，5的时候，id=5导致阻塞了；第二次修改了id值，id导致的阻塞没有了，e列提示唯一冲突，第三次4，5，5，4继续被阻塞，这次阻塞肯定是列C的锁没错了；

先不去纠结id、e、c的检查顺序，id=5不是报id冲突，而是阻塞，证明id=5 是被加锁了；修改为4后，对e进行了检测，证明id=4没有被加锁；为了证明id=4确实没有加锁，再执行下面的操作：

insert into t values(4,-1,5,4);执行成功；

看起来对C索引加锁，也会同时对主键进行加锁，其实很好理解，如果仅仅锁住索引C，那么利用id对数据进行修改能正常进行的话，那么C的索引也就没有什么意义了；至此，我对锁的概念渐渐清晰了；加锁不仅仅加在当前索引上，同样加在满足要求的数据的聚集索引上，当别的事物通过其他索引加锁数据时也需要对聚集索引进行加锁，这样才能达到互斥的目的，否则c索引锁c的，e索引锁e的，共享的数据还是得不到互斥；只有通过聚集索引这个统一的入口才能实现互斥；至于聚集索引的加锁范围，目前只知道过滤条件范围内的数据的主键肯定是会被锁住的；继续往下，重点实践看看聚集索引如何加锁；

问题是如果需要对聚集索引加锁，那么是否需要回表呢？ 我认为不需要，因为索引本身就存放了聚集索引的值，可以通过HashMap这样的数据结构存放那些聚集索引（key）被加锁了，是哪个索引加锁导致的聚集索引加锁（value);

2.2.2 非唯一索引范围操作：

表数据如下；

id主键
c索引
e唯一索引；

id  c   d   e
0   0   0   0
2   2   2   2
5   5   5   5
10  10  10  10
15  15  15  15
20  20  20  20

事务A：

begin;
select * from t where c >= 5 and c <= 10 for update;

有了上面的理解或者说推论，我可以猜测事务B：

//推测如下：
begin;
// 下面语句会被阻塞；
// 过滤条件为c=5和c=10的这行数据的select、update、delete语句会被阻塞，如：
select * from t where c = 5 for update;
select * from t where c = 10 for update;
delete from t where c=5;
......

// 会被阻塞的insert操作区间(2,15],因为5左边为2，10左边是15；
// 按照之前的推论，mysql会对需要对 3～14 加上next key lock；

实践表明，我的猜测是正确的；具体过程就不贴上来了，占用篇幅，也没有太大意义；但有一个现象是值得一提的：

// 字段顺序是id c d e;
当我执行insert into t values(3,2,3,3);时候，被阻塞了，我分别给id、c、d、e赋值了3、2、3、3，我认为c给2值应该可以顺利插入，但是阻塞了；

当我执行insert into t values(-1,2,-1,-1);时候，成功了，证明上一句被阻塞，问题出在id、e这两列中，因为d没有索引；

当我执行insert into t values(1,2,3,3);时候，成功了，证明了猜测是对的；


可当我执行insert into t values(101,2,100,100);时，阻塞了；what？这是什么玩意儿？

当我执行insert into t values(101,1,100,100);时，成功了；

难道c=2被锁住了？不可能，前面都正常插入了，赶紧查询一下：

    SELECT * FROM information_schema.INNODB_TRX\G;

显示线程处于running状态并不是lock wait，oh，我就当作其是一个mysql的bug吧；不去深究了；

实践结论（无法解释最后一次实践insert into t values(3,2,3,3)被锁住）：

至此，mysql的在可重复读级别下的锁机制基本了解清楚了；

  1. 加锁的单位是next key lock，不会混用行锁、gap lock、next key lock，唯一例外情况就是第二点；

  2. 例外情况：唯一索引的等值查询时，next key lock会降级为行锁；

  3. next key lock加入的间隙锁对select、insert、delete无效；

  4. 加锁行为会不仅仅在当前索引，还会锁住满足过滤条件的存在的数据的聚集索引；

     select * from t for update;// 这语句想想都很带劲；

  5. 非唯一索引的等值查询：值的next key lock；

  6. 非等值查询，next key lock往左往右各找到一个不满足要求的数据a和b，对(a,b)中的数据加next key lock；

利用select * from performance_schema.data_locks\G 查看锁验证我们的结论并找到结论缺陷；

表中的数据：

// 主键id，索引c，唯一索引e；
id  c   d   e
0   0   0   0
2   2   2   2
5   5   5   5
10  10  10  10
15  15  15  15
20  20  20  20
25  5   25  25
35  35  35  35

<del>事务A：</del>

<del> begin；</del>
<del> select * from t where c >= 5 and c <= 10 for update;</del>

<del>事务B：</del>

<del> select * from performance_schema.data_locks\G；</del>

<del>加锁情况：</del>

<del>+ 表意向写锁；</del>

<del>+ 主键上产生行锁：5，10，25；</del>

<del>+ C上行锁：（5，5），（5，25），（10，10），（15，15），这里前面的数字是c，后面的数字是id，因为不是唯一索引，所以c为5的记录可能存在多个，这里也猜测，如果只锁住了（5，5）可能不影响（5，25）；实践证明，这个猜测是对的；</del>

<del>纳尼？居然没有gap lock；现在理解了实践结论中next key lock加入的间隙锁对select、insert、delete无效的原因了，其根本就没有使用next key lock，而是直接使用了行锁；那么gap lock确实存在，且受影响的都是insert操作，到这里才恍然大悟，next key lock只在insert时候才会加上（实际上这个想法是错误的，正确原因是gap lock之间不互斥），一直以为mysql是在select时候加了间隙锁才会导致insert不成功</del>

<del>### 既然insert才是加上next key lock的操作，那就去探究一下其是如何加上next key lock，也许可以解释我们上面的实践结论中没能解释的现象；</del>

<del>表和数据还是上面一样；</del>

<del>事务A：</del>

<del> begin；
insert into t values(3,2,3,3);</del>

<del>事务B:</del>

<del> select * from performance_schema.data_locks\G;</del>

<del>#### 结果仅仅只有一个表意向写锁，一定是锁提前释放了；让我来想想办法；</del>

<del>上面的实践我记得向表中插入数据，首先是检查id，再检查e，在检查c，那加锁顺序应该也是这样；如果我锁住c，至少能看到id 和 e的加锁情况；动手试试</del>

<del>事务A：</del>

<del> begin；
select * from t where c=2 for update;
insert into t values(3,2,3,3);</del>

<del>事务B:</del>

<del> select * from performance_schema.data_locks\G;</del>

<del>其实到这里我已经头晕脑胀了，想要从表现推出mysql行为我觉得已经不可能了，太难了；比如上面觉得select不加gap lock，但时间上会加；但是范围查询时居然没加；我已经快疯了；如下：</del>

<del> select * from t where c=5 for update</del>

<del>他居然对（10，10）加了gap lock；</del>

<del>我意识到这样推测去了解mysql加锁机制不是一个好办法；每当我觉得马上就通了的时候又会陷入另一个陷阱；
我决定直接第二步，直接使用performance_schema.data_locks来看加锁情况；</del>

万恶的mysql，select * from performance_schema.data_locks\G；中显示的如下：

LOCK_TYPE: RECORD;
LOCK_MODE: X;
LOCK_STATUS: GRANTED;
LOCK_DATA: 15, 15，

居然是gap lock，我将其当成了行锁；关键他对gap 还有这样的写法：

LOCK_TYPE: RECORD;
LOCK_MODE: X GAP;
LOCK_STATUS: GRANTED;
LOCK_DATA: 15, 15，

很容易误以为这是间隙锁而上面的是行锁吧？或者说就是mysql开发人员的疏忽？啊啊啊··，我的脑细胞，我本来就不多的发量！！！一天的努力白费了,shit！前面删除的重新来过，没有删除内容也是提醒自己，不要轻易相信任何东西：

表中的数据：

// 主键id，索引c，唯一索引e；
id  c   d   e
0   0   0   0
2   2   2   2
5   5   5   5
10  10  10  10
15  15  15  15
20  20  20  20
25  5   25  25
35  35  35  35

事务A：

begin；
select * from t where c >= 5 and c <= 10 for update;

事务B：

select * from performance_schema.data_locks\G；

加锁情况：

  • 表意向写锁；

  • 主键上产生行锁：5，10，25；

  • C行上的next key lock：（5，5），（5，25），（10，10），（15，15），这里前面的数字是c，后面的数字是id，因为不是唯一索引，所以c为5的记录可能存在多个；

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。