Mysql是怎么加锁的？

原文地址https://www.xiaolincoding.com/mysql/lock/how_to_lock.html#%E4%BB%80%E4%B9%88-sql-%E8%AF%AD%E5%8F%A5%E4%BC%9A%E5%8A%A0%E8%A1%8C%E7%BA%A7%E9%94%81我只是精简一下做个记录这篇汇总将基于MySQL 8.0 的 InnoDB 引擎在可重复读Repeatable Read, RR隔离级别下进行深度的全场景拆解。核心底座加锁准则原则加锁的基本单位是Next-Key Lock临键锁区间是 (前一个记录, 当前记录]。退化为了提高并发MySQL 会在确保“不产生幻读”的前提下将临键锁降级为Record Lock记录锁或Gap Lock间隙锁。查找索引查找过程中扫描到哪里锁就加到哪里。准备环境表user主键 id普通索引 age无索引字段 name。数据1,10 5,20 10,30 15,40 20,50一、 唯一索引主键加锁逻辑1. 等值查询记录存在如 id5锁记录锁 id5。原因唯一性保证了不可能有第二个 id5 插入只需锁住这一行不需要锁间隙最极致的退化。记录不存在如 id7锁间隙锁 (5, 10)。原因锁是加在索引上的既然 7 这条记录不存在没法直接锁行。扫描到 10 时发现大于 7为了防止别人插入 7只能把 5~10 之间的“物理空地”锁住。放过 10 是因为 10 本身与 7 无关。2. 范围查询、、、id 5锁(5, 10]、(10, 15]、(15, 20]、(20, supremum]。注supremum是无穷大伪记录原因所有的 Next-Key Lock 都不降级确保整个范围及后续间隙无法插入。id 5锁记录锁 id5 5 10]10 15] ...原因id5 是等值匹配且唯一所以 5 这一处精准降级为记录锁后面的维持临键锁。id 15锁-∞ 1]、1 5]、5 10]、间隙锁 10 15。注意MySQL 扫描到 15 时发现不满足 15于是最后的临键锁 (10, 15]退化为间隙锁 (10, 15)。既防止了 15 之前的间隙被插入又释放了对 15 本身的锁定。id 15锁-∞ 1]、1 5]、5 10]10 15]。原因因为 15 在查询范围内必须被锁住同时为了防止 10~15 之间的间隙被插入数据必须保持完整的临键锁不发生降级二、 非唯一索引普通索引加锁逻辑这是最容易搞混的地方因为它需要同时保护二级索引树和主键聚簇索引树。1. 等值查询记录存在如 age20锁age 索引上加 (10, 20] 的临键锁并且额外加 (20, 30) 的间隙锁。为什么非唯一索引必须把 20 左右两边的物理过道都封死否则别人可以插入一个新的 age20只要主键不同即可导致幻读。回表对应的 id5 主键索引上会加一个记录锁。记录不存在如 age25锁age 索引上加 (20, 30) 间隙锁。原因退化逻辑与唯一索引未命中时一致只需守住这块无人区。2. 范围查询、、、年龄 20锁age 索引上 20 30]、30 40]、40 50]、50 supremum]。规则非唯一索引的范围查询保护极严全是 Next-Key Lock基本不降级。年龄 20锁age 索引上 10 20]20 30] ...区别即使是等值部分非唯一索引也不会像主键那样降级为记录锁因为 20 前面的间隙也得守住防止插队。三、 各种符号的“退化”总结表 (修正版)场景索引类型匹配结果最终锁范围备注等值 ()唯一存在记录锁最极致的性能降级精准打击等值 ()唯一不存在间隙锁退化仅锁住目标所在的真空地带等值 ()非唯一存在Next-Key 随后的 Gap双重保护前后过道全部封死范围 ()唯一包含边界边界记录锁 后续 Next-Key起点等于精确查找起点降级范围 ()唯一-全程 Next-Key不降级范围 ()唯一不含边界前置 Next-Key 最后的 Gap扫到第一个不符合条件的边界边界退化为间隙锁范围 ()唯一包含边界全程 Next-Key因为要锁住边界又要保护间隙不降级所有范围非唯一-全程 Next-Key保护极严绝不降级四、 没加索引的查询全表封杀SQL更新用户设置年龄100 其中 name 张三;过程InnoDB 发现 name 没索引只能走聚簇索引主键进行全表扫描。扫描时每一行记录都会被加上Next-Key Lock。结果是-∞ 1]、1 5]、5 10] ...直到 ∞ supremum]。后果这意味着整张表无论是修改已有行还是插入新行全被锁死这就是俗称的“锁表”。补充注意MySQL 内部有优化在某些版本中如果扫描后发现不匹配会提前释放掉不符合条件的锁。但在高并发瞬间全表扫描依然可能导致大规模锁等待或死锁。五、 终极恍然大悟为啥要这么变为啥唯一索引等值存在就降级记录锁因为主键 ID 是唯一的只要我锁住了 ID5 这一行全世界都不可能再产生第二个 ID5。所以我不需要管 (1, 5) 的间隙。为啥非唯一索引等值还要加间隙锁因为你有 age20我再插一个 age20主键设为 99是可以成功的。如果不锁间隙你第二次查 age20 就会多出一行这就是“幻读”。为啥范围查询很少降级范围查询的目标通常是保护“一片领土”。如果你查 id 5哪怕 5 后面的 6, 7, 8 现在不存在我也得防着你所以必须把后面所有的间隙全焊死。 一句话总结能降级退化的前提是——即使我放开了间隙你也绝对无法在我的眼皮底下插进一行符合我查询条件的“幻影”数据。如果不能保证这一点MySQL 就会死死握住 Next-Key Lock 不撒手。