# PostgreSQL 数据库 DDL 变更阻塞事故分析报告

### 一、事故概述

- **故障时间**：2026年06月06日 19:51 - 20:34
- **涉及对象**：PostgreSQL 数据库 `TOPMES`，表 `pw_steps`
- **故障现象**：在执行表结构变更（DDL）操作时失败。尽管设置了锁等待超时，语句仍因“Lock Timeout”报错。经排查，当时该表无活跃查询，但 DDL 始终无法获取锁。
- **处理结果**：通过系统视图定位并终止了一个持续 19 天的异常会话后，DDL 立即执行成功，业务恢复正常。

**tip：这次事故里面开启审计日志无法进行排查。**

![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/51ca4a54c293ebb78161f2485fb3e733.png)


### 二、故障根因分析

本次故障的根本原因并非高并发冲突或硬件资源不足，而是一个典型的 **“僵尸事务”导致的元数据锁死**。

1. **直接原因**：进程 ID (PID) 为 `44756` 的数据库会话持有了目标表 `pw_steps` 的 `AccessShareLock`（共享访问锁）。

   

2. **锁冲突机制**：`ALTER TABLE` 操作需要获取最高级别的 `ACCESS EXCLUSIVE LOCK`（排他锁）。在 PostgreSQL 的 MVCC 机制下，即使是只读的 `SELECT` 操作持有的共享锁，也会阻塞后续的表结构变更操作，以保证数据快照的一致性。

3. **隐蔽性特征**：该会话的状态为 `idle in transaction`（空闲但在事务中），且持续时间长达 19 天（始于 2026-05-18）。这意味着它早已完成了 SQL 执行，处于“发呆”状态，因此不会出现在常规的“正在运行 SQL”列表中，导致常规排查手段失效。

4. **来源推测**：根据最后执行的 SQL `SELECT COUNT(*) AS total FROM qa_record WHERE ...` 及连接用户 `sa` 判断，极大概率为开发人员或运维人员在使用 Navicat/DBeaver 等客户端工具进行临时查询时，开启了事务但未提交/回滚，随后关闭了窗口或长时间未操作，导致连接被挂起。

### 三、处置过程与排查脚本

#### 1. 初步尝试与现象确认

首先尝试设置较短的锁等待超时以验证锁的存在，但操作依然失败。

```sql
-- 设置锁等待超时为5秒，避免无限期卡死
SET lock_timeout = '5s';
SET statement_timeout = '10s';

-- 再次尝试加列
ALTER TABLE pw_steps ADD COLUMN abc varchar(255);
-- 结果：错误: 由于锁超时，取消语句操作
```

#### 2. 深入排查：定位阻塞源

由于常规查询看不到活跃 SQL，需通过 `pg_locks` 和 `pg_stat_activity` 关联查询来寻找持有锁的会话。

**排查步骤 A：查找当前所有的锁等待关系**

此 SQL 用于查看谁在等待锁，以及谁持有了锁（注意：如果持有者处于 idle 状态，可能不会显示在 waiting 列表中，但可以通过下面的步骤 B 进一步确认）。

```sql
SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
```

![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/e7ab8123442c098b612d9a8b18a3aaa1.png)


**排查步骤 B：针对特定表的锁详情查询（关键步骤）**

当上述通用查询未能直接给出线索时，直接针对目标表 `pw_steps` 查询所有持有锁的会话，无论其是否处于 active 状态。

```sql
-- 这个语句可以有效找出所有持有 pw_steps 表锁的进程（不管它在干嘛）
SELECT 
    l.pid,
    l.mode,          -- 锁的模式，AccessExclusiveLock 是最高级别
    l.granted,       -- 是否已获取锁
    a.state,         -- 进程状态（可能是 idle in transaction）
    a.query_start,
    now() - a.query_start AS duration,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'pw_steps'::regclass
ORDER BY l.granted DESC, a.query_start ASC;
```

**排查结果：**

通过步骤 B 的查询，发现 PID `44756` 持有 `AccessShareLock`，状态为 `idle in transaction`，且事务已持续 19 天。
![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/4dff6e5d3f8add7b2afec676a71a364b.png)



#### 3. 解决措施

强制终止该异常会话以释放锁资源。

```sql
-- 终止阻塞进程
SELECT pg_terminate_backend(44756);

-- 重新执行 DDL
ALTER TABLE pw_steps ADD COLUMN abc varchar(255);
-- 结果：执行成功
```

### 四、改进建议与预防措施

为防止此类“僵尸事务”再次阻塞核心业务变更，建议实施以下防御性配置：

1. **设置空闲事务超时（强烈推荐）**
   在数据库层面配置 `idle_in_transaction_session_timeout` 参数。一旦事务开启后超过指定时间（如 10 分钟）没有任何操作，数据库将自动终止该会话并回滚事务。

   ```sql
   ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
   SELECT pg_reload_conf();
   ```

2. **规范客户端工具使用习惯**
   提醒开发和运维团队，在使用 Navicat、DBeaver 等 GUI 工具进行查询时，务必注意事务状态。查询结束后应显式点击“提交”或“回滚”，或直接关闭连接，避免长时间挂起事务。

3. **监控告警优化**
   将长事务（超过 30 分钟）纳入监控告警范围，特别是状态为 `idle in transaction` 的连接，以便在影响业务变更前及时发现并处理。


### P-MES系统事务导致

在开启下一个工艺步骤的时候，会生成对应的质量巡检任务，导致这个事务一直挂载在上面，无法结束。
**事故页面**
![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/833ca8d4bac9498462a0cb6d9edf5e27.png)
**事故程序**
![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/72e721ed211180fa9816f11e12d97b36.png)

**Hermes最终事故分析的原因**

![在这里插入图片描述](https://developer.qcloudimg.com/http-save/yehe-8133024/58df2460a3e06b85128ae2451ddb04aa.png)

PostgreSQL 数据库 DDL 变更阻塞事故分析报告 一、事故概述 故障时间：2026年06月06日 19:51 - 20:34 涉及对象：PostgreSQL 数据库 TOPMES，表 pwsteps 故障现象：在执行表结构变更（DDL）操作时失败。尽管设置了锁等待超时，语句仍因“Lock Timeout”报错。经排查，当时该表无活跃查询，但 DDL 始终无法获取锁。 处理结果：通过系统视

【PGSQL】无法修改表结构

运维

PostgreSQL数据库因僵尸事务导致DDL变更阻塞，事故根源为PID 44756会话持表共享锁长达19天，状态为idle in transaction。通过pg_locks与pg_stat_activity关联查询定位并终止异常会话，DDL立即执行成功。建议设置idle_in_transaction_session_timeout参数、规范客户端事务操作，并将长事务纳入监控告警，防范类似元数据锁死风险。

PostgreSQL

数据库

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

PostgreSQL数据库因僵尸事务导致DDL变更阻塞，事故根源为PID 44756会话持表共享锁长达19天，状态为idle in transaction。通过pg_locks与pg_stat_activity关联查询定位并终止异常会话，DDL立即执行成功。建议设置idle_in_transaction_session...

【PGSQL】无法修改表结构-腾讯云开发者社区-腾讯云

【PGSQL】无法修改表结构

【PGSQL】无法修改表结构

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐