1. 从一条慢SQL说起为什么明明有索引查起来还是卡死最近在帮一个老项目排查线上问题现象很典型某张订单表数据量大概八百多万行平时查询都挺正常结果某天下午业务方反馈说后台列表页打不开数据库CPU直接飙到99%紧跟着就是大量锁等待超时的报错。我第一反应是慢查询结果翻到慢日志还真有一条——一条按订单号查详情的SQL执行时间从平时的8毫秒涨到了38秒。更诡异的是这条SQL走了索引执行计划正常单查一条数据无论如何也不该这么慢。后来把SHOW ENGINE INNODB STATUS拉出来一看真相才浮出水面有事务持有某行记录的排他锁一直没提交后面所有想碰这一行的查询全被堵在锁等待队列里。从那次之后我在团队里定了个规矩任何开发同学在写涉及更新的SQL之前必须把MySQL锁机制的基本盘搞清楚。因为说白了90%的数据库性能问题背后都不是SQL写得烂而是锁用得不对——要么锁粒度太大要么锁范围没控制住要么干脆就是事务忘了提交把全表的写操作给堵死。这篇文章我打算把MySQL锁这块掰开揉碎讲清楚。我会从锁的分类体系说起然后重点讲InnoDB的行锁、间隙锁、意向锁到底怎么工作再结合死锁案例和排查工具讲实战经验。内容会比较偏底层但我会尽量用大白话加上真实场景来说明争取让刚接触MySQL的读者也能建立起一个完整认知框架。2. 锁的分类全景图先搞清楚数据库到底在锁什么2.1 从粒度上看表锁、行锁、页面锁各管多大事MySQL的锁按粒度可以分为三类表级锁、行级锁和页面级锁。这个粒度是什么意思你可以把它理解成锁的管控范围——是锁住整张桌子还是只锁桌子上的一只杯子。表级锁是MySQL提供的最粗粒度的锁锁住整个表。它的优点是开销小、加锁快、不会出现死锁但问题也非常突出并发能力极差。在MyISAM存储引擎里表级锁是唯一的选择读锁和写锁会相互阻塞这就导致MyISAM在写多读少的场景下很容易出现严重的性能瓶颈。行级锁则恰好相反锁粒度最小并发能力最强但加锁开销也最大而且可能出现死锁。InnoDB之所以在并发场景下表现远优于MyISAM核心原因就是它支持行级锁。页面级锁介于两者之间开销和并发能力都居中但它并不是MySQL的默认选项在实际使用中比较少见主要出现在某些特定存储引擎或特殊配置场景中。我见过不少初学者有一个误区以为锁粒度越小就一定越好。其实不是的。行级锁虽然并发高但如果一个事务需要更新几万行数据每一行都要加锁那么锁管理的开销会大得惊人这种情况下反而不如表锁干脆。所以在真实业务里批量更新数据时经常会有意识地控制一次更新的行数或者干脆用临时表绕开大事务核心目的之一就是减小锁开销。2.2 按类型分共享锁、排他锁和它们的性格差异从锁的性质上看MySQL的锁分为共享锁Shared Lock简称S锁和排他锁Exclusive Lock简称X锁两大类。这两种锁之间的关系可以直接看命名共享锁就是多个事务可以同时持有同一行记录的读锁大家都在读谁也不挡谁而排他锁则霸道得多一旦某个事务持有了某行记录的X锁其他事务别说是写连读都会被挡住。锁之间的兼容关系用一张表就能看明白锁类型共享锁S锁排他锁X锁共享锁S锁兼容互斥排他锁X锁互斥互斥你可以把这个规则记成一句话只有读读能共存读写写读写写全部互斥。在SQL层面普通的SELECT查询走的是非锁定读也就是快照读它不会主动加S锁。只有显式加上LOCK IN SHARE MODE才会去加S锁而SELECT ... FOR UPDATE以及INSERT、UPDATE、DELETE这些写操作走的都是X锁。这个区别非常重要因为快照读的存在让InnoDB在多版本并发控制MVCC的支持下可以实现读写并行——写不阻塞读读也不阻塞写。这里我想强调一个很多人在面试和实战里都容易搞混的点SELECT是否加锁、加什么锁取决于两种因素一是当前事务的隔离级别二是你是否显式声明了加锁语句。在可重复读隔离级别下普通SELECT走快照读不加锁但在读已提交级别下每条普通SELECT也会生成新的快照而SELECT ... FOR UPDATE则不论在哪个级别下都是当前读必须加X锁。理解快照读和当前读这两个概念是理解锁阻塞行为的前提。2.3 按使用方式分隐式锁与显式锁的区别除了天然的分类MySQL在锁的获取方式上还有一个很重要的维度隐式锁和显式锁。隐式锁是数据库根据你的SQL操作自动加的锁你不需要写任何特殊语法。比如执行一条UPDATE语句InnoDB会为涉及的行自动加上X锁。这类锁是MySQL在底层自己管理的绝大多数情况下我们都不需要去管它但你必须知道它存在——因为一旦事务没提交或没回滚隐式锁就一直握在手里别的会话就只能干等着。显式锁则需要开发者在SQL中明确写出来常见的语法就是前面提到的SELECT ... FOR UPDATE和LOCK IN SHARE MODE。显式锁的使用场景比较固定比如在秒杀场景中要防止多个用户同时扣减同一个商品的库存就需要在扣减前用SELECT ... FOR UPDATE锁住那一行库存记录确保只有一个事务能读到当前值并执行扣减。我从实际项目里总结出一条经验能靠事务隔离级别和MVCC解决的问题就不要去动显式锁。显式锁是把双刃剑用好了能保证数据一致性用不好就是活活把自己锁死。3. InnoDB行锁的核心机制锁的不只是一行数据3.1 记录锁Record Lock最基础的行锁InnoDB的行锁最基本的形态是记录锁Record Lock它是加在索引记录上的锁。这里有个非常重要的概念必须强调InnoDB的行锁是锁在索引上的不是锁在表数据行上的。如果一张表没有显式定义主键InnoDB会隐式生成一个聚簇索引如果表上有二级索引那么针对二级索引的查询也会在二级索引记录上加锁同时回表去聚簇索引上加锁。这个特性带来的实际影响是如果你的SQL查询条件没有走索引那么InnoDB无法精确定位到行记录它就只能从第一条聚簇索引记录开始扫描把扫描过程中遇到的每一行都加上锁。你表面上只想更新一行数据实际上全表扫描期间经过的所有行都被锁住了。这就是实践中最常见的明明只更新一行却把表给锁死的根本原因。所以记住这个结论给高频查询和更新条件建立合适的索引不仅是查询性能的问题更是锁范围的问题。一个设计良好的索引可以直接把锁的粒度从表级缩小到行级。3.2 间隙锁Gap Lock与临键锁Next-Key Lock幻读的防线记录锁锁住的是已经存在的行但如果你的查询条件是范围查询那么问题就来了在可重复读隔离级别下一个事务两次查询同一范围的数据如果第一次查询后其他事务插入了符合条件的新行第二次查询就会多出这些新行这就是幻读。为了杜绝幻读InnoDB引入了间隙锁Gap Lock和临键锁Next-Key Lock。间隙锁锁的是索引记录之间的间隙也就是一个开区间范围它允许其他事务插入新的记录但不允许在间隙锁覆盖的范围里插入满足条件的记录。临键锁则是记录锁和间隙锁的组合它锁的既包括索引记录本身也包括记录之前的那段间隙。我举一个实际例子来说明。假设一张表有一列num数据分别是1、4、7、10你现在执行SELECT * FROM t WHERE num BETWEEN 2 AND 8 FOR UPDATE。InnoDB会把4和7这两条记录之间的间隙以及7和10之间的部分间隙都锁上。这意味着其他事务想插入一个num5的记录会被直接阻塞但插入num11的记录则不受影响。间隙锁有一个很容易被忽略的问题它可能不是在保护任何实际存在的记录而是在保护一个逻辑上的范围。这会导致两个事务互相持有间隙锁却都没有获取记录锁从而形成死锁。我在后面会专门讲这个案例。3.3 意向锁Intention Lock表锁与行锁之间的协调器意向锁是一种很特别的锁它既不是锁表也不是锁行而是锁在表级别上的一个标记。InnoDB设计它的核心目的是为了让表级锁比如LOCK TABLES ... WRITE和行级锁之间能够快速判断兼容性而不用去逐行检查。意向锁分为意向共享锁IS锁和意向排他锁IX锁。当一个事务准备给某行记录加S锁时它会先在表级别加上IS锁准备给某行记录加X锁时则先在表级别加上IX锁。表锁和意向锁之间的兼容关系如下意向共享锁IS与共享锁S兼容意向排他锁IX与意向共享锁IS兼容意向排他锁IX与排他锁X互斥意向共享锁IS与排他锁X互斥简单理解就是意向锁之间互相兼容它们只是告诉别人这个表里有人准备加行锁了但真正判断冲突仍然要看行锁本身。这就像大楼门口的登记簿每个进入的人都会登记一下我要去3楼但你到底能不能进3楼还得看3楼房间的锁开没开。3.4 AUTO-INC锁自增主键的隐性保护还有一个经常被忽略的锁类型是AUTO-INC锁它专门用于保护自增主键的分配。当表上有AUTO_INCREMENT字段并且有INSERT语句执行时InnoDB会获取这个表的AUTO-INC锁保证每次分配的自增值不会出现错乱。在innodb_autoinc_lock_mode参数为0的旧模式下这个锁会持有到事务结束并发插入性能会受影响在参数为1默认值的模式下对于简单的插入语句AUTO-INC锁在SQL执行完就释放在参数为2的交叉模式下自增值的分配完全不依赖锁但要求binlog格式必须是row。这里我建议大家不要轻易去修改这个参数默认的1在绝大多数场景下都够用了而且能在保证自增ID单调不重复的前提下获得较好的并发性能。4. 两个隔离级别下的锁差异为什么默认级别容易出幺蛾子4.1 读已提交RC下的锁行为在读已提交隔离级别下MySQL的锁行为相对克制间隙锁基本不会被使用只有记录锁在起作用。这意味着两个并发事务可以同时在同一个范围上做条件查询只要锁不住具体记录就不会互相阻塞。RC级别下还有一个特点在UPDATE语句执行过程中如果扫描到不符合条件的记录InnoDB会立即释放这些记录上的锁。这样做的好处是减少了锁的持有时间提升了并发能力坏处是无法避免幻读因为间隙没有被锁保护其他事务仍然可以在扫描范围内插入新数据。如果你的业务对幻读不敏感其实RC级别是一个更务实的选择。很多互联网公司干脆把默认隔离级别改成RC不仅并发更好死锁的概率也小不少。这个取舍后面我会详细说。4.2 可重复读RR下的锁行为可重复读是MySQL InnoDB的默认隔离级别也是锁范围最大的一个级别。在这个级别下InnoDB会启动临键锁机制范围查询时不仅锁定实际记录还会锁定记录之间的间隙并且这个锁要一直持有到事务结束。前面已经说过间隙锁正是为了挡住幻读。但也正因为间隙锁RR级别下非常容易出现锁等待和死锁——两个事务各锁了一段间隙谁也插不进去还互相等对方释放结果双双被MySQL的检测机制杀掉。回到我开头讲的那个线上故障当时系统默认就是RR级别业务代码里开启事务后又调用了外部接口事务一直挂着不提交导致间隙锁和行锁越积越多最终把好几张表的关键行全部堵死。后面我们把这几个核心交易表的隔离级别调成了RC这类长事务引发的锁堆积问题明显减少。这里给一个经验结论如果你的业务逻辑本身不需要防止幻读——比如主要是更新、扣减、状态流转这类操作——那么用RC级别比RR级别更稳。需要防幻读的业务比如对账、某些统计类场景再考虑继续留在RR级别。5. 死锁是怎么产生的最小现场还原与排查链路5.1 一个典型的死锁现场还原先看一个我实际遇到过的死锁案例。有两张表账户表A和订单表B。事务1先更新账户表再插入订单表事务2刚好反着来先更新订单表再更新账户表。事务1执行UPDATE account SET ... WHERE id 1;获取了账户表id1这行的X锁事务2执行UPDATE orders SET ... WHERE id 100;获取了订单表id100这行的X锁事务1接着执行INSERT INTO orders ...;需要获取订单表某个范围的插入意向锁但此时事务2已经锁住了相关间隙事务1被阻塞事务2接着执行UPDATE account SET ... WHERE id 1;需要获取账户表id1的X锁但事务1还握着这把锁事务2被阻塞于是两边各等各的形成一个完美的环形等待。InnoDB会在检测到死锁后立即选择一个代价较小的事务进行回滚另一个事务才能继续执行。5.2 死锁排查的三个标准动作遇到死锁不要慌按下面三个步骤排查第一步看死锁日志。MySQL会记录最近一次死锁的详细信息包括涉及的事务、持有的锁、等待的锁、执行过的SQL语句。查看方法是执行SHOW ENGINE INNODB STATUS\G在输出的LATEST DETECTED DEADLOCK段落中可以看到完整现场。第二步定位锁等待源头。执行SELECT * FROM information_schema.INNODB_TRX;查看当前正在运行的事务重点看trx_started字段找出持有时间超长的事务。同时执行SELECT * FROM sys.innodb_lock_waits;可以直接看到谁在等谁。第三步确认SQL和代码逻辑。根据上面查出的SQL回到代码里看事务边界。我最常发现的问题是某个方法里Transactional注解套在了一个包含远程调用的方法上事务迟迟不提交锁自然就迟迟不放。5.3 打破死锁的常用手段死锁的预防比事后处理更重要。我的实操经验可以总结为四点。第一控制事务大小不要在事务里做网络调用或耗时操作。事务越大占用的锁越多形成死锁的概率就越高。第二所有涉及多表更新的操作尽量按照相同的顺序访问表和行。比如约定先更新账户表再更新订单表那么所有业务代码都遵从这个顺序环形等待就失去了形成的前提。第三缩小锁范围尽量用索引去精确定位行避免范围锁升级为表锁。第四如果业务确实难以避免死锁可以在代码里做好死锁重试机制——捕获到死锁异常后重试整个事务。提示innodb_lock_wait_timeout参数控制的是锁等待超时时间默认50秒。很多生产环境会把它调低到3到5秒这样即使出现锁等待也能快速失败并触发应用层重试而不是让用户干等50秒。6. 从锁的角度理解一条UPDATE语句的完整旅程为了把上面的概念串起来我讲一条非常普通的UPDATE语句在InnoDB中到底经历了什么。假设有表user主键是id还有一个二级索引idx_status在status字段上我们执行UPDATE user SET name 张三 WHERE status active AND id 123;InnoDB第一步会先检查id主键索引定位到聚簇索引中的那条记录。如果WHERE条件只使用了二级索引字段比如status那么InnoDB会先去二级索引idx_status上定位符合条件的记录再回表到主键索引取完整行数据。在这个过程中InnoDB需要做的事情是给主键索引上对应记录加X锁同时给二级索引上对应的记录加X锁。然后更新name字段。如果name字段不在这个二级索引中二级索引不需要修改但如果更新的字段恰好是二级索引列那么二级索引的记录也要同步更新同样需要加锁。关键在于这条SQL如果写成WHERE status active而status列上只有普通索引且区分度不高那么InnoDB会扫描到多条记录每条命中的二级索引记录和对应的聚簇索引记录都加X锁同时扫描过程中经过的间隙也会被加上间隙锁或临键锁。这时候即使你只改一行其他事务想往这个status范围里插入任何数据也都会被挡住。这就是我前面反复强调的写SQL时WHERE条件的索引选择直接决定了锁的范围。这也是为什么那些看似只动一行实际锁了一片的线上事故往往都能追溯到一条不走索引的UPDATE或DELETE语句上。7. 排查锁问题的实用工具箱7.1 几条必会的命令锁问题排查和别的性能问题不一样它往往发生在某个瞬间错过就没了。所以你至少要把下面几条命令吃透-- 查看当前所有正在执行的事务 SELECT * FROM information_schema.INNODB_TRX\G; -- 查看锁等待关系 SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看当前持有的锁 SELECT * FROM performance_schema.data_locks; -- 查看完整的InnoDB状态含死锁记录和锁信息 SHOW ENGINE INNODB STATUS\G;在实际排查中我最常用的是INNODB_TRX加INNODB_LOCK_WAITS的组合。INNODB_TRX能看到每个事务的开始时间、当前执行的SQL和等待的锁信息INNODB_LOCK_WAITS则能直接告诉我们谁在等谁形成一条清晰的等待链。7.2 怎么看死锁日志看死锁日志是个技术活我来教大家一个快速入门的方法。执行SHOW ENGINE INNODB STATUS\G后锁定LATEST DETECTED DEADLOCK这段通常长这样*** (1) TRANSACTION: TRANSACTION 24507, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY *** (2) TRANSACTION: TRANSACTION 24508, ACTIVE 10 sec starting index read *** (2) HOLDING THE LOCK: RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY看的时候记住三条要点第一事务1在等哪个锁第二事务2现在正握着哪个锁第三事务1还握着哪个锁不放。如果日志中index PRIMARY出现了说明锁在聚簇索引上如果出现idx_status说明指标在二级索引上。结合SQL内容基本就能判断出是哪两条SQL逻辑形成了交叉等待。7.3 一个调参建议锁相关的参数有几个值得关注innodb_lock_wait_timeout决定锁等待多久后放弃innodb_deadlock_detect控制是否开启死锁检测transaction_isolation决定隔离级别。我建议线上环境把innodb_lock_wait_timeout设置在3到10秒之间太短会误杀正常等待的请求太长会拖垮整体吞吐。innodb_deadlock_detect通常情况下保持默认开启除非你的业务是典型的超高并发且每条事务都很短才考虑关闭死锁检测来节省检测开销。8. 分布式锁和MySQL锁的边界别再混为一谈了最近后台的热搜词里分布式锁出现得非常频繁不少读者会拿着这类问题来问我想搞明白分布式锁和MySQL本身的锁到底是什么关系。这里我统一说清楚。MySQL的内置锁是单机数据库内部的并发控制手段作用范围仅限于一个MySQL实例内部分布式锁则是跨进程、跨节点的互斥机制解决的是多个应用实例操作同一份共享资源时的竞争问题。如果你的服务只部署了一个节点用MySQL行锁或乐观锁就够了一旦服务横向扩展成多个实例同一份数据可能被不同实例并发处理这时候就需要借助Redis、ZooKeeper或者数据库表来实现在分布式环境下的互斥锁。有一种常见的做法是用MySQL自身来实现分布式锁——建一张锁表在表中插入一行唯一记录表示获得锁删除该行释放锁。这种方式实现简单、数据可靠但性能远不如Redis而且如果持有锁的进程挂了行记录可能会一直残留在表里还需要额外的超时清理机制。所以选择哪种锁本质上是在一致性、性能、实现复杂度三者之间做权衡。我在之前的项目中遇到过把MySQL锁当分布式锁用的尴尬服务扩容到三个节点后同一业务操作触发的事务在三台机器上同时执行MySQL行锁确实能保证同一时刻只有一个事务能更新数据但其他两个事务会一直阻塞等待最后大量请求堆积在数据库连接池里反而把数据库本身拖垮了。这就是典型的锁选型错误——单机锁解决不了跨节点的互斥需求跨节点的互斥又必须考虑锁的获取和释放都要有网络开销和超时机制。9. 锁表故障的真实排查记录从现象到根因最后分享一次完整的锁表故障排查过程算是把前面所有知识点综合运用一遍。现象是某天早上业务方反馈后台的订单导出功能点了按钮之后一直转圈紧接着相关报表页面也打不开了。我登录数据库先执行了SHOW PROCESSLIST发现一大批查询状态是Waiting for table metadata lock也就是说这些查询都在等表级元数据锁。注意这里不是行锁等待而是元数据锁等待。元数据锁MDL锁是MySQL在表结构变更时自动加的一个会话执行了ALTER TABLE但一直没提交后面所有访问这张表的SQL都会被阻塞。去查INNODB_TRX果然发现有个事务已经运行了二十多分钟正在执行一条ALTER TABLE user ADD INDEX idx_status(status)但事务一直没有提交。根因很快清楚了开发同学在执行DDL之前手动开启了一个事务然后又执行了其他查询操作把DDL放进了长事务里。在MySQL 8.0之前ALTER TABLE的元数据锁会阻塞DML到事务结束所以一个没提交的长事务直接拖垮了全表访问。处理方式是找到那个事务的会话ID执行KILL 12345强制终止会话。之后我们补充了一条线上规范DDL操作必须在独立会话中执行且执行前确认没有长事务运行如果一定要在线做表结构变更优先考虑使用在线DDL工具并设置合理的锁等待超时时间。这个案例其实很有代表性。很多锁问题的核心原因不是MySQL本身难用而是开发同学对事务边界和锁的生命周期缺乏意识——事务开在哪里锁就跟着活到哪里事务不结束锁就一直在。你写再好的SQL也架不住一个不知道什么时候才会提交的长事务。10. 关于锁的几条实战心得最后聊几条我从实战中攒下来的经验篇幅不长但每一条背后都对应过真实的线上事故。第一索引即锁的边界。凡是更新和删除操作务必确认WHERE条件能走索引。走不上索引的更新就是全表扫描加全表行锁这种SQL在低峰期执行可能没感觉一旦碰到业务高峰就是锁表的定时炸弹。第二事务里不做远程调用。事务应该又快又短所有涉及RPC、HTTP、文件传输的操作一律放到事务外面。一次因为事务里调外部接口导致线上锁死20分钟的教训足够让整个团队记一辈子。第三区分快照读和当前读。普通SELECT不阻塞UPDATE是MVCC的功劳但SELECT ... FOR UPDATE会把读写全部串行化。该用显式锁的地方要用不该用的时候别手欠。第四隔离级别不是越高越好。可重复读是MySQL默认但从锁的维度看它引入的间隙锁会显著放大锁的范围。业务能接受RC的就大胆改RC收益远超损失。第五死锁不可怕可怕的是没有重试机制。任何涉及多行更新、多表更新的业务都应该在代码里捕获死锁异常做有限次数的重试。死锁被MySQL自动检测并回滚之后应用程序只要能正确感知并重试对用户来说就像什么都没发生一样。我在实际工作中发现很多团队的数据库规范里只写了SQL要走索引避免全表扫描却很少讲清楚这背后的锁机制。希望通过这篇文章你能建立起一个相对完整的认知MySQL的锁不是孤立的知识点它和事务隔离级别、索引结构、SQL写法、应用代码的事务边界都紧密相关。真正理解了锁你才能在写SQL时天然地避坑而不是等到线上出了事故才来救火。