InnoDB 锁机制

InnoDB 锁机制 #

锁分类和特性 #

  • S锁与X锁互斥，与S锁兼容
  • X锁与S/X锁互斥
  • 加锁是实际是锁索引或者锁表
    • 如果用了主键就锁聚簇索引
    • 如果用了二级索引就锁定二级索引再锁定聚簇索引
    • 如果没用到索引，就锁表
  • 锁的释放时机是事务提交或回滚

行级锁 #

  • 基本的加锁是临键锁，因为一些条件转换为间隙锁、行锁、表锁

记录锁（S/X锁） #

单索引值锁

间隙锁（S锁） #

  • 在两行/两索引之间的左开右开区间锁
  • 间隙锁S与插入意向锁X互斥，作用是防止其他事务插入数据，避免幻读
  • 间隙锁S之间是兼容的

临键锁（S/X记录锁+s间隙锁） #

  • 对记录行，以及以记录行本身主键/索引值为右边界，往前一个主键值/索引值为左边界的，左开右闭区间锁
  • 临键锁的记录部分与其他临键锁的记录部分根据记录锁的S/X区分冲突
    • 临键锁的记录部分与其他临键锁的间隙锁不会冲突，不如说不存在加了间隙锁还有记录在中间的情况，也不存在有记录锁居然能加间隙锁的情况
    • 临键锁的间隙锁和其他临键锁的间隙锁不冲突

插入意向锁（X模式间隙锁） #

  • 与普通S间隙锁互斥，插入意向锁之间不互斥（特殊）
  • insert插入数据时，需要对所在间隙加插入意向锁，多个事务可以对同一间隙加插入意向锁
  • 如果该间隙存在普通间隙锁，则插入意向锁会被阻塞
  • 多个事务插入数据，只要对应主键和索引无约束冲突，就可以并发执行

表级锁 #

表锁（S/X锁） #

  • 当进行需要加锁操作，但是未能明确指定主键/索引时，Innodb会扫描全表，对主键聚簇索引加临键锁（覆盖所有行，等效表锁）
  • 或显示使用 Lock Tables xxx write/read (Innodb不推荐，应优先行锁)
  • 是极端状态下的行锁集合（全表行锁+间隙锁），性能极差
  • S表锁阻塞各种行X锁，不阻塞S锁。X表锁阻塞各种行X锁

意向锁（IS/IX锁，表级信号锁） #

  • 当事务对某行加行级S锁，自动对表加意向共享锁（IS锁）
  • 当事务对某行加行级X锁，自动对表加意向排他锁（IX锁）
  • 永远与行级S/X锁共存（行锁必然带有对应意向锁）
  • 作用仅是标记“表中存在行锁”，以与全表锁互斥，不阻塞其他行锁和表意向锁

语句加锁情况枚举（可重复读级别） #

可以根据语句加锁情况和对应区域锁的S/X模式，来理论判断锁冲突情况

普通查询 #

  • 不加锁，快照读，不会导致并发阻塞。且因为是快照读，不会幻读
  • 基于MVCC进行快照读，查询开始时生成快照，可读取已提交事务和本快照事务修改的数据

SELECT … FOR UPDATE #

  • 唯一索引等值查询且匹配到数据，对匹配行加行锁（X锁）
  • 唯一索引范围查询且匹配到数据，对所有匹配行加临键锁（X记录锁+S间隙锁）
  • 唯一索引查询未匹配到数据，目标值所在间隙加间隙锁（S锁）
  • 普通索引范围查询匹配到数据，范围内索引项加临键锁（X记录锁+S间隙锁），对应主键行锁（X锁）
  • 普通索引查询未匹配到数据，目标值所在间隙加间隙锁（S锁）
  • 不使用索引/索引失效，退化为全表锁（各行X锁，间隙S锁）

SELECT … LOCK IN SHARE MODE #

  • 唯一索引等值查询且匹配到数据，对匹配行加行锁（S锁）
  • 唯一索引范围查询且匹配到数据，对所有匹配行加临键锁（S记录锁+S间隙锁）
  • 唯一索引查询未匹配到数据，目标值所在间隙加间隙锁（S锁）
  • 普通索引范围查询匹配到数据，范围内索引项加临键锁（S记录锁+S间隙锁），对应主键行锁（S锁）
  • 普通索引查询未匹配到数据，目标值所在间隙加间隙锁（S锁）
  • 不使用索引/索引失效，退化为全表锁（各行S锁，间隙S锁）

insert #

  1. 单行插入
  • 尝试对插入间隙加插入意向锁X锁，如果存在间隙锁S锁，就阻塞等待
  • 加锁成功后执行插入，对插入位置加X锁，仅自己能插入，并执行插入
  • 如果位置上有别的数据插入的数据，则阻塞等待其他事务提交释放锁
  • 多个事务可以并发插入同个间隙的不同位置，不互相阻塞
  1. 批量插入 逐行执行多个单行插入，重复上述过程

update #

  • 对where条件匹配的行加X锁
    • 如果用了唯一索引（索引扫描），对匹配索引值加记录X锁
    • 如果用了普通索引（索引扫描），对匹配索引值加临键X锁
    • 如果没有使用索引或者索引失效（全表扫描），对全表加X表锁(所有记录X临键锁)
  • 如果会更新索引字段，旧索引项加X锁

delete #

  • 对where条件匹配的行加X锁
    • 如果用了唯一索引（索引扫描），对匹配索引值加记录X锁
    • 如果用了普通索引（索引扫描），对匹配索引值加临键X锁
    • 如果没有使用索引或者索引失效（全表扫描），对全表加X表锁(所有记录X临键锁)

可以优化的操作方向 #

测试机检查 #

测试SQL执行究竟是加表锁还是行锁等，修改以避免表锁

慎用显式加锁 #

select … for update 和 select … lock in share mode 必须使用索引，并检查加锁情况，避免锁表

减小加锁范围 #

每次事务，加锁范围尽量小，以提高并发量，减少并发冲突

减少持锁时间 #

锁施放时间为事务提交时，如果数据库操作完毕后续的步骤失败不需要回滚数据，可以先提交事务，避免长事务，减少持锁时间，提升并发

需要警惕的死锁操作场景 #

死锁的条件是 并发资源争抢 + 不同的加锁顺序
在数据库中，并发的是事务，资源是行或者表，锁指的是互斥的锁对（X-S, X-X）
可以说，只要两个事务同时要操作同一部分数据，并且涉及加锁（加锁读/insert/update/delete），就需要警惕死锁
一般来说，最常见的是批量的insert/update/delete并发

  • insert批量不容易死锁，毕竟是逐行插入，就算两事务并发插入，针对的也是一块空白区域，每一行A先填，B就等，A提交释放锁再执行
  • update批量容易死锁，两事务同时update两行数据(同表或不同表)，A事务先更新1再更新2，B事务先更新2再更新1，并发时互相等锁导致死锁
    • 扩展为两事务并发批量更新两部分数据，但是更新数据行有所重叠，并且更新顺序不同，可能导致死锁
  • delete批量不容易死锁，删了就删了
  • 具体场景具体分析，充分考量并发情况下的各种加锁组合判断，并在测试库中测试对应情况是否死锁

加锁场景众多，仍需实践具体辨析

2025-05-03