mysql事务中加锁总结202303 mysql8.0.20

JohnDWall
342 阅读9分钟

一直很 困惑代码里面打了注解，MySQL 到底是怎么加锁的，会加哪些锁，加了锁会对其他事务有什么影响 ， 也看过不少这方面的博客，但是自己没有实操过，记忆也就不那么深刻。看了之后只能是将信将疑，云里雾里。 所以就模拟一遍各种场景sql执行之后，看看会产生哪些锁。贴图说明。

解释、前提

  • 基于mysql8.0
  • 只对事务隔离级别做测试 RR
  • 做了模拟简化，不是标准
  • 开启了自动提交

LOCK_MODE字段值说明

  • IX: 表级意向排他锁
  • S: 共享锁
  • X: 排他型 NEXT-KEY锁，等价于 记录锁+gap锁
  • X,REC_NOT_GAP: 排他型 记录锁
  • X,GAP: 排他型 gap锁

测试用的表，索引结构如下:

截屏2023-03-30 21.28.39.png

场景一 ：通过普通二级索引锁定读

代码，sql

@Transaction(rollbackfor=exception.class)
public void traction(Param args){
 // 锁定 读
 1.  SELECT * FROM d_delivery WHERE delivery_code='9107903282' AND requirement_company_code='5500' FOR  UPDATE ;
  ......
     return;
}
以上代码类似于在 数据库执行了如下操作：
start transaction;

SELECT * FROM d_delivery WHERE delivery_code='9107903282' AND requirement_company_code='5500' FOR  UPDATE ;

结论：共8个锁，3个 二级索引记录锁，3个主键索引记录锁，一个表级IX锁，一个gap锁，加锁先后顺序如下图： 表IX锁，二级索引X锁，主键X锁，gap锁

SELECT * FROM performance_schema.data_locks;

截屏2023-03-30 21.33.30.png

  1. sql使用普通二级索引delivery_code执行查询，如果只根据二级索引条件有3条记录返回，加上了其他非索引条件如requirement_company_code='5500' ,查询结果只有2条记录返回，但是加锁结果也会对满足二级索引值的3条二级索引和主键索引加锁。

  2. 对查询表加IX 锁，会用于其他事务在加表级共享锁或排他锁的时候快速判断当前表有没有记录加了行锁

  3. 3条二级索引记录加排他记录锁，其他事务对加锁的记录做 锁定读 或 更新，会被阻塞等待，需要等当前事务提交释放锁之后才能执行，快照读可以正常执行

  4. 3条二级索引记录对应的主键索引加排他记录锁，其他事务对加锁的记录通过主键值做 锁定读 或 更新，会被阻塞等待，需要等当前事务提交释放锁之后才能执行

  5. 对距离二级索引条件delivery_code='9107903282' 最近的下一个二级索引值加gap锁，在这里就是对 9107903283 的二级索引记录加锁。假设当前二级索引值最近的上一个二级索引值是 9107903277，则在二级索引值(9107903277,9107903282]区间，另外一个事务插入新记录会被阻塞，gap锁的加锁记录9107903283的新记录插入不会阻塞。9107903277的新记录，有一种特殊情况可以插入，当插入的新记录指定了主键id且主键ID值小于加锁事务里面二级索引值为9107903277 的所有记录中最小主键ID。

  6. 不会对任意其他insert语句做有关主键ID值范围区间的插入阻塞，只要他的二级索引值不在上面两个gap锁锁定的区间

加锁并发事务场景测试

以上代码执行到代码1结束之后，在数据库里面就有了另外上面8个锁，查看当前数据库事务状态是运行中，如下： 截屏2023-03-30 21.42.37.png 并发分析主要分析两点：加锁的记录其他事务是否能够更新或查询；加了间隙锁哪些地方不能插入新记录

  1. 现在另外一个事务锁定读取/更新条件delivery_code='9107903282' 的值，不能查询或更新，事务状态等待获取锁 LOCK_WAIT
UPDATE d_delivery set factory_code='sdfjoasdjfajdfqewr' WHERE delivery_code='9107903282';
或者
SELECT * FROM d_delivery WHERE delivery_code='9107903282' AND requirement_company_code='5500' FOR  UPDATE;

执行之后查询事务状态：SELECT * FROM information_schema.innodb_trx; 截屏2023-03-30 21.48.39.png

  1. 另外一个事务是 快照读delivery_code='9107903282' 的值，能正常读取，不会锁定
  2. 在二级索引值(9107903277,9107903282]区间，另外一个事务插入新记录会被阻塞
INSERT INTO `d_delivery`( `requirement_company_code`, `delivery_code`, ) VALUES ('5500', '9107903277');

执行之后查询事务状态： 截屏2023-03-30 22.37.20.png

场景二： 普通二级索引范围查询锁定读

sql

start transaction;
SELECT * FROM d_delivery WHERE delivery_code>='91230202002211'  OR (delivery_code>='9107903274' AND delivery_code<'9107903282')  FOR  UPDATE ;

结论

  1. 对表加IX锁， 6条二级索引记录加X锁，对应的6个主键索引也加X锁，根据OR把二级索引字段划分出两个范围区间，每个区间满足二级索引条件的最大值右边第一条记录分别加上gap锁，一共15个锁结构

截屏2023-03-30 23.16.16.png

  1. 第一个查询区间delivery_code>='91230202002211'，他满足二级索引条件的最大值右边第一条记录，么有用户记录，会把gap锁加在mysql内置的虚拟记录 最大记录上面(supremum)

  2. 在当前事务未提交释放锁之前，另一个事务锁定读 目测如场景1，没有再单独做测试

  3. 第一个范围区间是gap锁在最大虚拟记录,任何大于等于91230202002211二级索引的值插入都会阻塞.

  4. 第二个范围区间 gap锁在9107903282值，在区间[9107903274,9107903282)插入新值会阻塞

  5. 不会对任意其他insert语句做有关主键ID值范围区间的插入阻塞，只要他的二级索引值不在上面两个gap锁锁定的区间

延伸

根据上面普通二级索引范围查询，可以看到他加gap锁根据查询条件做了区分，有几个查询区间就有几个gap锁，类似的sql还有比如IN子句 SELECT * FROM d_delivery WHERE delivery_code in(a,b,c),就有了3个区间.执行下面sql，

BEGIN;

SELECT * FROM d_delivery WHERE delivery_code IN('91230202002211','9107903274' ,'9107903277') FOR  UPDATE ;

满足条件的二级索引记录3条，主键3个，表IX锁一个，范围区间3个，所以有3个gap锁，一共10个锁

截屏2023-03-30 23.26.51.png

场景三：普通二级索引等值条件更新

sql

start transaction;

UPDATE d_delivery  SET requirement_company_code='9999' WHERE delivery_code='9107903282';

结论:加锁效果如同场景一对这个二级索引的锁定读。加锁先后顺序如下图： 表IX锁，二级索引X锁，主键X锁，gap锁

截屏2023-03-30 21.33.30.png

  1. sql使用普通二级索引delivery_code执行更新，如果只根据二级索引条件有3条记录返回，加上了其他非索引条件如requirement_company_code='5500' ,查询结果只有2条记录返回，但是加锁结果也会对满足二级索引值的3条二级索引和主键索引加锁。
  2. 对查询表加IX 锁，会用于其他事务在加表级共享锁或排他锁的时候快速判断当前表有没有记录加了行锁
  3. 3条二级索引记录加排他记录锁，其他事务对加锁的记录做 锁定读 或 更新，会被阻塞等待，需要等当前事务提交释放锁之后才能执行，快照读可以正常执行
  4. 3条二级索引记录对应的主键索引加排他记录锁，其他事务对加锁的记录通过主键值做 锁定读 或 更新，会被阻塞等待，需要等当前事务提交释放锁之后才能执行
  5. 对距离二级索引条件delivery_code='9107903282' 最近的下一个二级索引值加gap锁，在这里就是对 9107903283 的二级索引记录加锁。假设当前二级索引值最近的上一个二级索引值是 9107903277，则在二级索引值(9107903277,9107903282]区间，另外一个事务插入新记录会被阻塞，gap锁的加锁记录9107903283的新记录插入不会阻塞。9107903277的新记录，有一种特殊情况可以插入，当插入的新记录指定了主键id且主键ID值小于加锁事务里面二级索引值为9107903277 的所有记录中最小主键ID。

场景四：非索引字段等值条件更新

sql

start transaction;

UPDATE d_delivery  SET requirement_company_code='9999' WHERE ggg_id='769bde1b7b0b4c41a7e9c76a41b9feae';

结论

  1. 先对表加IX锁
  2. 不会加表锁，会给每行主键索引加X锁，每个主键索引叶子节点虚拟最大记录也加X锁。
  3. 抽查测试了若干锁定读，更新语句，都会阻塞，类似于全表都锁住了，但是快照读可以正常执行

场景五 ： 主键索引的更新

sql

参考文档

各种博客文档，加上自己实际编写测试

avatar
文章
阅读
粉丝