美国服务器上的数据库出现锁等待,如何从事务与慢查询定位

发布人:A5数据发布时间:2026-10-26 10:54阅读量:90

美国服务器上的数据库出现“锁等待”时,慢查询只是候选原因之一,不能看到接口变慢就直接增加索引或扩大连接池。真正需要确认的是:哪个事务正在等待、等待哪个锁、谁持有锁、持锁事务为什么没有及时提交,以及慢查询是否让持锁时间被拉长。

排查应沿着“请求链路—数据库资源—锁关系—事务代码”逐层收敛。美国服务器的位置本身不会直接产生数据库行锁,但应用与数据库跨地域、网络抖动、外部接口调用或连接池排队,可能让一个事务持续更久,最终把普通的慢查询放大成锁等待。定位时应先确认等待类型,再区分网络、系统、数据库服务和应用事务四类原因。

先确认:确实是锁等待,而不是连接池或网络变慢

锁等待、慢查询和连接池等待的区别

一个接口耗时上升,至少可能对应以下几种情况:

先确认:确实是锁等待,而不是连接池或网络变慢配图

现象实际等待位置常见观察指标初步方向
请求还没有拿到数据库连接应用连接池pool acquire time、active、idle、pending连接池配置、连接泄漏、数据库连接数上限
已建立连接,但数据库迟迟未返回数据库执行或锁管理lock wait、query time、wait event事务、索引、资源或锁冲突
建连阶段耗时增加网络或数据库接入层TCP connect、TLS、DNS、丢包、RTT网络路径、跨地域链路、防火墙或接入层
SQL 执行时间稳定变长但没有锁等待扫描、排序、I/O 或 CPU执行计划、Rows examined、磁盘延迟、CPU索引、SQL 写法、资源容量
多个请求互相等待,最终报死锁锁顺序不一致deadlock 日志、锁等待图事务访问顺序、批量更新、隔离级别

因此,应用日志最好把一次请求拆成几个阶段记录,而不是只记录总耗时。下面是一个用于说明判断过程的模拟链路数据,不代表某台美国服务器已经执行过检查:

request_total=2.36s
pool_acquire=1.18s
db_connect=0.02s
sql_execute=1.14s
lock_wait=0.87s
sql_cpu_and_io=0.27s

这组数据至少说明两个问题:请求既存在连接池排队,也存在数据库锁等待。此时单纯增加索引不能解决连接池排队,单纯扩大连接池也可能把更多并发请求推向同一个锁。

先建立时间线

建议把同一时间段的以下信息对齐:

  1. 应用接口耗时、数据库连接获取耗时和 SQL 耗时。
  2. 数据库锁等待开始时间、等待者和阻塞者。
  3. 阻塞事务的开始时间、最后一条 SQL 和提交时间。
  4. CPU、内存、磁盘延迟、网络 RTT 和连接数变化。
  5. 慢查询日志、死锁日志和应用异常日志。

锁等待通常不是孤立事件。一个事务在 10:01:00 开始更新订单,10:01:01 调用外部服务,10:01:04 才提交,期间其他事务已经等待了几秒。若只看第二个事务的慢查询记录,很容易误以为它是根因,实际上它可能只是被前一个长事务阻塞。

先建立时间线配图

建立原因树:从网络、系统、服务到应用

网络层:网络不会制造行锁,但会延长持锁时间

美国服务器上的应用和数据库可能处于同一台机器、同一数据中心,也可能分布在不同地域。网络问题的判断重点不是“服务器在美国”,而是应用到数据库之间的实际链路。

需要观察:

  • 数据库连接建立时间是否突然增加。
  • 应用到数据库的 TCP RTT 是否明显高于平时。
  • 是否存在丢包、重传、连接重置或短时断连。
  • 事务中是否包含跨地域 RPC、对象存储访问、支付接口或 DNS 查询。
  • 数据库连接是否使用长连接,连接空闲后是否被中间设备回收。

普通 SQL 在网络上往返多次时,RTT 会累积;更危险的是,应用在已经执行 BEGIN 后再等待外部服务,数据库事务并不会因为应用在等待网络而自动结束。持锁事务的生命周期因此变长。

可以在业务主机上做低风险的基础检查。以下命令只用于观察,具体参数和工具是否安装取决于操作系统:

date
getent hosts db.example.com
nc -vz -w 3 db.example.com 3306
ping -c 5 db.example.com

ping 只能作为辅助信息,不能证明数据库端口和应用协议正常。生产环境应结合应用连接耗时、TCP 建连耗时和数据库日志判断。若数据库使用 PostgreSQL,端口通常为 5432;若使用 MySQL,端口通常为 3306,实际端口应以部署配置为准。

如果网络 RTT 只升高了几十毫秒,但锁等待从几十毫秒增加到几十秒,网络通常不是唯一根因,应继续查事务边界和阻塞者。若所有请求都在建连阶段变慢,而已有连接执行 SQL 正常,则重点转向连接池、网络或数据库接入层。

数据库锁等待往往与事务持锁时间、主机资源和应用到数据库的网络链路共同相关。A5数据提供美国物理服务器,涵盖常规 Xeon 与 AMD EPYC 等系列,部分配置配备大容量内存和 NVMe 存储,并有 CN2 GIA 线路方案,可为面向美国用户的数据库及业务服务提供不同的计算、存储和网络资源组合。

系统层:资源不足会把持锁事务拖长

数据库进程没有明显锁冲突时,也可能因为系统资源不足而执行变慢;当一个持锁事务受到 CPU、内存或磁盘 I/O 拖慢,其他事务就会表现为锁等待。

常见观察项包括:

  • CPU 使用率和运行队列是否持续升高。
  • iowait 是否明显增加,数据盘延迟是否异常。
  • 是否发生 swap,数据库进程是否被内存回收影响。
  • 磁盘空间、inode、日志盘写入是否接近上限。
  • 数据库进程是否受到容器 CPU 或内存限制。

Linux 上可以使用以下只读命令查看基础状态:

uptime
free -h
vmstat 1 5
iostat -xz 1 5
df -h
df -ih

如果系统没有 iostat,通常需要安装 sysstat,但生产环境安装软件前应遵守主机变更流程。示例中不建议为了排查锁等待直接重启数据库、清理日志或删除临时文件。

判断时要注意因果方向:

  • CPU、I/O 在锁等待出现前就已经升高,资源问题可能是前置原因。
  • 只有锁等待出现后 CPU 升高,可能是大量等待连接、重试或锁竞争造成的结果。
  • 磁盘延迟高且持锁 SQL 正在排序、刷日志或扫描大量数据,慢查询可能正在延长锁持有时间。
  • 系统指标平稳,但单个事务长期占锁,应优先检查事务逻辑,而不是先扩容。

数据库服务层:确认锁类型和等待链

数据库中的“锁等待”并不只有一种。常见类型包括:

  • 行锁或键值锁:多个事务更新同一行或相邻键值。
  • 表级锁:显式锁表、DDL 或某些维护操作影响并发。
  • 元数据锁:修改表结构时等待正在使用该表的事务结束。
  • 事务 ID 或谓词相关等待:常见于特定隔离级别和数据库实现。
  • 咨询锁:应用主动使用的逻辑锁,等待关系不一定对应某一行数据。

先找等待链,再看 SQL,比直接搜索“慢 SQL”更有效。下面分别给出 MySQL 8 和 PostgreSQL 的参考查询。查询本身用于读取状态,字段和权限要求会随数据库版本、配置和发行版变化,执行前应先在测试环境核对。

MySQL 8:从等待者追到阻塞事务

查看 InnoDB 事务

SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

重点关注:

  • trx_state 是否为 LOCK WAIT。
  • trx_started 距当前时间是否过长。
  • trx_query 是否为空但事务仍未提交。
  • 阻塞事务是否处于应用连接的 Sleep 状态。

trx_query 为空不代表事务已经结束。应用执行完一条 SQL 后保持连接空闲,但没有提交或回滚,仍可能继续持有锁。此类连接在应用层通常表现为“空闲”,在事务层却可能是阻塞者。

查看等待者与阻塞者

MySQL 8 可通过 performance_schema 的锁表建立关系:

MySQL 8:从等待者追到阻塞事务配图

SELECT
    rw.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
    rw.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
    r.OBJECT_SCHEMA AS waiting_schema,
    r.OBJECT_NAME AS waiting_table,
    r.INDEX_NAME AS waiting_index,
    r.LOCK_TYPE AS waiting_lock_type,
    r.LOCK_MODE AS waiting_lock_mode,
    b.OBJECT_SCHEMA AS blocking_schema,
    b.OBJECT_NAME AS blocking_table,
    b.INDEX_NAME AS blocking_index,
    b.LOCK_TYPE AS blocking_lock_type,
    b.LOCK_MODE AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS rw
JOIN performance_schema.data_locks AS r
  ON r.ENGINE_LOCK_ID = rw.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE_LOCK_ID = rw.BLOCKING_ENGINE_LOCK_ID;

如果结果显示等待集中在同一张表、同一个索引或相近的锁定数据上,下一步要把锁关系与业务操作对应起来。不要仅根据表名判断是索引问题,还要查看 SQL 的 WHERE 条件、更新范围和事务隔离级别。

对于元数据锁,可进一步查看:

SELECT
    OBJECT_TYPE,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_STATUS,
    OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

执行 DDL 前存在长事务,是元数据锁等待的常见来源。此时即使 DDL 语句本身很短,也可能等待很久;后续访问该表的请求还可能形成排队。

危险处理:不要看到阻塞者就立即杀连接

终止数据库会话可能导致事务回滚、业务请求失败和锁释放后的重试风暴。处理前至少应确认:

  • 阻塞者属于哪个应用、租户或业务操作。
  • 事务是否包含已经对外产生副作用的流程。
  • 应用是否支持幂等重试。
  • 是否已经保存 SQL、事务开始时间、线程 ID 和调用链。
  • 当前是否处于批量写入、结算、迁移或结构变更窗口。

必要时可先终止正在执行的语句,而不是直接断开连接:

KILL QUERY <mysql_thread_id>;

如果确认连接处于异常长事务、无法自行结束,才评估:

KILL CONNECTION <mysql_thread_id>;

上述操作需要相应权限,<mysql_thread_id> 只是占位符,不能直接照抄。执行前应完成业务影响评估和必要的数据备份或快照安排;操作本身没有“撤销”命令,连接终止后的事务通常由数据库回滚,应用则需要按业务规则恢复或重试。不要通过重启数据库代替精准处理,也不要批量终止所有长连接。

PostgreSQL:区分锁事件与空闲事务

查看正在等待锁的会话

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.client_addr,
    a.state,
    a.wait_event_type,
    a.wait_event,
    now() - a.query_start AS query_age,
    now() - a.xact_start AS transaction_age,
    pg_blocking_pids(a.pid) AS blocking_pids,
    a.query
FROM pg_stat_activity AS a
WHERE a.wait_event_type = 'Lock'
ORDER BY a.query_start;

特别关注以下组合:

  • state = 'active' 且 wait_event_type = 'Lock':当前 SQL 正在等待锁。
  • state = 'idle in transaction':事务已开启但会话暂时没有执行 SQL,仍可能持有锁。
  • transaction_age 远大于 query_age:事务可能在多条 SQL 之间停留过久。
  • blocking_pids 不为空:可以继续追踪具体阻塞进程。

可以查看阻塞进程的状态:

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    query
FROM pg_stat_activity
WHERE pid IN (<blocking_pid>);

如果发现阻塞进程处于 idle in transaction,应回到应用代码检查提交、回滚和异常处理路径,而不是只优化被阻塞的 SQL。

取消与终止 PostgreSQL 会话

通常先尝试取消当前 SQL:

SELECT pg_cancel_backend(<pid>);

如果会话仍然持有锁且确认需要结束,再评估:

SELECT pg_terminate_backend(<pid>);

这类操作需要足够权限,可能使事务回滚并让应用请求失败。执行前应保留现场信息,确认连接归属、重试策略和业务影响;它不能替代数据备份,也没有简单的撤销操作。若问题来自应用长期不提交,终止会话只能释放当前锁,不能阻止问题再次发生。

从慢查询判断:它是等待者,还是持锁者

慢查询不一定是锁等待根因

慢查询排查要回答两个不同问题:

  1. 这条 SQL 自己执行很慢,是否在等待别人释放锁?
  2. 这条 SQL 执行时间过长,是否持有锁并阻塞了其他事务?

例如,订单更新语句的慢日志可能显示执行耗时 8 秒,但其中 7 秒在等待锁,真正的 CPU 和磁盘执行只有 1 秒。此时给订单表增加索引未必有效。相反,一条没有合适索引的更新语句扫描大量记录,可能先耗费较长执行时间,再持有或扩大锁影响范围,才是需要优化的持锁者。

检查执行计划和扫描范围

MySQL 可以先查看不执行 SQL 的计划:

EXPLAIN FORMAT=JSON
UPDATE orders
SET status = 'paid'
WHERE user_id = 12345
  AND order_no = 'A10001';

PostgreSQL 的普通 EXPLAIN 不会执行语句:

EXPLAIN
UPDATE orders
SET status = 'paid'
WHERE user_id = 12345
  AND order_no = 'A10001';

EXPLAIN ANALYZE 会真正执行语句。对 UPDATE、DELETE 或包含副作用的 SQL,不应直接在生产环境运行;如果必须采集实际运行数据,应先在测试或只读副本验证,并准备回滚和业务隔离方案。

分析计划时重点看:

  • 是否发生全表扫描或大范围索引扫描。
  • 估算行数与实际行数是否偏差很大。
  • WHERE 和 JOIN 条件是否使用了合适的索引。
  • 是否出现大量排序、临时表、回表或磁盘溢写。
  • 更新条件是否缺少唯一键或选择性较高的过滤条件。
  • SQL 是否因为隐式类型转换、函数包裹列或前导模糊匹配而无法有效使用索引。

索引优化应建立在实际过滤条件上。例如查询经常使用:

WHERE tenant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC

可以评估以 tenant_id、status、created_at 为顺序的组合索引,但最终顺序仍需结合数据分布、查询比例和写入成本验证。索引不是越多越好:每增加一个索引,写入、更新、空间占用和维护成本都会增加。创建索引前应在测试环境检查执行计划,并确认在线 DDL 能力、锁影响和回滚方案;不要在业务高峰直接执行未评估的表结构变更。

慢查询日志和统计视图的边界

MySQL 慢查询日志适合发现超过阈值的语句,但单条日志不一定能完整说明阻塞链。应结合 performance_schema、事务开始时间和应用追踪。

PostgreSQL 可使用 pg_stat_statements 观察 SQL 的调用次数、平均耗时和总耗时,但它是聚合统计,不能代替某一次具体锁等待的现场。统计中总耗时很高,可能是调用次数多,也可能是少数异常请求拖高平均值。

开启慢查询或扩大日志采样会增加磁盘写入和日志量,修改前应确认磁盘空间、保留周期和配置持久化方式。不要把临时会话参数误认为重启后仍然有效,也不要为了捕获一次问题无限制记录所有 SQL 参数。

逐层排除:连接池、缓存和资源指标

连接池过小或连接泄漏

连接池等待经常被误判为数据库锁等待。可以同时查看:

  • pool_acquire_time 是否升高。
  • 活跃连接是否长期等于池上限。
  • 空闲连接是否为零。
  • 数据库端是否有大量会话处于空闲或空闲事务。
  • 应用异常路径是否遗漏关闭、提交或回滚。
  • 连接池超时是否与数据库锁等待时间同时发生。

扩大连接池只能提高并发上限,不能解决同一行被多个事务更新的问题;连接过多还可能增加 CPU、内存、上下文切换和锁竞争。合理做法是先确认数据库可承受的并发,再设置连接池上限、获取超时、空闲回收和事务超时,并为连接泄漏增加告警。

缓存只能减少部分读压力

缓存对热点读请求有效,但通常不能直接消除写事务之间的锁冲突。以下情况反而可能放大数据库压力:

  • 缓存同时失效导致大量请求回源。
  • 热点键没有防击穿控制。
  • 更新数据库后没有正确失效缓存,业务不断重试。
  • 为保证强一致性,读写请求仍必须回数据库确认。
  • 缓存故障时所有流量瞬间落到数据库。

因此,发现锁等待时,仍要检查缓存命中率、失效时间和回源请求量,但不能用“加缓存”替代对阻塞事务和慢 SQL 的定位。

资源指标要和锁时间对齐

建议按时间轴对比以下指标:

指标说明对锁等待的判断价值
锁等待时长和等待连接数直接反映锁冲突范围判断是否为单点阻塞或普遍竞争
最老事务年龄事务从开始到当前的时间发现未提交、异常中断和长事务
CPU 使用率、运行队列数据库计算压力判断 SQL 扫描、排序或重试是否拖慢持锁者
磁盘延迟、I/O 等待数据落盘和读取压力判断日志刷盘、随机读写是否延长事务
活跃连接、连接池等待应用并发压力区分池排队和数据库内部等待
死锁次数、回滚次数冲突是否以失败结束区分普通阻塞与锁顺序问题
慢 SQL p95/p99长尾执行时间找出可能长期持锁的语句

指标需要按同一时间窗口查看。只看当前值容易错过已经结束的阻塞,也无法判断谁先发生。

定位根因:把证据收敛到事务边界

经过前面的排除,根因通常可以归入以下几类:

事务范围过大

典型代码路径是:

定位根因:把证据收敛到事务边界配图

  1. 开启事务。
  2. 更新订单或库存。
  3. 调用外部接口。
  4. 等待接口返回。
  5. 写入日志或附加记录。
  6. 最后提交。

如果外部接口偶发耗时 5 秒,数据库锁也可能被持有 5 秒。更稳妥的方式是缩短事务,只把必须原子完成的数据库操作放在事务内;外部调用使用状态机、消息队列或可重试的异步流程承接,并保证幂等。

批量写入范围过大

一次事务更新数万行,会扩大锁范围、延长日志刷盘时间,也增加死锁概率。可以按业务安全边界分批处理,但分批提交会改变原子性,必须先确认失败后是否允许部分完成,并设计进度记录和补偿机制。

访问顺序不一致

事务 A 先更新库存再更新订单,事务 B 先更新订单再更新库存,双方可能形成死锁。统一表和资源的访问顺序,缩短事务时间,并对死锁异常设计有限次数、带退避的幂等重试。

索引不匹配

缺少索引或索引选择性过低,可能让更新语句扫描更多记录。此时需要结合执行计划和实际数据分布优化索引,而不是只看 SQL 文本。索引变更本身也可能带来表锁、资源消耗或写入性能下降,必须先评估变更方式和回滚方案。

隔离级别或锁定方式不合适

较高隔离级别、SELECT ... FOR UPDATE、显式表锁和范围更新都可能扩大冲突范围。降低隔离级别不是默认修复方案,因为它可能改变一致性语义。应先确认业务是否确实需要当前读、行级锁或范围保护,再在测试环境验证调整后的数据一致性。

验证恢复:不能只看一条 SQL 结束

处理阻塞者或上线索引后,应重新观察完整链路,而不是看到等待数降为零就结束。至少检查:

  1. 原等待者是否恢复,接口错误和超时是否下降。
  2. 是否还有新的阻塞链产生。
  3. 最老事务年龄是否回到业务允许范围。
  4. 慢 SQL 的执行时间和扫描行数是否改善。
  5. 连接池等待是否仍然存在。
  6. CPU、磁盘延迟、日志写入和数据库连接数是否出现新的峰值。
  7. 数据库提交、回滚、死锁和应用重试是否正常。

可以使用一段持续观察窗口验证,例如覆盖几个业务高峰或至少连续观察 15 至 30 分钟。这个时间只是常见验证示例,实际应按业务流量周期决定。若只在低流量时恢复,不能证明高峰期已经解决。

复发监控点:把一次排障变成可提前发现的告警

建议同时建立数据库、应用和业务三类监控:

  • 数据库:锁等待数量和时长、死锁次数、最老事务年龄、空闲事务数量、活跃连接数、慢查询 p95/p99。
  • 应用:连接池获取耗时、连接池使用率、事务持续时间、请求重试次数、数据库超时和取消次数。
  • 系统:CPU、内存、swap、磁盘空间、I/O 延迟、数据库日志盘写入。
  • 业务:订单更新失败、库存扣减冲突、批处理耗时和重复提交数量。

告警阈值应以正常基线和业务 SLA 为依据。可以将“锁等待 p95 持续高于平时基线”“最老事务超过典型事务时长数倍”“连接池等待持续出现”“出现新的死锁”作为触发条件,而不是给所有数据库套用同一个固定秒数。

最终要保留的不是某次 KILL 命令,而是完整证据链:哪个请求发起事务、事务执行了哪些 SQL、锁定了什么对象、被谁阻塞、持锁多久、慢在哪里,以及修复后哪些指标恢复。这样再次遇到美国服务器上的数据库延迟、锁等待或慢查询时,才能快速区分网络抖动、资源瓶颈、连接池排队和事务设计问题。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/5794.html

相关推荐