数据库没死,业务卡住了:行锁等待、元数据锁、长事务的排查路径

3 阅读15分钟

十五年数据库相关经验,做过 DBA、架构师、技术顾问。不求"颠覆",只求"靠谱"。


周日晚上九点,业务在群里说"提交不了,一直在转圈"。

我登上服务器看监控:CPU 30%,连接数正常,磁盘不忙,复制延迟 0,没有死锁告警。数据库看上去很健康。

这就是最难查的那类故障——没有报错,也没有告警,就是卡。

死锁反而好办,错误日志里有一条 deadlock detected,两个事务谁等谁写得明明白白。锁等待不一样,它不报错,只是等。等到业务方的耐心耗尽,才以"系统卡了"的形式传到 DBA 耳朵里。

这篇讲三种"不报错的堵":行锁等待、元数据锁、长事务阻塞。四段:怎么分清是哪一种、怎么取证、怎么处置、怎么治本。


01 先分清四五种"卡",别一上来就查锁

"系统卡了"是个症状,不是一个问题。同样是卡,排查方向差得很远。

现象关键特征大概率是
某几行数据的操作卡住,其他正常CPU 不高,连接正常,特定 SQL 超时行锁等待
某张表的所有操作都卡,包括查询一条 DDL 正在执行或排队元数据锁
越来越多会话在等,慢查询数暴涨有个会话开了事务不提交长事务阻塞
连接数打满,新请求连不上应用侧报"获取连接超时"连接池耗尽
所有操作都慢,但没有等待执行计划退化、IO 打满、统计信息过期慢查询堆积

这三类堵的共性是:卡的不是数据库,是会话。 某个会话持有着什么东西,另一个会话在等它。

所以排查的第一动作不是看 CPU、看内存,是回答一个问题:谁在等谁,最源头的那个在干什么。

我见过太多人一开始就怀疑硬件,把机器查了一遍,两小时过去,根因是一个三小时前忘了提交的事务。


02 取证:三条 SQL 找到"谁在等谁"

第一步:列出所有在等的会话

PostgreSQL 系:

SELECT pid,
       usename,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start  AS xact_age,     -- 事务开了多久
       now() - query_start AS query_age,    -- 这条语句跑了多久
       pg_blocking_pids(pid) AS blockers,   -- 谁在挡着它
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_age DESC;

这张结果里,wait_event_type = 'Lock' 的就是在等锁。blockers 那列如果有 pid,就说明它被那个会话挡住了。

MySQL:

-- 谁在等谁(8.0 用 sys 库,直观)
SELECT wait_started, wait_age_secs,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

-- 当前所有事务,重点看开了多久、锁了多少行
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, now()) AS trx_age_sec,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

第二步:找到根阻塞者

关键点:你要找的不是报错的那个会话,是没被任何人挡住、却在挡住别人的那个。

-- 根阻塞者:它挡着别人,但没人挡它
SELECT a.pid, a.usename, a.state,
       now() - a.xact_start AS xact_age,
       left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
  AND EXISTS (
        SELECT 1 FROM pg_stat_activity b
        WHERE a.pid = ANY(pg_blocking_pids(b.pid))
      )
ORDER BY xact_age DESC;

这条查询我在事故现场敲过很多次,命中的往往是一个 state = 'idle in transaction' 的会话——它什么都不干,就坐在那里持有锁。

第三步:看它持有什么锁

SELECT l.pid, l.locktype, l.mode, l.granted,
       l.relation::regclass AS object,
       l.page, l.tuple,
       a.state, now() - a.xact_start AS xact_age
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
   OR l.pid IN (
        SELECT pid FROM pg_locks WHERE NOT granted
      )
ORDER BY l.pid;

locktype 是 tuple 或 transactionid,就是行锁;是 relation 且 mode 为 AccessExclusiveLock,就是元数据锁——两种堵,处置方式完全不同。

踩坑提醒:取证的时候不要急着杀。先看清链子有多长、根是谁、根的事务多大。杀错一次,等于把现场销毁了,问题还会重来。


03 元数据锁:一条 DDL 让整张表排队

这是最容易被误判成"数据库挂了"的一种。

它是怎么发生的

改表结构需要拿到表的排他锁(PostgreSQL 里是 AccessExclusiveLock,MySQL 里是 MDL 排他锁)。而这个锁有一个要命的性质:它会排队,并且排队时会把后面的请求一起挡住。

时间线大概是这样:

10:02:11  一条 ALTER TABLE 开始执行,等锁
10:02:12  表上的一个长查询还在跑(持有共享锁)
10:02:13  新来的 SELECT 要拿共享锁 —— 但队列里有个排他的在等,只能排在它后面
10:02:14  更多 SELECT 排队
10:02:45  业务开始超时,报"系统卡住"

注意:真正慢的是那个长查询,真正堵住所有人的是那条等锁的 DDL。 而 DDL 本身看起来"卡在 0%",因为它一直在等。

怎么确认

-- PostgreSQL:找出在等 AccessExclusiveLock 的会话
SELECT a.pid, a.query, now() - a.query_start AS wait_age
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE l.locktype = 'relation'
  AND l.mode = 'AccessExclusiveLock'
  AND NOT l.granted;
-- MySQL 8.0:在等的元数据锁
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
       OWNER_THREAD_ID, OWNER_EVENT_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

怎么处置

三步,顺序不能反:

  1. 先停掉触发 DDL 的那条语句(取消它,不是杀库)。队列里的排他锁一撤,后面的查询立刻放行。
  2. 再处理那个真正的长事务。它是元凶,不处理,下次 DDL 还会堵。
  3. 最后重新执行 DDL,这次带上锁超时。
-- 让 DDL 等不到锁就快速失败,而不是无限排队拖死全表
SET lock_timeout = '3s';
ALTER TABLE t_order ADD COLUMN remark VARCHAR(200);
-- 超时报错就退避重试,别硬等

MySQL 侧对应的做法是用在线改表工具(pt-online-schema-change、gh-ost),或者用 ALGORITHM=INPLACE 的变更方式,避免长时间持有排他元数据锁。

踩坑提醒:DDL 等待期间,最坏的选择是重启数据库。重启能解一时,但长事务的根因还在,下一次 DDL 照样堵。而且重启会丢掉现场,下次排查从头开始。


04 长事务:锁等待的真正源头

排掉元数据锁之后,剩下的锁等待里,八成能追到一个开了很久不提交的事务。

长事务从哪来

我整理过的来源清单,按出现频率排:

来源特征为什么难发现
应用漏提交连接一直 idle in transaction代码不报错,事务一直挂着
连接池配置借出的连接做了查询没 commit/rollback池子看着"正常",实际全被占着
批处理单事务一个事务处理几十万行业务方认为"跑批是正常的"
报表/导出大查询跑十几分钟只读查询也会持有锁
人工运维手工 BEGIN 后忘了提交只有当事人知道,而当事人下班了
备份/迁移工具长时间持有一致性视图工具日志里没写"我锁了什么"

怎么找

-- 最危险的状态:事务开着,但会话什么都不干
SELECT pid, usename, state,
       now() - state_change AS idle_sec,
       now() - xact_start  AS xact_age,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY idle_sec DESC;

MySQL:

SELECT trx_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, now()) AS trx_age_sec,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, now()) > 60
ORDER BY trx_started;

怎么治

三道防线,从松到紧:

第一道,给"事务开着不干活"设上限。

-- 会话级(推荐写进应用的连接初始化语句)
SET idle_in_transaction_session_timeout = '60s';
SET statement_timeout = '30s';

-- 或者角色级,一次配好所有应用连接
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

idle_in_transaction_session_timeout 这个参数,我认为是运维必备。它会自动断开"开着事务不干活"的会话,等于把最难查的那类根阻塞者从源头掐掉。

第二道,把大事务拆小。 批处理按 5000 到 10000 行一批提交,别一个事务干到底。批次大小看两个约束:回滚段/undo 压力,以及业务对"部分成功"的容忍度。

第三道,只读业务用只读账号和只读节点。 报表、导出、BI 查询不该跑在主库上,也不该和交易事务抢同一批行。

踩坑提醒:杀大事务之前先看它改了多少行。一个大事务的回滚时间可能比它执行的时间还长,而且回滚期间照样持有锁。杀之前评估代价,比杀完再后悔强。


05 行锁等待:热点行、无效索引、更新顺序

排掉前两种,剩下的是真正的行锁竞争。

三种典型形态

形态一:更新没走索引,锁的范围远超预期。

PostgreSQL 里,更新语句锁住的是它实际修改的那些行。但如果条件没走索引,全表扫描期间语句执行时间会拉长,锁的持有时间跟着拉长;命中行数一多,锁的范围也大。

MySQL 的 InnoDB 更要注意:可重复读隔离级别下,没有合适索引的更新会锁住扫描过程中触碰到的记录,还会加间隙锁。一条本来只想改一行的语句,可能锁住整张表。

-- 先验证走不走索引,再看扫描行数
EXPLAIN UPDATE t_account SET balance = balance - 100 WHERE user_no = 'U20260118';

type 是 ALL、rows 是几十万,那就是它了。

形态二:并发更新顺序不一致。

两个事务都更新 A、B 两行,一个按 A→B 顺序,一个按 B→A 顺序,就互等了。这类问题在死锁日志里能看到,但如果两边的事务都很慢,表现出来是"长时间等待",而不是立刻死锁报错。

治法是固定顺序:批量更新时按主键排序再执行。

形态三:热点行串行化。

账户余额、库存、计数器、全局配置——这类行被高并发更新时,等待队列会越排越长。每个事务只改一行,但同一行同一时刻只能有一个写者。

这时候加索引没用,要改的是模型:

手段做法代价
拆桶余额拆成 10 个分桶行,读时求和查询变复杂,需汇总
排队化写入进队列,单线程串行处理延迟上升,但吞吐稳
乐观锁版本号 + 重试冲突高时重试风暴
异步化只记流水,余额异步汇总强一致场景不适用

一个反直觉的点

SQL 优化有时会加剧锁竞争。一条慢 SQL 改成快 SQL,执行时间从 2 秒降到 20 毫秒,同样的并发下,单位时间打到同一行上的更新次数翻了 100 倍——等待队列反而更长。

所以锁问题的解法不只是"让 SQL 更快",还要问一句:这一行的并发写,业务上真的需要这么密吗?

踩坑提醒:行锁等待超时后,应用侧的重试如果没加退避和幂等,会把一次拥堵变成雪崩。重试要有随机退避,写操作要幂等。


处置顺序:先救火,再治本

事故现场的处置,我按这个顺序走:

步骤动作目的
1找根阻塞者(不是最早报错的)别在症状上花时间
2评估根的代价:事务年龄、改了多少行决定是杀还是等它提交
3取消/终止根事务,或联系业务方尽快提交撤掉堵塞源
4给新请求设 lock_timeout,让它快速失败保护性熔断,别让队列无限增长
5恢复后核对数据一致性被杀的事务可能有部分写入需回滚
6事后:加索引、拆事务、调节奏、改顺序治本

两个常见错误:

错误一:杀等待者。 等待者杀了,根还在,队列照样堵,而且你失去了"谁在等"的证据。

错误二:一次杀一批。 看到等待链上二十个会话,一口气全杀掉,根事务因为"不在报错列表里"被留下。

踩坑提醒:尽量用取消(cancel)而不是终止(terminate)。取消只中断当前语句,事务还能回滚干净;终止会断开连接,应用侧看到的是连接异常,重连风暴可能带来二次冲击。


四种堵,一张表对照

维度行锁等待元数据锁长事务阻塞连接池耗尽
业务表现特定操作卡整表操作卡越来越多操作卡新请求连不上
关键指标锁等待时长、等待链排队中的 DDL事务年龄、idle in transaction 时长连接数使用率、获取连接耗时
典型根因热点行、无索引更新、顺序不一致DDL 等长查询释放漏提交、大事务、报表连接泄漏、池子配小
首选处置缩短事务、加索引、拆热点取消 DDL、处理长事务终止根事务、设超时查泄漏、调池参数
验证方式等待链消失、P99 恢复表上无 PENDING 锁最长事务年龄归零连接数回落、慢查询减少

决策框架:按影响面定动作

影响面先查什么处置优先级
单条 SQL 慢执行计划、统计信息低,正常优化
单表全堵元数据锁、长事务高,立即取消 DDL
整库连接打满idle in transaction、连接池紧急,先熔断再排查
伴随复制延迟上升大事务、批量写入高,大事务会拖住整个复制链路
反复发生同一张表业务代码的事务边界中,改代码而不是改参数

判断"要不要立刻动手"的标准很简单:有没有正在扩大的等待队列。 队列在长,就动手;队列稳定,就可以先取证再决定。


深度分析:锁是业务并发语义的显式化

把上面几种情况抽出来看,会发现一个共同点:锁等待不是数据库的问题,是业务动作太长、太挤的结果。

一个事务开三分钟,意味着这三分钟里它碰过的行,别人都改不了。数据库只是把这个事实变得可见。你想让它不可见,唯一办法是把事务缩短,而不是把锁去掉。

这解释了为什么很多"锁优化"最后落在业务侧:

  • 批处理拆批,是减少单次事务的行数
  • 热点行拆桶,是减少同一行的并发写
  • 固定更新顺序,是消除循环等待
  • 幂等加退避的重试,是把拥堵摊平

数据库侧能做的,主要是三件事:让锁的持有时间可见(监控事务年龄和锁等待 P99)、让等待有上限(lock_timeout、statement_timeout、idle_in_transaction_session_timeout)、让根因可追溯(把锁等待事件记进慢日志或审计)。

我要特别强调第二件事。没有超时的等待是无限的等待,而无限等待会把一个小故障放大成全站故障——队列越长,恢复越慢,因为放行的瞬间所有请求同时冲进来。

设置超时的思路要改一下:它不是"限制业务",是给系统一个快速失败的机会。失败的应用可以重试,卡死的应用只会拖垮别人。

最后说监控。绝大多数团队监控的是"有没有死锁",这个指标几乎没用——死锁会自动回滚,影响面很小。真正该盯的是两个数:锁等待的 P99 时长,和当前最长事务的年龄。这两个数一抬头,事故就在酝酿了。


检查清单

日常监控

  • 锁等待 P99 时长有阈值告警(而不只是死锁告警)
  • 最长事务年龄有告警,超过 60 秒就提醒
  • idle in transaction 会话数有监控
  • 连接数使用率、获取连接耗时上盘
  • 元数据锁 PENDING 状态有监控(MySQL 8.0 可直接查)

参数与规范

  • idle_in_transaction_session_timeout 已设置
  • statement_timeout / lock_timeout 按业务分级设置
  • DDL 统一带 lock_timeout + 重试,或走在线改表工具
  • 批处理按批提交,单事务行数有上限
  • 报表、导出走只读节点或只读账号

事故处置

  • 有"找根阻塞者"的固定 SQL,写在 runbook 里
  • 明确"取消优先于终止"的处置原则
  • 保留现场:处置前导出等待链和事务信息
  • 处置后核对数据一致性(被杀事务的回滚结果)

治本整改

  • 热点行有拆桶或排队化方案
  • 批量更新按主键排序,消除循环等待
  • 应用侧重试有随机退避 + 幂等
  • 同类问题复盘进了开发规范(事务边界怎么写)

最后

这三类堵,我处理过的次数比死锁多得多。原因也简单:死锁是两个事务撞上了,属于概率事件;长事务和热点行是设计问题,天天都在发生。

判断一个团队的数据库运维水平,我会看两件事:出事时能不能在十分钟内说出"谁在等谁",以及平时有没有盯"最长事务年龄"这个指标。这两件做到了,锁问题就变成常规运维,而不是半夜的灾难。

下一篇聊事务日志和 PITR——WAL 归档怎么配、误删数据之后怎么回到某个时间点。这类事故一年遇到一两次,但每次都能要命。

有问题评论区见。


十五年数据库领域老炮。关注我,一起把数据库这件事搞明白。