MySQL锁机制全解析：从基础分类到死锁排查与性能调优

聊到 MySQL 锁很多人的第一反应就是死锁、锁表、锁等待超时这几个词。我刚开始接手线上数据库那会儿最怕的就是半夜收到锁等待报警语句其实很简单但就是跑不动整条业务链路被拖死。后来踩过几次坑把锁相关的机制、参数、监控工具彻底梳理了一遍才慢慢从遇到问题查状态变成提前在设计和编码阶段规避锁问题。这篇内容就围绕 MySQL 锁展开适合后端开发、DBA、以及对数据库并发控制有兴趣的朋友参考。我会从锁的分类、事务隔离级别对锁边界的影响、真实的死锁排查链路、分布式锁的取舍、再到日常监控常用的 SQL 和参数调优一次性把杂的知识串成体系。1. 先理清锁的分类与本质MySQL 到底在锁什么很多人看锁相关的文章上来就是一堆名词表锁、行锁、共享锁、排他锁、意向锁、间隙锁……背完就忘。我觉得先想清楚一个根本问题——锁到底锁住了什么——再去看分类思路会顺畅很多。1.1 按粒度分表锁、行锁、页面锁锁的粒度直接决定了并发能力。MySQL 里不同存储引擎的做法差别很大这也是为什么选表引擎时要认真考虑。表级锁开销小、加锁快、不会出现死锁锁的是整张表嘛但并发度极低。MyISAM 引擎只有表锁这也是它逐渐被 InnoDB 替代的原因之一。即便在 InnoDB 里LOCK TABLES ... READ/WRITE这种显式表锁依然存在但日常业务基本用不到更多是在做备份、批量变更时临时使用。行级锁InnoDB 的看家本领锁粒度最小、并发度最高但加锁开销也最大而且可能出现死锁。注意行锁是建立在索引上的如果一个表压根没有索引或者 SQL 的 WHERE 条件没走索引InnoDB 就只能锁全表的所有记录实际上会升级成对所有行的锁。页面级锁介于表锁和行锁之间锁定一个数据页。PostgreSQL 在某些场景下会用页锁MySQL 的 InnoDB 在空间索引SPATIAL相关操作中也有类似机制但日常 OLTP 场景基本感知不到知道有这个东西就行。1.2 按模式分共享锁、排他锁、意向锁行锁本身还分读写两类加上表层的意向锁合起来构成了 InnoDB 的完整锁兼容矩阵。锁模式缩写含义兼容性共享锁S允许多个事务同时读同一行但禁止写入多个 S 兼容S 与 X 互斥排他锁X独占该行读写都被禁止与任何锁都互斥意向共享锁IS事务准备在某些行上加 S 锁先在表级别打个标记相互兼容意向排他锁IX事务准备在某些行上加 X 锁先在表级别打个标记相互兼容意向锁是很多初学者最容易忽略的。我举个例子你就明白它为什么必要事务 A 在 user 表的 1、3、5 三行上加了 X 锁事务 B 这时想执行LOCK TABLES user WRITE也就是对整个表加排他锁。如果没有意向锁InnoDB 就必须去扫描所有行看看有没有行被别的事务锁住。有了 IX 锁事务 A 在加行锁之前就先在表上打个我要锁行的标记事务 B 看到表上有 IX 锁立刻知道自己要加的表级 X 锁不兼容直接等待就行。意向锁本质是一个预告帮数据库省去了遍历检查的功夫。1.3 用户经常忽略的元数据锁 MDL除了上面这些标准锁还有一类锁是 DDL 和 DML 之间的协调者——元数据锁Metadata Lock简称 MDL。这个东西藏得很深平时不惹事一惹就是大事。经典场景你凌晨跑一个ALTER TABLE user ADD COLUMN age INT发现 SQL 卡住不动SHOW PROCESSLIST显示Waiting for table metadata lock。原因往往是有一个长时间未提交的事务在那张表上执行过 SELECT事务不结束MDL 就不会释放DDL 只能干等。而且 MDL 有排队机制后面所有对这张表的查询都会被阻塞一个小事务能拖垮整个业务。我在实际排查中遇到过不止一次这种情况教训是业务侧一定要设置合理的事务超时时间DDL 前先查 information_schema.innodb_trx 确认没有长事务。2. 事务隔离级别如何悄悄改变锁的边界锁不是孤立的机制它和事务隔离级别是相辅相成的。同样一条 UPDATE在不同隔离级别下加锁的范围可能完全不同。这也是明明只更新一行却把整张表锁住这类问题背后的核心原因。2.1 MVCC、当前读与快照读锁和读的关系InnoDB 默认隔离级别是可重复读RR它主要靠 MVCC多版本并发控制来实现快照读。普通 SELECT 是快照读不加锁读的是历史版本数据所以不会被其他事务的写操作阻塞。而SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT这四类是当前读读取的是最新版本并且要对涉及的行加锁。这个区分特别重要我见过不少人写代码时用SELECT * FROM order WHERE order_no ? FOR UPDATE做分布式锁请求量一上来就把数据库拖垮了。他不知道的是这条语句如果没走唯一索引锁的范围就不是一行而是扫描到的所有行。2.2 间隙锁与 Next-Key LockRR 隔离级别下的特殊边界在 RR 隔离级别下InnoDB 为了彻底解决幻读引入了间隙锁Gap Lock。间隙锁锁的是记录与记录之间的空隙以及第一条记录之前的区间和最后一条记录之后的区间。它和行锁组合起来就是 Next-Key Lock临键锁锁住的是一个左开右闭的区间。举个具体的例子假设user表有 id 为 1、5、9 的三条记录事务 A 执行SELECT * FROM user WHERE id BETWEEN 2 AND 6 FOR UPDATE。这条 SQL 实际加锁的范围不只是 id5 这一行而是 (1,5]、(5,9) 以及 id6 这个不存在的行本身。也就是说其他事务想往 id3、id7 这些位置插入数据时会被阻塞。这看起来有点霸道但在 RR 级别下是为了保证同一事务内两次查询的结果一致防止幻读。很多团队从 RR 切到 RC读已提交之后发现并发能力大幅提升就是因为 RC 级别下 InnoDB 关闭了间隙锁只保留行锁和外键锁。代价是会有幻读现象但如果业务可以接受RC 往往是一个更好的选择。MySQL 8.0 里默认还是 RR 的这个不必迷信默认值要根据业务场景去权衡。2.3 一个真实的锁范围扩大案例以前有个订单服务的线上问题订单表 order 有 30 万行一条很简单的UPDATE order SET status 1 WHERE merchant_id 100 AND order_no SN123456偶尔会阻塞大量其他订单查询。把执行计划拿出来一看merchant_id没走索引order_no虽然有普通索引但没用到——因为查询条件是merchant_id和order_no的组合优化器最终选择了全表扫描。在 RR 级别下全表扫描意味着对扫描路径上的所有聚簇索引记录都加了 Next-Key Lock这张表瞬间就是只读状态。这里的解决思路有三层第一给(merchant_id, order_no)建联合索引让 UPDATE 精准定位到单行第二如果确实无法加索引在可重复读级别下把隔离级别调整为 RC让扫描路径上的间隙锁消失第三把大事务拆小避免一条 UPDATE 拖太久。这个案例的核心结论是锁的范围取决于访问路径索引决定路径路径决定锁的边界。3. 一次真实的锁等待死锁排查从 show processlist 到根因光讲理论容易飘我把一次线上死锁的排查过程完整复盘出来你可以直接照着这套链路去复现和定位。3.1 第一反应死锁日志怎么看当时业务报错日志里出现了很典型的提示Deadlock found when trying to get lock; try restarting transaction。我第一步就是执行SHOW ENGINE INNODB STATUS把目光聚焦到LATEST DETECTED DEADLOCK段落。这里能看到死锁发生时两个事务各自的 SQL、持有的锁、等待的锁。很多刚接触的人不知道该看哪一行其实核心就三个信息两个事务分别持有什么锁双方各自在等什么锁哪个事务最终被回滚MySQL 会选代价较小的事务作为牺牲品。那次日志显示事务 1 持有 t_sku 表中 sku_id100 这行的 X 锁正在等待 sku_id200 的 X 锁事务 2 持有 sku_id200 的 X 锁正在等待 sku_id100 的 X 锁。标准的循环等待两个事务互不相让死锁检测机制介入后回滚了事务 2。3.2 结合应用日志定位源头会话死锁日志能看到 InnoDB 内部的状态但往往看不出业务上下文——到底是哪个接口触发的。我当时需要结合应用端的全链路日志和数据库连接时间线把两笔操作的业务入口找出来。排查的顺序是在information_schema.innodb_trx里查当前活跃事务找到 trx_started 时间最早的那几个长事务用 trx_mysql_thread_id 关联SHOW PROCESSLIST拿到对应的连接和正在执行的 SQLMySQL 5.7 里可以用sys.innodb_lock_waits视图直接看谁在等谁8.0 里候选信息在performance_schema.data_locks和data_lock_waits里查更为底层精确去应用日志里搜对应的请求 ID找到完整的调用链路。我强烈建议业务应用给每个请求生成一个 traceId 并写入 MySQL 的注释里比如UPDATE t_sku SET stock stock - 1 WHERE sku_id 100 -- traceId:xxxx。这在排查锁问题时简直是救命的线索几秒钟就能从死锁日志定位到具体的那次用户请求。3.3 死锁发生的深层原因在业务代码定位到具体 SQL 后背后的业务逻辑也浮出水面。这是一个订单支付的预占库存接口代码大致是这样的逻辑// 事务1锁定库存行 UPDATE t_sku SET stock stock - 1 WHERE sku_id 100; // 事务2锁定优惠券行 UPDATE t_coupon SET status 1 WHERE coupon_id 500; // 两个事务后续又去操作对方的行...业务为了减少库存超卖会有按商品维度加锁的逻辑同时为了处理优惠券又有按优惠券维度加锁的逻辑。由于两个接口入口的处理顺序不一致导致事务 1 先锁商品再锁优惠券事务 2 先锁优惠券再锁商品订单量一大就碰上了死锁。这种问题的解法不是去调数据库参数而是从业务侧统一加锁顺序。比如约定所有操作都先锁商品行、再锁优惠券行死锁就会自然消失。如果跨表的事务实在无法统一顺序再考虑异步化、串行化或者把锁粒度从行级调整到更粗的业务维度用 Redis 分布式锁等方式来控制入口的并发。3.4 死锁与锁等待超时的区别死锁和锁等待是两个容易混淆的概念我自己也花了一段时间才完全分清。死锁是循环等待InnoDB 死锁检测机制发现后会主动回滚一个事务让另一个事务继续跑整个过程一般不会让请求卡太久。锁等待则是单方向的等待事务 A 拿着锁不释放事务 B 干等直到超过innodb_lock_wait_timeout默认 50 秒才报错退出。锁等待超时在线上更常见而且往往比死锁更难定位因为日志里光有一条Lock wait timeout exceeded; try restarting transaction根本看不出是谁堵住了谁。解决的抓手是时间点反查在报错时间附近去performance_schema.events_statements_history或者监控系统里找耗时最长的 SQL再找它对应的会话是不是有未提交事务。线上很多疑似死锁的问题最终定位下来其实是某条 SQL 慢查询占着锁不放手后面的请求全都排队撞上了超时阈值。4. 从单机锁到分布式锁MySQL 在分布式方案中的定位再看热搜词里大量出现的分布式锁、Redis 分布式锁这类话题其实是在问一个更深的问题MySQL 的锁能不能解决多服务实例下的并发互斥答案是可以但代价要算清楚。4.1 为什么应用内的 synchronized 不够用Java 的synchronized、ReentrantLock这类本地锁作用域是单个 JVM 进程。现在互联网业务几乎都是多实例部署同一个接口的请求会被负载均衡分发到不同的机器上。A 机器上的线程锁住了B 机器上的线程完全感知不到照样可以并发进入临界区。所以单机锁在分布式场景下等于没锁。4.2 基于 MySQL 实现分布式锁能干什么、有什么坑利用 MySQL 唯一键约束可以做一个最简单的分布式锁CREATE TABLE method_lock ( method_name VARCHAR(64) NOT NULL COMMENT 锁定的方法名/资源名, lock_token VARCHAR(64) NOT NULL COMMENT 持有者令牌释放时校验, expire_time BIGINT NOT NULL COMMENT 过期时间戳用于兜底, PRIMARY KEY (method_name) ); -- 加锁插入成功说明获取到锁插入冲突说明锁被占用 INSERT INTO method_lock(method_name, lock_token, expire_time) VALUES (order:create:123456, token-abc, 1710000000000); -- 释放锁删除对应记录 DELETE FROM method_lock WHERE method_name order:create:123456 AND lock_token token-abc; -- 兜底清理加锁时先删掉已过期的记录 DELETE FROM method_lock WHERE expire_time 1710000000000;这个方案的优点是简单、可靠不走额外中间件很多老项目确实是这么干的。但坑也明显第一锁没有自动续期机制持有锁的线程挂了只能靠 expire_time 兜底过期时间设得太短业务还没干完锁就没了太长则故障恢复慢第二所有请求都打到一个 MySQL 实例上数据库压力大而且 MySQL 本身是单点主从切换时锁数据不一定能同步好第三不可重入需要额外记录持有者信息和重入次数第四释放锁要保证事务性和原子性DELETE 和业务操作之间如果没在一个事务里逻辑就乱套了。总之能用但只适合并发量小、对一致性要求没那么苛刻的内部系统。4.3 Redis 分布式锁与 MySQL 锁的取舍Redis 的实现方式大家比较熟SET lock_key random_token NX PX 30000获取锁时用 SETNX 加过期时间释放锁时用 Lua 脚本校验 token 再删除。这套方案相比 MySQL 锁的优势是性能高、有自然过期机制、Redis 本身的可用性比业务数据库更可靠通常配合集群和哨兵。我个人的选型建议很简单业务库就是 MySQL且并发极低、不想引入 Redis用数据库表锁方案有现成 Redis 基础设施并发中等用 Redis 分布式锁对一致性和可靠性要求极高比如扣款、抢券建议在 Redis 锁之上再加一层数据库唯一约束做兜底双保险。4.4 用锁之前先想想能不能不用锁聊了很多种锁最后我想多说一句分布式系统里最好的锁是没有锁。能用幂等性解决的并发问题就不要引入互斥机制。比如订单创建接口直接以业务订单号作为唯一键重复请求自然会被 MySQL 拒掉库存扣减用UPDATE t_sku SET stock stock - #{count} WHERE sku_id #{id} AND stock #{count}通过影响行数判断是否成功根本不需要先 SELECT 再 UPDATE。先优化业务设计再考虑锁方案顺序不能反。5. 日常要用到的锁监控 SQL 与常用调优参数理论讲清楚了最后落回日常运维。分享几个我实际在用的监控 SQL 和参数经验遇到锁问题不至于抓瞎。5.1 关键时刻必用的查询命令当前有哪些事务在跑、跑了多久SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;查询当前锁等待关系5.7 直接用 sys 库SELECT * FROM sys.innodb_lock_waits;MySQL 8.0 里看具体锁对象SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;这几个命令组合起来基本能回答现在谁在锁谁、锁了哪些行、锁了多久。5.2 参数调优的三板斧先说要怎么查当前值SHOW VARIABLES LIKE innodb_lock_wait_timeout; SHOW VARIABLES LIKE innodb_deadlock_detect; SHOW VARIABLES LIKE autocommit;innodb_lock_wait_timeout默认 50 秒。对于 OLTP 系统我一般建议调到 10 秒以内让锁等待快速失败尽早暴露问题而不是让用户请求挂几十秒才报错。注意这不是让锁更快解除只是改变超时报错的触发时间。innodb_deadlock_detect默认 ON这个开关负责死锁的主动检测。绝大多数场景保持开启就好。只有在高并发、热点行频繁更新、且系统对死锁不太敏感的业务里才有人提议关掉它换innodb_lock_wait_timeout兜底这种情况需要压测数据支撑别拍脑袋就关。autocommit也是个容易踩坑的项。很多数据库连接池的配置把 autocommit 关了业务代码里又忘记在事务结束时提交锁就会一直握着不放。我遇到过最夸张的一次一个连接池里的连接因为异常没回滚持有行锁超过 8 小时整条业务线瘫痪。排查连接池配置、确认事务边界是锁问题治理里最容易忽略但收益最高的一环。还有transaction_isolation建议根据业务场景显式设置而不是依赖默认值。需要间隙锁防幻读就留 RR追求高并发就切 RC但切之前要评估好业务上是否可以接受幻读。5.3 几个琐碎但实用的锁冷知识最后补几个你们可能在其他文章里见不到的点每一件都是我在实践中验证过的。自增锁的变化MySQL 5.7 及之前自增列插入用的是表级 AUTO-INC 锁插入完毕才释放高并发批量插入时是明显的瓶颈。8.0 开始自增锁被改成了轻量级互斥量性能好了很多但如果你还在 5.7 上批量插入的并发表现可能不尽如人意。外键会隐式加共享锁如果表有外键约束对子表插入或更新时InnoDB 会自动对父表对应的行加 S 锁确认外键关联存在。这个锁是隐式的很多 SQL 优化时根本想不到。外键在生产环境建议少用除了性能问题排查锁也要多拐一个弯。INSERT 也会触发锁冲突INSERT在存在唯一键冲突时会对已存在的记录加上 S 锁INSERT ... ON DUPLICATE KEY UPDATE在更新阶段会加 X 锁。两个事务同时对同一唯一键执行这种写入时可能互相等待。这类隐式加锁一般在唯一键冲突率较高的业务里才会暴露比如抢单、批量导入数据。信息缺失时先查 system 表的 memory 引擎如果 online 排查时SYS.INNODB_LOCK_WAITS查不到数据有可能是你的 MySQL 版本比较老性能库不全可以用SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS兜底。版本的坑要及时记录不然排查到一半会怀疑人生。最后说一个我的个人习惯每次发版前我会把核心 SQL 的执行计划拉出来看一眼确认涉及的行锁范围和索引使用情况数据库层面则每天都跑一次锁等待的监控脚本把超过 5 秒的锁等待事件记到日志里。锁的问题不是 DBA 一个人的事而是从建表、索引、事务边界、连接池配置到代码顺序每一个环节都值得多留一份心。这篇内容把 MySQL 锁的几个核心侧面串了一遍希望你们在实际项目里能把锁这个抽象概念落到具体的 SQL 和参数上少走弯路。