PHP小码农

Appearance

锁等待与超时 ​

上一节讲了死锁——两个事务互相等待，由数据库自动挑一个杀掉。但线上更常见、更磨人的其实是另一类问题：一个 SQL 卡在锁等待上，既不报错也不返回，一直挂着。如果持有锁的事务是个 idle in transaction 的连接，它可能挂上几分钟甚至几小时，把后面的请求全部拖死。

PostgreSQL 为此提供了一整套超时（Timeout）参数：lock_timeout、statement_timeout、deadlock_timeout、idle_in_transaction_session_timeout、idle_session_timeout、transaction_timeout。它们的名字相似、作用却各不相同，本页把这六个参数一次讲清，并给出线上 DDL 加锁超时的完整实战方案。

前置知识

阅读本节前，建议先了解：页级锁与死锁、表级锁（ACCESS SHARE / EXCLUSIVE）

基础概念 ​

锁等待到底在等什么 ​

当一个语句请求的锁与别人已持有的锁冲突时，它不会立刻失败，而是进入等待队列（Wait Queue）。等待时该后端进程处于「睡眠」状态，pg_stat_activity.wait_event_type 会显示为 Lock：

sql
SELECT pid, wait_event_type, wait_event, state, left(query, 40) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
text
 pid  | wait_event_type |  wait_event   | state  |        query
------+-----------------+---------------+--------+----------------------
 9301 | Lock            | transactionid | active | UPDATE accounts SET ...
 9302 | Lock            | relation      | active | ALTER TABLE orders ...

wait_event 的取值说明了在等哪一类锁：

wait_event在等的对象常见来源
transactionid某个事务 ID 的行锁UPDATE / DELETE / SELECT ... FOR UPDATE 撞上同一行
relation某个表上的表级锁LOCK TABLE、ALTER TABLE、VACUUM FULL
tuple某个具体的元组锁少见，通常是外键检查
extend等待扩展关系文件大量并发插入同一张表
virtualxid等待虚拟事务 ID少见
object / userlock / advisory其他对象锁pg_advisory_lock 等

六个超时参数总览 ​

参数默认值作用超时后行为错误码可设置范围
deadlock_timeout1s等待超过此时长后检查死锁若检测到死锁则中止一个事务40P01超级用户
lock_timeout0（禁用）等待锁的最长时间中止当前语句55P03任意用户
statement_timeout0（禁用）单条语句总执行时间上限中止当前语句57014任意用户
idle_in_transaction_session_timeout0（禁用）事务中空闲多久后终止会话终止整个会话（FATAL）—任意用户
idle_session_timeout0（禁用）不在事务中空闲多久后终止会话终止整个会话（FATAL）—任意用户
transaction_timeout0（禁用）单个事务总时长上限（PG17+）终止整个会话（FATAL）—任意用户

别把三者搞混

lock_timeout 只管等锁的时间；statement_timeout 管整条语句的时间（包含执行、也包含等锁）；deadlock_timeout 不中止任何东西，它只是「什么时候去检查有没有死锁」的阈值。三者可以同时生效，谁先到谁生效。

超时、死锁与长等待三者关系 ​

text
时间轴（一个语句开始等待锁）
│
├─ 阻塞发生
│
├─ 0s ─────────────────────────────────────────────────►
│
├─ lock_timeout 到点 ──► 报 55P03 lock_not_available，本语句失败
│
├─ deadlock_timeout 到点 ──► 检查等待图
│                              ├─ 有环 → 挑一个事务中止，报 40P01 deadlock_detected
│                              └─ 无环 → 继续等待（什么都不发生）
│
├─ statement_timeout 到点 ──► 报 57014 query_canceled，本语句失败
│
└─ 一直等到持有者提交/回滚 ──► 拿到锁，语句继续执行

关键点：deadlock_timeout 不是「死锁的超时时间」，它不会解掉一个「单向长等待」。想给长等待兜底，必须用 lock_timeout 或 statement_timeout。

语法与用法 ​

设置与查看超时参数 ​

text
-- 查看当前会话生效值
SHOW <参数名>;

-- 会话级 / 事务级设置（SET LOCAL 仅当前事务有效，事务结束自动还原）
SET <参数名> = '<值>';
SET LOCAL <参数名> = '<值>';

-- 数据库级 / 角色级（对之后连接该库或该角色登录的会话生效）
ALTER DATABASE <dbname> SET <参数名> = '<值>';
ALTER ROLE <rolename> SET <参数名> = '<值>';

-- 集群级（写入 postgresql.conf，需重载或重启）
ALTER SYSTEM SET <参数名> = '<值>';
SELECT pg_reload_conf();
sql
-- 查看六个参数当前值
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
  'deadlock_timeout', 'lock_timeout', 'statement_timeout',
  'idle_in_transaction_session_timeout', 'idle_session_timeout',
  'transaction_timeout'
)
ORDER BY name;
text
                name                 | setting | unit |  context
-------------------------------------+---------+------+-----------
 deadlock_timeout                    | 1000    | ms   | superuser
 idle_in_transaction_session_timeout | 0       | ms   | user
 idle_session_timeout                | 0       | ms   | user
 lock_timeout                        | 0       | ms   | user
 statement_timeout                   | 0       | ms   | user
 transaction_timeout                 | 0       | ms   | user

context 列很重要

context = 'user' 表示普通用户即可 SET；superuser 表示只有超级用户能改；sighup 表示改配置后 pg_reload_conf() 即可生效；postmaster 表示必须重启。用这一列可以快速判断某个参数该怎么调。

用 SET LOCAL 给单条语句设限 ​

SET LOCAL 是事务作用域的，事务一结束就自动还原。这是给「危险的 DDL」加保险的标准写法：

sql
BEGIN;
-- 只在本事务内限制等锁 3 秒
SET LOCAL lock_timeout = '3s';
-- 整条语句最多跑 10 秒
SET LOCAL statement_timeout = '10s';

ALTER TABLE orders ADD COLUMN remark text;
COMMIT;
text
ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,5) in relation "orders"

超时后事务已中止，此时必须 ROLLBACK（或直接断开），不能继续 COMMIT：

sql
ROLLBACK;

详细说明 ​

等待队列的 FIFO 公平性 ​

PostgreSQL 的锁等待队列是先进先出（FIFO）的。这一点看似细节，实则有重要后果：一个请求不会因为「刚好没和别人冲突」就插队拿到锁。

举个例子：表 T 上，会话 A 持有 ACCESS EXCLUSIVE；会话 B 在等 ACCESS SHARE；此时会话 C 来了，它想拿 ACCESS SHARE——虽然 ACCESS SHARE 之间不冲突，但 C 仍然要排在 B 后面。

sql
-- 会话 B（先到）
BEGIN; LOCK TABLE orders IN ACCESS SHARE MODE;   -- 排队等待中

-- 会话 C（后到）
SELECT count(*) FROM orders;                    -- 同样要排在 B 后面
text
-- 在第三个会话观察排队顺序
SELECT pid, locktype, relation::regclass AS rel, mode, granted
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY pid;
text
 pid  | locktype |  rel   |        mode         | granted
------+----------+--------+---------------------+---------
 9401 | relation | orders | AccessExclusiveLock | t       ← A 持有
 9402 | relation | orders | AccessShareLock     | f       ← B 在等（先到）
 9403 | relation | orders | AccessShareLock     | f       ← C 在等（后到）

排队也会「堆积」

正因为有 FIFO，一个被卡住的 ACCESS EXCLUSIVE 请求（比如 ALTER TABLE）会把后面所有请求都堵住——哪怕它们彼此并不冲突。这就是「DDL 会引发雪崩」的机制：一条 DDL 卡住，后面的查询全排到它后面，连接池迅速耗尽。

lock_timeout：只对等锁生效 ​

lock_timeout 是最精准的超时参数：它只在「尝试获取锁并等待」时计时，一旦拿到锁就停止计时，不会因为语句本身跑得久而杀它。

sql
-- 只限制等锁 1 秒，语句拿到锁后可以继续跑很久
SET lock_timeout = '1s';

UPDATE orders SET status = 'done' WHERE id = 42;
text
ERROR:  canceling statement due to lock timeout

超时对应 SQLSTATE 55P03（lock_not_available），在应用层可以精确识别：

sql
-- 用 PL/pgSQL 的异常处理捕获 lock_timeout（55P03）
DO $$
BEGIN
  SET LOCAL lock_timeout = '500ms';
  UPDATE orders SET status = 'done' WHERE id = 42;
EXCEPTION
  WHEN lock_not_available THEN        -- SQLSTATE 55P03
    RAISE NOTICE '锁等待超时，稍后重试';
END $$;
text
NOTICE:  锁等待超时，稍后重试
DO

单位可以用字符串

lock_timeout 的值可以写成毫秒数（1000）也可以写成带单位的字符串（'1s'、'500ms'）。用字符串可读性更好，推荐使用。

statement_timeout：整条语句的总时限 ​

statement_timeout 管的是从语句到达服务器到执行完成的全过程，包括解析、等锁、执行、以及结果发送前的处理。它不关心时间花在哪，只要超了就直接中止。

sql
SET statement_timeout = '2s';

-- 一个慢查询（含等锁）
SELECT count(*) FROM big_table;
text
ERROR:  canceling statement due to statement timeout

对应 SQLSTATE 57014（query_canceled）——注意这个错误码并不是 statement_timeout 专属的，任何「用户主动取消语句」的行为（包括 Ctrl+C、pg_cancel_backend()）都会返回 57014。

sql
-- 两种超时在同一语句上叠加，谁先到谁生效
SET lock_timeout = '1s';
SET statement_timeout = '5s';

BEGIN;
UPDATE orders SET status = 'x' WHERE id = 42;  -- 若等锁超 1 秒 → 55P03
COMMIT;
text
ERROR:  canceling statement due to lock timeout

statement_timeout 不覆盖事务

statement_timeout 是逐语句计时的。一个事务里有 100 条语句，每条都不能超时，但事务整体仍可能跑很久——它不会因为「事务开了 1 小时」而中止。要限制事务总时长，得用 transaction_timeout（PG17+，见下文）。

deadlock_timeout：死锁检测的触发阈值 ​

deadlock_timeout 是六个参数里唯一不负责「中止」的。它的含义是：「一个会话等锁超过这么久还没等到，就去做一次死锁检查」。

sql
SHOW deadlock_timeout;

-- 默认 1s；调大能减少无谓检测开销，调小能更快发现死锁
ALTER SYSTEM SET deadlock_timeout = '2s';
SELECT pg_reload_conf();
text
 deadlock_timeout
------------------
 1s

为什么默认要等 1 秒才检查？因为检测本身有成本：需要获取锁管理器的内部锁、遍历等待图。而绝大多数锁等待都会在毫秒级内解决，如果每次都检测，纯属浪费。

sql
-- 配合 log_lock_waits，让所有超过 deadlock_timeout 的等待都留痕
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

SELECT name, setting FROM pg_settings WHERE name = 'log_lock_waits';
text
     name       | setting
----------------+---------
 log_lock_waits | on

调大 deadlock_timeout 的副作用

如果把它调得过大（比如 10 秒），死锁被发现的延迟就会变长——期间两个事务一直互相等着，占用连接、持有锁、放大阻塞面。除非有明确的性能理由，不建议把它调离默认值太远。

idle_in_transaction_session_timeout：事务中空闲 ​

这是最先应该在生产环境打开的参数。很多「锁等待」事故的根因就是：应用开了事务、拿了锁，然后去做别的事（调用下游 HTTP、读文件、等待用户输入），事务一直不提交。

idle_in_transaction_session_timeout 会在会话处于事务中但空闲（idle in transaction）超过指定时长后，直接终止整个会话（返回 FATAL，不是普通的 ERROR）：

sql
-- 把「事务中空闲」限制在 30 秒
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '30s';

-- 或者在会话级设置
SET idle_in_transaction_session_timeout = '30s';

-- 观察当前有多少会话卡在 idle in transaction
SELECT pid,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_age,
       left(query, 40) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;
text
 pid  |     xact_age     |   idle_age   |             last_query
------+------------------+--------------+-------------------------------------
 9501 | 00:04:12.331     | 00:04:05.109 | UPDATE accounts SET balance = ...

-- 超时后，该会话在日志中会看到：
FATAL:  terminating connection due to idle-in-transaction timeout

终止的是会话，不是语句

被 idle_in_transaction_session_timeout 杀掉的是整个连接，客户端会收到连接断开。应用必须有重连机制，否则会直接报错。这也是为什么该参数往往要配合连接池的重试策略一起上线。

idle_session_timeout：不在事务中的空闲连接 ​

idle_session_timeout（PostgreSQL 14+ 引入）管的是另一类空闲：会话不在事务中、也没有在跑语句，纯粹挂在那里不动。

sql
-- 空闲连接（非事务中）超过 10 分钟就断开
SET idle_session_timeout = '10min';

-- 区分两类空闲
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;
text
         state          | count
------------------------+-------
 active                 |     3
 idle                   |    47   ← idle_session_timeout 管这些
 idle in transaction    |     2   ← idle_in_transaction_session_timeout 管这些

-- 超时后的日志：
FATAL:  terminating connection due to idle-session timeout

两者配合使用，可以把「僵尸连接」清理得比较干净：

ini
# postgresql.conf 推荐基线
idle_in_transaction_session_timeout = '30s'    # 事务中空闲 30 秒就断
idle_session_timeout = '10min'                 # 普通空闲连接 10 分钟就断

连接池存在时的取舍

如果用了 PgBouncer / 应用连接池，池里通常会保留一批空闲连接。此时 idle_session_timeout 要和池的 idle_timeout / max_lifetime 协调，避免池以为是「可用连接」而数据库已经把它断开（会导致「连接被服务器关闭」类报错）。

transaction_timeout：事务总时长（PostgreSQL 17+） ​

前面几个参数都只覆盖事务生命周期的一部分：statement_timeout 管单条语句，idle_in_transaction_session_timeout 管空闲。但一个持续执行的长事务（不断有语句在跑）却没有任何参数能拦住。

PostgreSQL 17 引入了 transaction_timeout，专门解决这个问题：它限制一个事务从开始到结束的总时长，超时后终止整个会话。

sql
-- 单个事务最多存活 5 分钟
SET transaction_timeout = '5min';
BEGIN;
SELECT pg_sleep(1);   -- 事务开始计时
-- ... 反复执行语句，只要总时长超 5 分钟就会被终止
COMMIT;
text
FATAL:  terminating connection due to transaction timeout
参数覆盖的场景
statement_timeout单条语句太长
idle_in_transaction_session_timeout事务开着但不动
transaction_timeout（PG17+）事务一直在动，但总时长太长
sql
-- 完整基线：三层时间保护一起上
SET statement_timeout = '30s';                        -- 单语句
SET idle_in_transaction_session_timeout = '60s';      -- 事务内空闲
SET transaction_timeout = '10min';                    -- 事务总时长

版本兼容性

transaction_timeout 是 PostgreSQL 17 新增的参数。在 16 及更早版本上执行 SET transaction_timeout = ... 会直接报错「unrecognized configuration parameter」。写迁移脚本或跨版本部署时要先判断版本。

参数作用域与优先级 ​

同一个参数可以在多个层级设置，生效顺序（从低到高）：

text
内建默认值
   ↓
postgresql.conf / ALTER SYSTEM
   ↓
ALTER DATABASE ... SET
   ↓
ALTER ROLE ... SET  （或 ALTER ROLE ... IN DATABASE ... SET）
   ↓
会话启动时的命令行参数 / 连接字符串参数
   ↓
SET（会话级）
   ↓
SET LOCAL（事务级，最高）
sql
-- 验证优先级：数据库级设 5s，会话级改 2s，事务级再覆盖为 500ms
ALTER DATABASE appdb SET lock_timeout = '5s';

SHOW lock_timeout;              -- 5s（继承自数据库级）
SET lock_timeout = '2s';
SHOW lock_timeout;              -- 2s（会话级覆盖）
BEGIN;
SET LOCAL lock_timeout = '500ms';
SHOW lock_timeout;              -- 500ms（事务级最高）
COMMIT;
SHOW lock_timeout;              -- 2s（SET LOCAL 已随事务结束还原）
text
 lock_timeout
--------------
 500ms
COMMIT
 lock_timeout
--------------
 2s

还可以直接查看某个来源到底设了什么，以及某个参数当前值的最终来源：

sql
-- 数据库级/角色级的参数覆盖
SELECT datname, unnest(setconfig) AS setting
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

-- 参数当前值的最终来源（database / role / session / default）
SELECT name, setting, source
FROM pg_settings
WHERE name IN ('lock_timeout', 'statement_timeout');
text
 datname |          setting
---------+----------------------------
 appdb   | lock_timeout=5s
         | statement_timeout=30s

      name       | setting |   source
-----------------+---------+------------
 lock_timeout    | 2000    | session
 statement_timeout| 30000  | database

排查「为什么参数没生效」

pg_settings.source 会直接告诉你当前值的最终来源，比逐层猜测要快得多；sourcefile 还能指出配置来自哪个文件。

超时错误的捕获与重试 ​

lock_timeout（55P03）与 statement_timeout（57014）的区别在应用层非常重要，因为它们的重试策略完全不同：

错误码含义是否适合立即重试
55P03lock_not_available（lock_timeout 触发）适合——冲突通常短暂，退避后重试往往成功
57014query_canceled（statement_timeout / 手动取消）要谨慎——说明语句本身可能就慢，盲目重试只会重复慢
40P01deadlock_detected适合——立即重试，但需幂等
40001serialization_failure（SERIALIZABLE 冲突）适合——立即重试，但需幂等
sql
-- 在 PL/pgSQL 里对 lock_timeout 做有界重试
DO $$
DECLARE
  attempts int := 0;
BEGIN
  LOOP
    attempts := attempts + 1;
    BEGIN
      SET LOCAL lock_timeout = '500ms';
      UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock >= 1;
      EXIT;                                   -- 成功，跳出循环
    EXCEPTION
      WHEN lock_not_available THEN            -- 55P03
        IF attempts >= 3 THEN
          RAISE;                              -- 重试 3 次仍失败就抛出
        END IF;
        PERFORM pg_sleep(0.1 * attempts);     -- 退避
    END;
  END LOOP;
  RAISE NOTICE '第 % 次尝试成功', attempts;
END $$;
text
NOTICE:  第 2 次尝试成功
DO

一个更实用的「锁等待重排」技巧：把 lock_timeout 设得很短（如 100ms），失败就退避重试。这样能避免长时间排队，把「等锁」转化为「快速失败 + 重试」：

sql
SET lock_timeout = '100ms';
UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock >= 1;
text
ERROR:  canceling statement due to lock timeout

重试必须幂等

重试的前提是操作幂等（Idempotent）。像 UPDATE ... SET n = n + 1 这类语句，重试时必须确认「上次是否已经提交」——如果盲目重试，可能把 +1 做了两次。稳妥做法是用条件更新（带上期望值）或唯一约束保证幂等。

用 pg_blocking_pids 与 pg_locks.granted 定位阻塞者 ​

超时发生后，最关键的问题是「到底是谁在阻塞我」。三个工具配合使用：

sql
-- 工具 1：pg_blocking_pids() 一步拿到阻塞者
SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       now() - query_start   AS waited,
       left(query, 50)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
text
 pid  | blocked_by |    waited    |               query
------+------------+--------------+-----------------------------------
 9601 | {9602}     | 00:00:42.117 | ALTER TABLE orders ADD COLUMN ...
sql
-- 工具 2：pg_locks.granted 精确区分「持有」与「等待」
SELECT pid, locktype, relation::regclass AS rel, mode, granted, waitstart
FROM pg_locks
WHERE NOT granted
ORDER BY waitstart;
text
 pid  | locktype |  rel   |        mode         | granted |          waitstart
------+----------+--------+---------------------+---------+----------------------------
 9601 | relation | orders | AccessExclusiveLock | f       | 2026-09-28 10:31:02.114+08
sql
-- 工具 3：顺着阻塞者往上查它是什么状态
SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(9601)));
text
 pid  |        state         |    xact_age    |          query
------+----------------------+----------------+--------------------------
 9602 | idle in transaction  | 00:03:41.882   | SELECT * FROM orders ...

结论一目了然：9601 的 ALTER TABLE 等了 42 秒，阻塞者是 9602，而 9602 处于 idle in transaction 状态已经 3 分 41 秒——典型的「应用开事务后去干别的」事故。

pg_cancel_backend vs pg_terminate_backend

pg_cancel_backend(pid) 只是取消当前语句（对应 57014），事务仍在；pg_terminate_backend(pid) 会杀掉整个连接。面对 idle in transaction 的阻塞者，只有 pg_terminate_backend() 有用，因为 cancel 一个空闲会话没有任何效果。

实战示例 ​

下面模拟一次典型的「线上 DDL 被长事务阻塞」事故，并给出加了超时保护的完整解决方案。

第 1 步：准备数据与环境 ​

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer   text   NOT NULL,
  amount     numeric(10,2) NOT NULL,
  status     text   NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer, amount, status)
SELECT 'cust-' || g, (random() * 1000)::numeric(10,2),
       CASE WHEN g % 3 = 0 THEN 'done' ELSE 'pending' END
FROM generate_series(1, 200000) AS g;

ANALYZE orders;
text
CREATE TABLE
INSERT 0 200000
ANALYZE

第 2 步：会话 A 开一个长事务并持有表锁 ​

sql
-- 会话 A：故意不提交，保持在事务中
BEGIN;
SELECT count(*) FROM orders WHERE status = 'pending';   -- 持 ACCESS SHARE 表锁
text
 count
--------
  133333
(1 row)

第 3 步：会话 B 执行 DDL，观察无限等待 ​

sql
-- 会话 B（不加任何超时，会一直等下去）
ALTER TABLE orders ADD COLUMN remark text;

在第三个会话里观察阻塞情况：

sql
SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       wait_event_type,
       wait_event,
       now() - query_start AS waited,
       left(query, 40) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
text
 pid  | blocked_by | wait_event_type | wait_event |    waited    |          query
------+------------+-----------------+------------+--------------+-------------------------
 9710 | {9709}     | Lock            | relation   | 00:02:18.440 | ALTER TABLE orders A...

ALTER TABLE 已等了 2 分 18 秒，而且它会一直等下去。

第 4 步：用 SET LOCAL 给它加超时保护 ​

先把会话 B 中断（Ctrl+C 或 pg_cancel_backend），然后换成带超时的写法：

sql
-- 会话 B（改进版）
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN remark text;
COMMIT;
text
ERROR:  canceling statement due to lock timeout

3 秒后立刻失败，不再无限期占用连接。会话 B 收到错误后回滚（ROLLBACK），接着排查阻塞者：

sql
-- 查看会话 A 的状态：如果它是空闲事务，就必须人为清理
SELECT pid, state, now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE pid = 9709;

-- 确认是空闲事务后终止它
SELECT pg_terminate_backend(9709);
text
 pid  |        state        |   xact_age
------+---------------------+---------------
 9709 | idle in transaction | 00:05:41.227

 pg_terminate_backend
----------------------
 t

第 5 步：清理阻塞者并重试 DDL ​

会话 B 重试 DDL，这次成功：

sql
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN remark text;
COMMIT;
text
ALTER TABLE
COMMIT

第 6 步：把防线固化到数据库级配置 ​

一次性治标之后，治本要把基线参数落地，避免同类事故复发：

sql
-- 给业务库设置超时基线
ALTER DATABASE appdb SET lock_timeout = '5s';
ALTER DATABASE appdb SET statement_timeout = '30s';
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '60s';
ALTER DATABASE appdb SET idle_session_timeout = '10min';
text
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ini
# postgresql.conf：集群级兜底
lock_timeout = '5s'
statement_timeout = '30s'
idle_in_transaction_session_timeout = '60s'
idle_session_timeout = '10min'
log_lock_waits = on
deadlock_timeout = '1s'

第 7 步：建立超时监控 ​

sql
-- 一个可直接放进巡检脚本的「锁等待与超时体检」查询
SELECT
  count(*) FILTER (WHERE wait_event_type = 'Lock')                       AS lock_waiters,
  count(*) FILTER (WHERE wait_event_type = 'Lock'
                     AND now() - query_start > interval '5 seconds')     AS long_lock_waiters,
  count(*) FILTER (WHERE state = 'idle in transaction'
                     AND now() - state_change > interval '1 minute')     AS stuck_idle_xacts,
  count(*) FILTER (WHERE now() - xact_start > interval '10 minutes')     AS long_xacts
FROM pg_stat_activity
WHERE backend_type = 'client backend';
text
 lock_waiters | long_lock_waiters | stuck_idle_xacts | long_xacts
--------------+-------------------+------------------+------------
            2 |                 0 |                1 |          1

注意事项 ​

错误！以为 deadlock_timeout 能解掉长锁等待。

sql
-- 错误认知：调小 deadlock_timeout 就能让卡住的语句早点失败
SET deadlock_timeout = '100ms';
UPDATE orders SET status = 'x' WHERE id = 42;
-- 事实：若没有等待环，它会一直等下去，deadlock_timeout 不起任何中止作用

-- 正确做法：要「早点失败」必须用 lock_timeout
SET lock_timeout = '100ms';
UPDATE orders SET status = 'x' WHERE id = 42;

错误！在生产环境全局设置极短的 statement_timeout，却不考虑长报表。

ini
# 错误！全局 1 秒会让月末报表、批量任务全部失败
statement_timeout = '1s'

# 正确做法：给一个宽松的全局基线，再让特殊业务按需放宽
statement_timeout = '30s'
sql
-- 报表/批处理会话自行放宽
SET statement_timeout = '30min';

错误！给 DDL 加了 lock_timeout 却不处理失败后的状态。

sql
-- 错误！DDL 超时后事务已中止，直接 COMMIT 实际会回滚，且容易被忽视
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN remark text;   -- 超时失败（55P03）
COMMIT;
-- 正确做法：捕获错误后显式 ROLLBACK，再由应用决定是否重试

错误！在连接池里给每条语句都设 lock_timeout，但池本身持有长事务。 超时保护的是服务端等锁的语句，如果连接池自己开着事务不放（例如「先 BEGIN，再调用外部服务，再提交」的写法），任何服务端参数都救不了——问题的根在应用代码。

不推荐：直接把 idle_in_transaction_session_timeout 设为极小值（如 5s）。 部分 ORM 会在事务内做多次往返（先 SELECT 校验、再 INSERT），如果两次往返之间因为网络抖动超过 5 秒，连接就会被服务器断开，表现为随机的「连接已关闭」错误，非常难排查。建议从 30~60 秒起步。

最佳实践 ​

  • 生产环境必开 idle_in_transaction_session_timeout，因为它是「锁等待事故」最大的根因。
sql
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '60s';
  • 线上 DDL 一律套用 SET LOCAL lock_timeout，并把「失败就重试」写进发布脚本。
sql
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN remark text;
COMMIT;
  • 开启 log_lock_waits，让所有超过 deadlock_timeout 的等待在日志里可回溯。
ini
log_lock_waits = on
  • 用 pg_settings.source 排查参数为何没生效，它直接给出当前值的最终来源，比逐层猜测高效得多。
sql
SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'lock_timeout';
  • 区分对待 55P03 与 57014：前者退避重试，后者先优化语句。
text
55P03 lock_not_available     → 短暂冲突，退避重试
57014 query_canceled         → 语句本身慢/被取消，先分析再决定
40P01 deadlock_detected      → 立即重试（需幂等）
40001 serialization_failure  → 立即重试（需幂等）
  • 不要用「设短超时」代替「修代码」。超时是安全网，不是解决方案；根因通常是长事务、更新顺序不一致或缺少索引。
sql
-- 先看根因：有多少长事务出自应用层而不该存在
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age, left(query, 40) AS query
FROM pg_stat_activity
WHERE now() - xact_start > interval '5 minutes';
  • 给不同业务配置不同基线，不要「一刀切」：OLTP 连接池要短超时快速失败，报表批处理则要宽松。
sql
-- OLTP：短超时，快速失败
ALTER ROLE app_oltp SET statement_timeout = '10s';
ALTER ROLE app_oltp SET lock_timeout = '1s';
-- 报表：宽松超时
ALTER ROLE app_report SET statement_timeout = '15min';
ALTER ROLE app_report SET lock_timeout = '30s';

下一节

继续学习：锁冲突矩阵

参考链接 ​