线上出过这么一件事一条 UPDATE 的 where 条件没走索引正好赶上业务高峰结果整张表被锁住读流量也跟着堆积最后只能等锁超时慢慢恢复。之后我把 MySQL 锁机制又重新过了一遍。MySQL 锁或者说 InnoDB 的锁机制是后端开发和 DBA 绕不开的核心问题。但“知道有表锁、行锁、间隙锁”和“能看懂一条 SQL 到底锁了哪些范围、为什么锁、卡住之后从哪里查”是两种完全不同状态。这篇文章不把锁概念抄一遍而是按实际开发、排查会遇到顺序来拆先分清锁的维度和作用对象再理解 InnoDB 行锁加锁原理然后演示一条 UPDATE 的加锁过程接着讲锁等待和死锁现场处理最后给出隔离级别影响和线上排查清单。适合正在准备 MySQL 面试的后端开发也适合线上遇到过 Lock wait timeout 却不知道从哪里下手的同学。1. 先搞清楚 MySQL 锁到底在“锁什么”1.1 锁是为了处理并发修改不是为了防止别人读很多人一听到锁就以为锁起来之后谁都读不了这个理解在 InnoDB 里不准确。数据库引入锁核心目的是解决多个事务同时修改同一份数据时产生的相互覆盖问题。没有锁的话两个事务同时对同一行做“余额减 50”和“余额减 30”最后提交谁结果都不一样数据到底是谁的说不清。锁的作用就是让同一时刻只有一个事务能改这条记录其他事务要改必须等它提交或回滚。在 InnoDB 里读和写并不一定是互斥的。普通的 SELECT 走的是快照读不需要加锁也不会被写操作阻塞。真正需要加锁的是 UPDATE、DELETE、INSERT以及手动加的 SELECT ... FOR UPDATE 这类当前读。理解这一点很多“为什么我查询不慢但更新卡住”的问题就少走一半弯路。所以第一个要建立的观念是锁不是把所有访问都挡在外面而是把临界资源的修改串行化。它解决的是写写冲突读读永远不阻塞读写靠 MVCC 来隔离。1.2 从三个维度拆开 MySQL 锁体系MySQL 锁并不只有一种实际面试和排查时最常被提到的是三个维度第一个维度是粒度。按作用范围分为全局锁、表级锁、行级锁。粒度从大到小并发能力从小到大。第二个维度是模式。InnoDB 使用共享锁 S 和排他锁 X。S 锁与 S 锁兼容S 锁与 X 锁不兼容X 锁与任何锁都不兼容。也就是说读读可以并行读和写、写和写不能同时进行。第三个维度是策略。业务代码里常说的乐观锁和悲观锁悲观锁就是直接锁定数据乐观锁通常是版本号或条件更新实现一般不占用数据库锁资源。表格比文字更好记维度分类特点粒度全局锁 / 表锁 / 行锁粒度越小并发越高加锁成本越高模式共享锁 S / 排他锁 XS 与 S 兼容其他组合互相排斥策略乐观锁 / 悲观锁乐观锁适合冲突少场景悲观锁适合冲突多场景1.3 “锁表”和“锁行”到底差在哪平时群里常有人说“表被锁住了”但这句表述包含三种完全不同的情况。第一种是真正加了表级锁。比如 MyISAM 引擎的写锁或者执行 DDL 时拿到的元数据锁 MDL。这种锁影响整张表。第二种是 InnoDB 的行锁范围失控。SQL 没有走索引行锁退化成全表记录锁。表面上像锁表实际是每一行都加了行锁连接一多整个表看起来就是卡死状态。第三种是事务长期未提交持有锁不释放。后面所有涉及这些记录的 UPDATE 都排队等锁慢慢堆积成“锁表”假象。处理方式完全不一样。真表锁要看谁执行了 LOCK TABLE 或 DDL行锁退化要看执行计划和索引长事务要查 innodb_trx 找到事务 id。2. 表级锁和行级锁不能只会背概念2.1 InnoDB 里仍然存在的表级锁场景InnoDB 支持行锁但不代表它不用表锁。有两类场景会让 InnoDB 出现表级锁。第一类是 DDL。ALTER TABLE、DROP TABLE、CREATE TABLE 这些结构调整需要拿到 MDL 元数据锁。MDL 的一个坑是如果有一个长事务一直有读请求MDL 写锁会被阻塞而后续所有访问这张表的读请求都会排队。结果往往是没人执行 DDL表却越来越卡。第二类是备份工具常用的全局锁。FLUSH TABLES WITH READ LOCK会把所有表加上全局只读锁用于一致性备份。生产环境执行要非常小心锁持续时间取决于备份速度一旦慢所有写请求全部被卡住。InnoDB 在自动加锁时还引入意向锁。事务要在某行加 X 锁之前会先在表上加意向排他锁 IX要加 S 锁前先加意向共享锁 IS。意向锁的意义是让另外一个事务快速判断“这张表有没有可能被行锁阻塞”不用遍历每一行。2.2 行锁锁的是索引记录不是物理行InnoDB 的行锁本质上是对索引记录加锁。表上没有索引它也会默认用隐藏的主键索引。所以 InnoDB 行锁另一个叫法是索引记录锁。这意味着一个非常重要但经常被忽略的点你加了索引锁的范围可能更小你没加索引锁的范围可能扩大不知道多少倍。举个例子。一条 UPDATE 通过主键 id 定位记录锁只落在这一条主键索引记录上。如果通过普通索引 name 定位InnoDB 除了锁普通索引记录还会锁回对应的主键索引记录因为最终回表要用主键找到完整数据。范围条件查询时锁定的不是只满足条件的那几行而是扫描过程中遇到的所有索引记录。有人会问为什么要锁主键索引记录因为如果不锁主键另一个事务可能通过主键直接修改同一行两边就冲突了。InnoDB 必须把两条访问路径都堵住。2.3 间隙锁和临键锁是 RR 隔离级别下的特殊锁行锁只能锁住已经存在记录。但 RR 可重复读隔离级别下MySQL 要解决幻读问题也就是同一个事务里两次查询返回结果不一样。只锁已有记录不够因为另一个事务可以在区间里插入一条新记录。间隙锁就是用来锁“记录之间的空隙”的。它锁的是一个范围比如表里 id 有 1、5、10间隙锁可能锁住 (1,5)、(5,10) 这样的区间让其他事务不能在这个区间插入数据。临键锁 Next-Key Lock 是记录锁和间隙锁的组合锁的是左开右闭区间例如 (1,5]。这也是 RR 隔离级别下 InnoDB 默认使用的锁策略。间隙锁带来一个实际代价锁范围变大冲突概率升高死锁更容易出现。如果业务能够接受 RC 隔离级别间隙锁会减少很多。3. 一条 UPDATE 在 InnoDB 里是怎么加锁的3.1 先搭一个最小演示环境概念说多了容易空。下面用一个最小环境演示你手边有 MySQL 8.0 或 5.7 都可以直接跑。CREATE TABLE emp ( id INT PRIMARY KEY, name VARCHAR(50), age INT, dept_id INT, KEY idx_name (name) ) ENGINEInnoDB; INSERT INTO emp VALUES (1, 张三, 25, 101), (2, 李四, 30, 102), (3, 王五, 35, 101), (4, 赵六, 40, 103), (5, 孙七, 45, 102);两个会话模拟并发事务会话 ASTART TRANSACTION; UPDATE emp SET age age 1 WHERE id 1;此时不提交。会话 BSTART TRANSACTION; UPDATE emp SET age age 1 WHERE id 1;会话 B 会进入阻塞直到线程 A 提交或事务被回滚或者等待超过innodb_lock_wait_timeout报出 Lock wait timeout。这个实验想说明一个最基本的加锁事实UPDATE 不是执行完就释放锁而是事务提交或回滚时才释放锁。这里就是“事务提交完后释放锁”的具体体现。3.2 正常加锁流程定位、加锁、提交释放一条 UPDATE 在 InnoDB 里的工作可以大致拆成四步第一步解析 SQL。MySQL 服务层确定要更新的表并结合事务状态决定加锁方式。第二步通过索引定位记录。优化器选择索引执行引擎根据索引找到需要修改的记录。走主键就走主键索引走普通索引就先查普通索引再回表查主键。第三步在扫描过程命中索引记录上加 X 锁。这一步是锁核心。所谓命中不一定只包含最后返回的记录扫描经过的区间都可能被锁住。第四步更新数据后不会立刻释放锁要等COMMIT或ROLLBACK才释放。这也是长事务会拖垮并发系统的根本原因。如果你在代码里手动开了事务调了一个外部接口然后才提交那么整个接口调用期间事务都握着锁不放。接口越慢数据库锁等待越严重。3.3 不同的 where 条件锁定范围差别很大把上面 emp 表换成不同 where 条件锁范围完全不同。第一种主键等值。UPDATE emp SET age age 1 WHERE id 1;RC 和 RR 下都只锁 id1 这一条主键索引记录。并发影响最小。第二种普通索引等值。UPDATE emp SET age age 1 WHERE name 张三;先通过 idx_name 找到 name张三 的索引记录给这条索引记录加锁然后回表到 id1 的主键索引记录再加锁。RR 隔离级别下如果 name 索引不是唯一索引还会在索引两侧加间隙锁防止插入 name张三 的新记录。第三种范围条件。UPDATE emp SET age age 1 WHERE age 30 AND age 40;如果 age 没有索引MySQL 只能全表扫描。扫描过程对每一行都加 X 锁。表面上是行锁实际上是把整张表的记录全部锁住和锁表没区别。这种情况最常见也是“一条慢 SQL 拖垮整个库”的典型原因。第四种索引失效。where 里对索引列做函数运算、隐式类型转换索引走不了也容易造成大范围加锁。判断具体锁范围最稳妥的方式是看执行计划EXPLAIN里 type 是不是 ref、range还是 ALL。ALL 基本意味着全表扫描加锁要当心。4. 锁等待、死锁现象、日志、处理顺序4.1 锁等待和死锁不是一回事锁等待是事务 B 想获取记录 A 的锁但记录 A 被事务 B 持有中事务 B 只能等。这是排队不一定报错。死锁是事务 A 持有记录 1 的锁想要记录 2事务 B 持有记录 2 的锁想要记录 1。两边都不让形成循环等待。InnoDB 检测到死锁后会选择一个事务作为牺牲者回滚它并抛出 Deadlock found 错误。一个需要记住的关键区别锁等待可以等下去设置超时时间到了才报错死锁是 InnoDB 主动回滚。死锁不一定错在牺牲事务本身它是并发路径设计问题。相关参数里innodb_lock_wait_timeout默认 50 秒要看实际版本确认生产环境经常设置成 5 到 10 秒让锁等待快速暴露。innodb_deadlock_detect默认开启用于自动检测死锁。4.2 现场排查要看哪些命令和视图遇到锁问题先不要乱杀连接。第一步是确认当前有哪些事务、锁等待关系是什么。常用命令-- 查看当前所有事务 SELECT * FROM information_schema.innodb_trx\G -- 查看锁等待关系 SELECT * FROM information_schema.innodb_lock_waits;MySQL 8.0 中旧的 innodb_locks 相关视图已经调整可以通过 performance_schema 下的 data_locks 和 data_lock_waits 查看SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;要快速找到谁阻塞谁可以用一条稍复杂一点的关联查询。逻辑不复杂先查出正在等待锁的事务再关联出它等待的锁 ID然后找到持有这个锁的事务对象。注意执行SHOW ENGINE INNODB STATUS可以查看最近一次死锁日志。日志中会包含死锁涉及的事务 SQL、持有的锁、等待的锁。排查死锁时这是第一手资料比猜更有价值。4.3 事务卡住后的规范处理步骤如果确认事务已经卡了很长时间影响业务处理顺序建议如下第一步查innodb_trx.try_id和trx_mysql_thread_id确认是不是业务连接卡死的长事务。第二步根据trx_mysql_thread_id找到原始连接。不要直接对 MySQL 内部线程 ID 乱操作要先确认它是业务端的连接。第三步如果需要中断事务先通过连接方式让业务连接回滚或者找到应用线程释放连接。实在无法联系业务时才考虑用语句杀掉对应会话。第四步事务清理完成后再查锁等待是否消失。如果大量连接仍然堆积可能是应用连接池没有及时释放需要从应用侧处理。这里最容易踩的坑是看到一个事务就 kill结果那是核心业务长事务一杀反而把任务一半数据回滚次要事务全起来了情况更乱。所以处理之前一定要看清事务状态、执行时间、涉及的表和行。4.4 典型死锁形成路径和规避最常见死锁是两个事务按相反顺序更新两条记录事务 AUPDATE emp SET ageage1 WHERE id1; 然后 UPDATE emp SET ageage1 WHERE id2;事务 BUPDATE emp SET ageage1 WHERE id2; 然后 UPDATE emp SET ageage1 WHERE id1;两边各持一把锁又互相要对方手里的锁直接死锁。规避方法也很直接所有事务按照相同顺序访问资源比如先小 id 后大 id。事务尽量短一次只涉及必要记录。高并发批量更新时不要更新范围重叠太大。如果业务允许降低隔离级别或减少间隙锁。把一批大 UPDATE 拆成多条小事务减少锁持有时间。5. 事务隔离级别怎么影响锁的范围5.1 快照读和当前读的加锁差异InnoDB 的读分成两种快照读和当前读。快照读就是普通 SELECT在 MVCC 机制下查询的是某个时间点快照不加锁。所以普通 SELECT 在多数场景不会和其他事务互斥。当前读读取的是最新版本并且要加锁。UPDATE、DELETE、INSERT 天然是当前读。手动写SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE也会触发当前读。如果你只是想读数据不要随手加 FOR UPDATE。很多人为了防并发给所有查询加锁结果把并发读全部串行化完全没有必要。业务上真正需要互斥的是“先查后改”且要求数据不能变化的场景例如库存扣减才考虑用当前读或版本号。5.2 RR 和 RC 锁范围到底差多少MySQL InnoDB 默认隔离级别是 RR REPEATABLE READ。RR 下 InnoDB 需要间隙锁和临键锁来防止幻读。锁范围不只是命中的记录还包括记录附近的间隙。RC READ COMMITTED 隔离级别下InnoDB 只锁命中记录不锁间隙。不过 RC 也放弃了对幻读的处理同一个事务里两次范围查询可能看到新插入数据。这在很多业务里可以接受尤其是互联网高并发读多写多场景。两者对比对比项RR 可重复读RC 读已提交幻读处理通过间隙锁避免可能出现幻读锁范围记录锁 间隙锁只锁记录高并发冲突更高相对低死锁概率更高低一些binlog 要求不限建议使用 ROW 格式5.3 改成 RC 需要满足什么条件如果业务对幻读容忍度比较高希望降低锁冲突可以把隔离级别改成 RC。修改语句SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;但全局修改前要注意几件事。第一确认 binlog 格式。RC 隔离级别下如果把 binlog_format 设置成 STATEMENT主从复制可能因为边读边写顺序不一致导致主从不一致。稳妥做法是把 binlog_format 改成 ROW记录行变更而不是记录 SQL 语句。第二确认业务是否依赖 RR 的可重复读能力。比如同一个事务里要求所有查询都基于同一快照改成 RC 后可能每次读都看到新提交数据业务语义变化很大。第三先在小流量节点测试。不要在核心库直接全局改改完可能死锁少了但业务结果不对更麻烦。6. 线上锁排查清单和优化经验6.1 Lock wait timeout 出现后的分步排查如果业务日志报出Lock wait timeout exceeded; try restarting transaction不要急着重启服务或杀连接按照这个顺序查第一步看报错是锁等待还是死锁。锁等待日志提示 Lock wait timeout死锁日志提示 Deadlock found。第二步查当前事务列表。SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;找到 trx_state 为 RUNNING 且 trx_started 很早的事务大概率是它持有锁不放。第三步查锁等待关系。看哪个事务在等哪把锁哪个事务持有锁。第四步评估这个长事务是否可以中断。如果可以通过连接信息找到业务侧让业务回滚。如果这个事务来自某个已经失联连接业务接口早已超时那么合理清理掉这个会话。第五步清理后不代表结束还要复盘。为什么事务执行那么长是不是代码里事务里加了远程调用是不是一条 SQL 没走索引只有把根因找出来锁等待才不会反复出现。6.2 正常开发中减少锁冲突的几个习惯第一where 条件一定要走索引。这可能是最重要的一条。无索引扫描会把行锁范围放大到全表记录其他事务全部被卡住。写完 SQL 养成看执行计划的习惯。第二事务时间要短。一个事务里不要做外部 HTTP 调用、文件读写、等待队列消息。数据库锁在事务提交前不会释放事务里每多耗一秒后端并发请求就多等一秒。第三批量修改要控制一次处理的数据量。比如根据 id 范围更新一百万行一行一行更新太慢一次十万又容易锁冲突。按照 id 分片每片几千条分批提交可以让锁持有时间短很多。第四通过 FOR UPDATE 手动加锁时锁住的记录越少越好。能锁一条不要锁十条能靠普通条件下推不要先查出来再过滤。第五避免在循环里一条条执行 UPDATE。一个循环一百条 UPDATE每条都单独隐式提交性能差而且释放锁不连贯不如合并成批量条件更新或者用临时表关联更新。6.3 几个容易被误判的锁问题有些问题表面像锁根因根本不是锁。慢 SQL 和锁经常同时出现。一条慢查询执行很久期间大量写请求排队看起来像是锁冲突实际是 SQL 本身性能差导致持有锁时间长。先看慢查询日志再看锁等待顺序别反。长事务没提交也是热门误导源。业务连接池里有一个连接执行了 SELECT 没提交后来开始 UPDATE事务持续半小时。看起来是 UPDATE 卡住其实是同一个连接自己把自己的事务拖住了。Large result set或者临时表操作也可能造成“锁表”假象。InnoDB 可能在临时表或排序过程中用表锁。另外不要把 MySQL 自身锁和分布式锁混在一起。MySQL 锁解决的是同一个 MySQL 实例里多个事务访问同一行数据的互斥问题。如果多个服务实例需要跨进程、跨数据库实例互斥比如多台应用服务器同时抢一个资源那才需要 Redis 或 ZooKeeper 实现分布式锁。两者解决的问题层次不同但面试和设计中经常被放到一起对比。6.4 复盘案例一条 UPDATE 把订单表锁住之前我排查过一个案例业务侧升级数据跑了一条 UPDATE根据 order_id IN (几百个 id) 批量修改订单状态order_id 是主键但 SQL 写法里多了函数转换索引失效结果变成全表扫描加锁。订单表本身量级大又有正常交易写入几分钟内锁等待堆积交易全部超时。处理过程其实不复杂先通过 slow_log 找到这条 UPDATE 的 SQL再用 EXPLAIN 确认 type 是 ALL最后 kill 掉这个会话等堆积事务消化完恢复正常。事后把 SQL 改回原始类型IN 条件带上正确类型索引重新生效问题消失。这个问题值得记住的一点是加锁范围不是由“影响行数”决定的而是由“扫描范围”决定的。哪怕你只更新一行如果扫描时锁住了一万行其他事务也要等这一万行相关记录释放锁。理解这一点才算真正理解 MySQL 锁。