MySQL锁机制：从懵逼到入门，我花了三年

MySQL锁机制从懵逼到入门我花了三年写在前面这篇文章源于我被死锁折磨的那些日日夜夜。如果你也曾经对着SHOW ENGINE INNODB STATUS发呆或者被间隙锁搞得怀疑人生那这篇文章可能就是为你写的。一、故事的开始一个诡异的阻塞三年前我刚接手一个电商项目。某天凌晨两点报警群炸了“订单创建接口超时”。我睡眼惺忪地打开监控发现数据库有一堆Lock wait。再看慢查询日志有一条 SQL 格外刺眼SELECT*FROMordersWHEREorder_id12345FORUPDATE;“这不就是查个订单吗怎么还能把数据库卡死” 我当时是这么想的。后来才知道order_id 12345这条记录根本不存在。而在 Repeatable Read 隔离级别下这条 SQL 竟然锁住了整个间隙导致所有插入订单的请求都被阻塞。那一刻我意识到我对 MySQL 锁的理解基本为零。二、锁的粒度从粗到细表锁简单粗暴想象一下你家的卫生间。表锁就像是给整个卫生间上锁。你要上厕所必须把整个卫生间的门锁上。别人想洗手不行等着。想刷牙也得等着。MyISAM 引擎就是这么干的。它的优点是实现简单开销小。缺点嘛……并发性能惨不忍睹。-- 表级读锁LOCKTABLESmy_tableREAD;-- 表级写锁LOCKTABLESmy_tableWRITE;行锁精细管理行锁就像是给卫生间的每个设施单独上锁。马桶、洗手池、淋浴间各自独立。你可以用马桶别人可以同时洗手。InnoDB 引擎支持行锁。这是它成为 MySQL 默认引擎的重要原因之一。但有个坑行锁是锁在索引上的这句话我说了无数遍但很多人还是不信。来看个例子-- 假设 id 是主键SELECT*FROMusersWHEREname张三FORUPDATE;如果name字段没有索引InnoDB 会怎么做全表扫描然后锁住每一行这跟表锁有什么区别所以一定要加索引。这不是建议是铁律。三、锁的模式共享与排他共享锁S锁大家一起看共享锁就是读锁。你可以看我也可以看但谁都不能改。SELECT*FROMusersWHEREid1LOCKINSHAREMODE;-- MySQL 8.0SELECT*FROMusersWHEREid1FORSHARE;排他锁X锁这是我的排他锁就是写锁。我拿了这把锁别人既不能看某些情况下也不能改。UPDATEusersSETage25WHEREid1;-- 或者SELECT*FROMusersWHEREid1FORUPDATE;意向锁提前打招呼这个概念很多人不理解。意向锁是表级锁用来告诉别人“我准备对表里的某些行加锁了”。意向共享锁IS我打算给某些行加 S 锁。意向排他锁IX我打算给某些行加 X 锁。有什么用假设你想给整个表加排他锁LOCK TABLES ... WRITE你怎么知道表里有没有行被锁住了总不能一行行检查吧这时候意向锁就派上用场了。只要检查表上有没有 IS 或 IX 锁就知道有没有行被锁住。四、三种锁算法核心难点来了这部分是重头戏也是大多数人包括当年的我最容易混淆的地方。1. 记录锁Record Lock最简单就是锁住某一行。-- 假设 id3 存在SELECT*FROMusersWHEREid3FORUPDATE;这条 SQL 会对id3这一行加排他锁。2. 间隙锁Gap Lock这个就有点意思了。它不锁记录只锁间隙。假设表里有id 1, 3, 5, 7。你执行SELECT*FROMusersWHEREid4FORUPDATE;id4不存在对吧但在 Repeatable Read 隔离级别下InnoDB 会加一个间隙锁锁住(3, 5)这个区间。为什么要这么做防止幻读。如果别人在你查询的间隙里插入了一条id4的记录你再次查询时就会看到这条凭空出现的数据这就是幻读。3. 临键锁Next-Key Lock这个名字听起来很玄乎其实就是记录锁 间隙锁的组合。InnoDB 在 Repeatable Read 级别下默认使用临键锁。它锁住的是(prev, current]这个区间左开右闭。举个例子表里有id 2, 5, 9。你执行SELECT*FROMusersWHEREid5ANDid9FORUPDATE;InnoDB 会怎么加锁对id5加临键锁锁住(2, 5]对id9加临键锁锁住(5, 9]注意不会锁住(9, ∞)因为你的查询条件是 9插入id10不会影响你的查询结果。一个重要的优化很多人不知道InnoDB 有个聪明的优化如果是唯一索引的等值查询且记录存在临键锁会退化成记录锁。-- id 是主键唯一索引SELECT*FROMusersWHEREid5FORUPDATE;-- id5 存在这条 SQL 只会对id5加记录锁不会加间隙锁。为什么因为id是唯一的不可能有另一条id5的记录插入。既然不会有幻读为什么要锁间隙这个优化极大地提高了并发性能。五、隔离级别锁的行为大不同MySQL 有四种隔离级别但常用的只有两种Read Committed (RC)和Repeatable Read (RR)。RR默认安全但保守RR 是 MySQL 的默认隔离级别。它的特点是使用临键锁防止幻读范围查询会锁住间隙并发性能相对较低RC激进但高效RC 隔离级别下不加间隙锁这是关键只锁住实际存在的记录并发性能更高这意味着什么在 RC 级别下你执行SELECT*FROMusersWHEREid4FORUPDATE;-- id4 不存在不会加任何锁因为记录不存在RC 级别不会加间隙锁。再比如SELECT*FROMusersWHEREid5ANDid9FORUPDATE;只会对id5和id9加记录锁不会锁住间隙。别人可以自由插入id6, 7, 8。那幻读怎么办RC 级别允许幻读。这是用一致性换取性能的权衡。我该选哪个金融、支付类系统用 RR数据一致性更重要高并发互联网应用考虑 RC性能更重要当然要能接受幻读我参与过的项目90% 都改成了 RC 级别。原因很简单间隙锁导致的锁等待真的太常见了。六、MVCC锁的好兄弟很多人以为防止脏读、不可重复读、幻读全靠锁。这是错的。InnoDB 有个神器叫MVCC多版本并发控制。它的作用是普通 SELECT快照读不加锁通过 MVCC 读取历史版本防止脏读只能看到已提交的数据防止不可重复读RR 级别整个事务看到同一个快照关键区别-- 快照读不加锁用 MVCCSELECT*FROMusersWHEREid1;-- 当前读加锁读最新版本SELECT*FROMusersWHEREid1FORUPDATE;UPDATEusersSETage25WHEREid1;DELETEFROMusersWHEREid1;所以防止幻读的机制有两个快照读靠 MVCCRR 级别下整个事务看到同一个快照自然不会有幻读当前读靠间隙锁/临键锁防止别人插入新数据七、实战如何排查锁问题理论说了这么多来点实际的。1. 查看当前锁-- MySQL 8.0SELECT*FROMperformance_schema.data_locks;-- 查看锁等待SELECT*FROMperformance_schema.data_lock_waits;2. 查看死锁信息SHOWENGINEINNODBSTATUS;在输出结果里找LATEST DETECTED DEADLOCK部分。这里会详细告诉你哪个事务持有什么锁哪个事务在等待什么锁死锁是怎么形成的3. 一个真实的死锁案例事务 ABEGIN;UPDATEusersSETage25WHEREid1;UPDATEusersSETage26WHEREid2;事务 BBEGIN;UPDATEusersSETage27WHEREid2;UPDATEusersSETage28WHEREid1;看到了吗事务 A 锁了id1想锁id2事务 B 锁了id2想锁id1。典型的死锁。解决方案统一访问顺序。所有事务都先访问id1再访问id2。4. 间隙锁导致的阻塞这个更隐蔽。事务 ABEGIN;SELECT*FROMusersWHEREid100FORUPDATE;-- id100 不存在事务 BINSERTINTOusers(id,name)VALUES(100,张三);-- 阻塞为什么因为事务 A 在 RR 级别下对不存在的id100加了间隙锁。事务 B 的插入操作被阻塞。解决方案改用 RC 隔离级别确保查询的记录存在用INSERT ... ON DUPLICATE KEY UPDATE代替SELECT INSERT/UPDATE八、避坑指南我踩过的雷坑 1忘记加索引-- name 字段没有索引SELECT*FROMusersWHEREname张三FORUPDATE;后果全表锁。所有对users表的写操作都会被阻塞。检查方法用EXPLAIN看 SQL 是否走索引。坑 2范围查询锁住大片间隙SELECT*FROMordersWHEREcreated_at2024-01-01FORUPDATE;后果锁住从2024-01-01到正无穷的所有间隙。新订单无法插入。解决方案改用 RC 隔离级别缩小查询范围比如只查某一天的订单避免对范围查询加锁坑 3大事务长时间持有锁BEGIN;-- 处理 10000 条订单UPDATEordersSETstatus1WHEREidIN(...);-- 发送 10000 封邮件-- 调用外部 API-- ... 过了 30 秒COMMIT;后果锁被持有 30 秒其他事务全部阻塞。解决方案大事务拆成小事务批量处理每 100 条提交一次把耗时操作发邮件、调 API放到事务外坑 4RR 级别下批量插入不存在的 ID-- 批量检查订单是否存在SELECTidFROMordersWHEREidIN(1001,1002,1003,...)FORUPDATE;-- 然后插入不存在的订单如果这些 ID 都不存在在 RR 级别下会锁住大片间隙导致并发插入性能极差。解决方案改用 RC用INSERT ... ON DUPLICATE KEY UPDATE先查已存在的 ID只处理不存在的九、最佳实践我现在的做法经过这些年的踩坑我总结了一套自己的实践1. 隔离级别默认 RC除非有强一致性要求否则一律用 RC。间隙锁导致的性能问题真的太常见了。2. 索引必须加所有WHERE、JOIN、ORDER BY的字段都要考虑加索引。用EXPLAIN验证。3. 事务越小越好只包含必要的 SQL耗时操作放到事务外批量操作分批提交4. 锁能不用就不用优先用乐观锁version字段避免SELECT ... FOR UPDATE除非真的需要如果必须用确保走唯一索引5. 监控必须做监控锁等待时间监控死锁次数定期分析慢查询日志十、总结MySQL 的锁机制说复杂也复杂说简单也简单。核心就几点行锁是锁在索引上的—— 没索引 表锁RR 级别有间隙锁RC 级别没有—— 这是性能差异的关键临键锁 记录锁 间隙锁—— RR 级别的默认策略MVCC 负责快照读锁负责当前读—— 两者配合实现隔离唯一索引等值查询会优化—— 临键锁退化成记录锁最后送大家一句话锁是用来保护数据的不是用来折磨开发者的。理解它才能用好它。希望这篇文章能帮你少走点弯路。如果还有疑问欢迎留言讨论。毕竟我也是从懵逼过来的。参考资源MySQL 官方文档InnoDB Locking《高性能 MySQL》第 7 章源码storage/innobase/lock/lock0lock.cc实验环境MySQL 8.0.32隔离级别Repeatable Read除非特别说明引擎InnoDB如果你觉得这篇文章有帮助欢迎转发给更多被锁折磨的朋友。毕竟独惨惨不如众惨惨开玩笑的是共同进步。