凌晨两点告警群炸了订单接口集体超时日志里刷屏Lock wait timeout exceeded; try restarting transaction。登上去一看SHOW PROCESSLIST里一堆 query 在排队却看不出谁在挡谁。这个场景几乎每个 DBA 和后端都撞过。InnoDB 的锁信息不在慢日志里、也不在业务日志里得去 performance_schema 里翻。今天把线上排查锁等待和死锁的 6 招整理成一份可以直接照抄的清单前 3 招负责找到人后 3 招负责别再犯。先分清两种报错入口不一样报错含义排查入口1205 Lock wait timeout等行锁超过innodb_lock_wait_timeout默认 50 秒只回滚这一条 SQL查锁等待视图找 blocking_pid1213 Deadlock found双向循环等待被 InnoDB 主动检测出来回滚了代价较小的一个事务看SHOW ENGINE INNODB STATUS的 LATEST DETECTED DEADLOCK 段很多人把这两种当成一回事去处理结果翻半天日志找不到死锁记录——1205 根本不产生死锁记录它只是等太久了。第 1 招一眼看谁在等谁这是最常用的一招。有人还在网上抄 5.7 时代的information_schema.INNODB_LOCK_WAITS注意 **MySQL 8.0 已经把这两张表删掉了**5.7 就标了弃用照搬会报 unknown table。8.0 直接用sys.innodb_lock_waitsSELECT waiting_pid, waiting_query,blocking_pid, blocking_query,wait_age_secs,sql_kill_blocking_connectionFROM sys.innodb_lock_waits\G-waiting_pid被卡的连接-blocking_pid挡住别人的元凶-wait_age_secs已经等了多少秒- 最后一列直接把KILL xxx语句替你拼好了一个常见的困惑**blocking_query显示 NULL 很正常**。元凶事务可能正开着但闲着——早先执行完一条 SQL 就没再发语句等着业务下一步。这时去看blocking_trx_started就知道它把事务开了多久。第 2 招从事务反查连接和长事务SHOW PROCESSLIST只能看到当前语句看不到这个连接把事务开了多久。长事务才是锁等待的温床去information_schema.innodb_trx里按开始时间倒着排SELECT trx_id, trx_mysql_thread_id,trx_started,TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS alive_secs,trx_rows_locked, trx_rows_modified,trx_queryFROM information_schema.innodb_trxORDER BY trx_started;trx_rows_locked看锁了多少行alive_secs看事务活了多久。线上常见画像一个连接在事务里查完数据转头去调第三方接口接口超时 30 秒期间它手里的行锁一个都不放后面所有相关更新全部排队。要把事务精确关联到 performance_schema 里的会话用performance_schema.threads的PROCESSLIST_ID就是SHOW PROCESSLIST里的 Id也就是KILL命令用的那个 id。注意别拿内部THREAD_ID去 kill那是另一个体系。第 3 招看懂 LOCK_MODE别被意向锁吓到锁的明细在performance_schema.data_locks里。很多新人第一次查到LOCK_MODE IX、LOCK_TYPE TABLE就慌了以为表被锁死。其实要按LOCK_TYPE分开看LOCK_TYPELOCK_MODE含义会不会挡人TABLEIS / IX意向锁表示有事务打算在这个表上加行锁只和表级锁LOCK TABLES冲突不挡行操作RECORDXNext-Key 锁锁记录 记录前面的间隙RR 默认挡本记录的写也挡往间隙里插入RECORDX,REC_NOT_GAP纯记录锁只锁这一行挡本记录的写RECORDX,GAP纯间隙锁只锁区间不锁记录挡往间隙里插入不挡已存在行的读写判断谁真正挡了谁别靠猜直接看performance_schema.data_lock_waits的等待关系最近一次死锁的完整细节两个事务各持哪把锁、各在等哪把锁、最后回滚了谁在SHOW ENGINE INNODB STATUS\G的 LATEST DETECTED DEADLOCK 段里。锁类型对照第 4 招最高频的成因——没走索引的 UPDATE线上整张表被锁住的案件十有八九是这条-- status 没索引UPDATE orders SET status 2 WHERE status 1;执行计划走全表扫描InnoDB 会给**扫到的每一行**都加锁在 REPEATABLE-READ 下还伴随间隙锁效果上等于把整张表锁了。看起来像锁升级其实没有锁升级这回事就是锁的范围跟着扫描范围走。习惯在建表和改锁问题之前先跑一遍EXPLAIN UPDATE orders SET status 2 WHERE status 1;type ALL、扫描行数接近全表就是它了。补上索引锁范围立刻从全表缩到命中的那几行。这是性价比最高的一个修复没有之一。顺带两个容易踩的坑1. **唯一索引上不存在的行也会被间隙锁挡住**。RR 下SELECT ... FOR UPDATE一条不存在的记录锁的是它前后的间隙别人往这个区间 INSERT 会被卡住。2. **INSERT ... ON DUPLICATE KEY UPDATE和REPLACE INTO并发跑是死锁高发区**撞的是唯一索引的间隙锁和 S 锁典型表现就是偶发 1213单并发复现不了。第 5 招从写法上减少锁冲突找到了人、也修好了索引还要管住代码里的事务写法。四条纪律按优先级排1. **多行更新固定顺序**。所有事务都按主键从小到大更新循环等待天然不存在。这也是消灭死锁最根本的一条。2. **大批量改小批量**。UPDATE ... LIMIT 1000配合循环每批一个事务锁持有时间从分钟级降到毫秒级。3. **事务里不做慢操作**。远程调用、发消息、算大数都挪到事务外。4. **热点行考虑 READ-COMMITTED**。RC 下间隙锁基本关闭外键和唯一键冲突检查除外锁范围明显收窄改之前和业务确认一致性语义。-- 分批更新模板固定顺序 小批量UPDATE orders SET status 2WHERE status 1ORDER BY idLIMIT 1000;-- 影响行数为 0 时结束循环MySQL 8.0 还给了两个开关FOR UPDATE NOWAIT遇到锁直接报错不排队和FOR UPDATE SKIP LOCKED跳过被锁的行。做任务表抢任务的场景SKIP LOCKED比自己造排队逻辑干净得多。反例与推荐写法第 6 招参数与止血的正确姿势三个参数值得提前配好参数默认作用与提醒innodb_lock_wait_timeout50行锁等待超时秒数会话级可改**只对行锁等待生效不影响死锁**innodb_deadlock_detectON超高并发打同一热点行时检测本身会烧 CPU官方允许关掉改靠超时兜底普通业务别关innodb_print_all_deadlocksOFF开启后每次死锁都写 error log线上排查靠它攒证据真要止血时KILL的姿势有个大坑KILL QUERY 12345; -- 只杀当前正在跑的语句事务还开着锁一个不释放KILL CONNECTION 12345; -- 断开连接并回滚事务锁才真正放掉KILL 12345 等价所以优先 kill 阻塞链条最上游的元凶blocking_pid别去杀等待者——杀掉一个等待者它身后排队的照旧堵着。另外元凶如果是改了很多行的大事务回滚本身要跑一阵子锁释放有延迟杀完用第 1 招确认sys.innodb_lock_waits清空了再收工。常见故障速查表现象最可能的原因第一步动作报 1205 Lock wait timeout行锁等太久查sys.innodb_lock_waits找 blocking_pid报 1213 Deadlock found加锁顺序不一致等循环等待看 error log / LATEST DETECTED DEADLOCK大量 query 集体卡住单个长事务持锁不放查innodb_trx按trx_started排序blocking_query为 NULL元凶事务开着但空闲看blocking_trx_started和trx_rows_locked像锁了全表更新条件没走索引EXPLAIN验证补索引偶发 1213难复现唯一键冲突写法ON DUPLICATE KEY/REPLACE固定加锁顺序或改普通INSERT写在最后锁问题的排查链路其实很固定**报错码定性 → 锁等待视图找元凶 → 事务表看持有时间 → LOCK_MODE 看锁粒度 → 索引和写法修根因 → 参数兜底**。前两招背熟90% 的锁告警能在 5 分钟内定责。真正值钱的是第 4、5 招的预防建表时就让更新条件有索引可用写代码时按顺序、小批量、事务里别干慢活。死锁和锁等待不是玄学是写法问题的延迟爆发。排查速查这套排查路径在云贝教育的 MySQL 实战课里是拿真实故障库练的命令怎么敲、结果怎么看对着练一遍比自己摸半天强。
凌晨两点告警群炸了订单接口集体超时日志里刷屏Lock wait timeout exceeded; try restarting transaction。登上去一看SHOW PROCESSLIST里一堆 query 在排队却看不出谁在挡谁。这个场景几乎每个 DBA 和后端都撞过。InnoDB 的锁信息不在慢日志里、也不在业务日志里得去 performance_schema 里翻。今天把线上排查锁等待和死锁的 6 招整理成一份可以直接照抄的清单前 3 招负责找到人后 3 招负责别再犯。先分清两种报错入口不一样报错含义排查入口1205 Lock wait timeout等行锁超过innodb_lock_wait_timeout默认 50 秒只回滚这一条 SQL查锁等待视图找 blocking_pid1213 Deadlock found双向循环等待被 InnoDB 主动检测出来回滚了代价较小的一个事务看SHOW ENGINE INNODB STATUS的 LATEST DETECTED DEADLOCK 段很多人把这两种当成一回事去处理结果翻半天日志找不到死锁记录——1205 根本不产生死锁记录它只是等太久了。第 1 招一眼看谁在等谁这是最常用的一招。有人还在网上抄 5.7 时代的information_schema.INNODB_LOCK_WAITS注意 **MySQL 8.0 已经把这两张表删掉了**5.7 就标了弃用照搬会报 unknown table。8.0 直接用sys.innodb_lock_waitsSELECT waiting_pid, waiting_query,blocking_pid, blocking_query,wait_age_secs,sql_kill_blocking_connectionFROM sys.innodb_lock_waits\G-waiting_pid被卡的连接-blocking_pid挡住别人的元凶-wait_age_secs已经等了多少秒- 最后一列直接把KILL xxx语句替你拼好了一个常见的困惑**blocking_query显示 NULL 很正常**。元凶事务可能正开着但闲着——早先执行完一条 SQL 就没再发语句等着业务下一步。这时去看blocking_trx_started就知道它把事务开了多久。第 2 招从事务反查连接和长事务SHOW PROCESSLIST只能看到当前语句看不到这个连接把事务开了多久。长事务才是锁等待的温床去information_schema.innodb_trx里按开始时间倒着排SELECT trx_id, trx_mysql_thread_id,trx_started,TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS alive_secs,trx_rows_locked, trx_rows_modified,trx_queryFROM information_schema.innodb_trxORDER BY trx_started;trx_rows_locked看锁了多少行alive_secs看事务活了多久。线上常见画像一个连接在事务里查完数据转头去调第三方接口接口超时 30 秒期间它手里的行锁一个都不放后面所有相关更新全部排队。要把事务精确关联到 performance_schema 里的会话用performance_schema.threads的PROCESSLIST_ID就是SHOW PROCESSLIST里的 Id也就是KILL命令用的那个 id。注意别拿内部THREAD_ID去 kill那是另一个体系。第 3 招看懂 LOCK_MODE别被意向锁吓到锁的明细在performance_schema.data_locks里。很多新人第一次查到LOCK_MODE IX、LOCK_TYPE TABLE就慌了以为表被锁死。其实要按LOCK_TYPE分开看LOCK_TYPELOCK_MODE含义会不会挡人TABLEIS / IX意向锁表示有事务打算在这个表上加行锁只和表级锁LOCK TABLES冲突不挡行操作RECORDXNext-Key 锁锁记录 记录前面的间隙RR 默认挡本记录的写也挡往间隙里插入RECORDX,REC_NOT_GAP纯记录锁只锁这一行挡本记录的写RECORDX,GAP纯间隙锁只锁区间不锁记录挡往间隙里插入不挡已存在行的读写判断谁真正挡了谁别靠猜直接看performance_schema.data_lock_waits的等待关系最近一次死锁的完整细节两个事务各持哪把锁、各在等哪把锁、最后回滚了谁在SHOW ENGINE INNODB STATUS\G的 LATEST DETECTED DEADLOCK 段里。锁类型对照第 4 招最高频的成因——没走索引的 UPDATE线上整张表被锁住的案件十有八九是这条-- status 没索引UPDATE orders SET status 2 WHERE status 1;执行计划走全表扫描InnoDB 会给**扫到的每一行**都加锁在 REPEATABLE-READ 下还伴随间隙锁效果上等于把整张表锁了。看起来像锁升级其实没有锁升级这回事就是锁的范围跟着扫描范围走。习惯在建表和改锁问题之前先跑一遍EXPLAIN UPDATE orders SET status 2 WHERE status 1;type ALL、扫描行数接近全表就是它了。补上索引锁范围立刻从全表缩到命中的那几行。这是性价比最高的一个修复没有之一。顺带两个容易踩的坑1. **唯一索引上不存在的行也会被间隙锁挡住**。RR 下SELECT ... FOR UPDATE一条不存在的记录锁的是它前后的间隙别人往这个区间 INSERT 会被卡住。2. **INSERT ... ON DUPLICATE KEY UPDATE和REPLACE INTO并发跑是死锁高发区**撞的是唯一索引的间隙锁和 S 锁典型表现就是偶发 1213单并发复现不了。第 5 招从写法上减少锁冲突找到了人、也修好了索引还要管住代码里的事务写法。四条纪律按优先级排1. **多行更新固定顺序**。所有事务都按主键从小到大更新循环等待天然不存在。这也是消灭死锁最根本的一条。2. **大批量改小批量**。UPDATE ... LIMIT 1000配合循环每批一个事务锁持有时间从分钟级降到毫秒级。3. **事务里不做慢操作**。远程调用、发消息、算大数都挪到事务外。4. **热点行考虑 READ-COMMITTED**。RC 下间隙锁基本关闭外键和唯一键冲突检查除外锁范围明显收窄改之前和业务确认一致性语义。-- 分批更新模板固定顺序 小批量UPDATE orders SET status 2WHERE status 1ORDER BY idLIMIT 1000;-- 影响行数为 0 时结束循环MySQL 8.0 还给了两个开关FOR UPDATE NOWAIT遇到锁直接报错不排队和FOR UPDATE SKIP LOCKED跳过被锁的行。做任务表抢任务的场景SKIP LOCKED比自己造排队逻辑干净得多。反例与推荐写法第 6 招参数与止血的正确姿势三个参数值得提前配好参数默认作用与提醒innodb_lock_wait_timeout50行锁等待超时秒数会话级可改**只对行锁等待生效不影响死锁**innodb_deadlock_detectON超高并发打同一热点行时检测本身会烧 CPU官方允许关掉改靠超时兜底普通业务别关innodb_print_all_deadlocksOFF开启后每次死锁都写 error log线上排查靠它攒证据真要止血时KILL的姿势有个大坑KILL QUERY 12345; -- 只杀当前正在跑的语句事务还开着锁一个不释放KILL CONNECTION 12345; -- 断开连接并回滚事务锁才真正放掉KILL 12345 等价所以优先 kill 阻塞链条最上游的元凶blocking_pid别去杀等待者——杀掉一个等待者它身后排队的照旧堵着。另外元凶如果是改了很多行的大事务回滚本身要跑一阵子锁释放有延迟杀完用第 1 招确认sys.innodb_lock_waits清空了再收工。常见故障速查表现象最可能的原因第一步动作报 1205 Lock wait timeout行锁等太久查sys.innodb_lock_waits找 blocking_pid报 1213 Deadlock found加锁顺序不一致等循环等待看 error log / LATEST DETECTED DEADLOCK大量 query 集体卡住单个长事务持锁不放查innodb_trx按trx_started排序blocking_query为 NULL元凶事务开着但空闲看blocking_trx_started和trx_rows_locked像锁了全表更新条件没走索引EXPLAIN验证补索引偶发 1213难复现唯一键冲突写法ON DUPLICATE KEY/REPLACE固定加锁顺序或改普通INSERT写在最后锁问题的排查链路其实很固定**报错码定性 → 锁等待视图找元凶 → 事务表看持有时间 → LOCK_MODE 看锁粒度 → 索引和写法修根因 → 参数兜底**。前两招背熟90% 的锁告警能在 5 分钟内定责。真正值钱的是第 4、5 招的预防建表时就让更新条件有索引可用写代码时按顺序、小批量、事务里别干慢活。死锁和锁等待不是玄学是写法问题的延迟爆发。排查速查这套排查路径在云贝教育的 MySQL 实战课里是拿真实故障库练的命令怎么敲、结果怎么看对着练一遍比自己摸半天强。
