真正被阻塞的UPDATE是在等锁,需查pg_stat_activity中wait_event_type='Lock'且state='active',并结合pg_locks确认锁信息;应使用pg_terminate_backend强制回滚事务释放锁,而非pg_cancel_backend。

怎么确认UPDATE真被阻塞了,而不是自己慢?
很多人一看到UPDATE没反应就急着kill,结果发现只是数据量大、没索引或者执行计划差。真正被阻塞的UPDATE,本质是在等锁——比如另一事务正持有同一行的XLock(排他锁)且未提交。
查证的关键是看pg_stat_activity里是否出现wait_event_type = 'Lock'且state = 'active',同时wait_event具体为transactionid、relation或tuple。如果wait_event_type是IO或Client,那大概率不是锁问题,别急着终止。
SELECT pid, usename, datname, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' AND wait_event_type = 'Lock';- 重点核对
query字段是否确实是你的UPDATE语句(注意可能被截断,用substr(query, 1, 200)辅助) - 别只看
pid,顺手查下pg_locks确认锁类型和目标对象:SELECT locktype, database, relation::regclass, mode, granted FROM pg_locks WHERE pid = 12345;
该用 pg_cancel_backend 还是 pg_terminate_backend?
pg_cancel_backend(pid) 对 UPDATE 无效——它只发SIGINT信号,仅中止当前语句执行,但事务仍保持打开状态,锁不会释放。而阻塞UPDATE的根源正是未提交事务持有的锁,所以取消查询根本解不了锁。
必须用pg_terminate_backend(pid):它发SIGTERM,强制回滚整个事务,释放所有锁,这才是解除阻塞的正确路径。
- 执行后客户端会收到错误:
FATAL: terminating connection due to administrator command - 返回值为
true只代表信号已发出,不保证进程立刻退出;若返回false,常见原因是PID不存在、权限不足,或你试图杀自己的会话(PostgreSQL禁止) - 超级用户可杀任意非内部进程,但不能杀
backend_type = 'background worker'这类系统进程
批量终止多个阻塞UPDATE的SQL怎么写才安全?
别手写一堆SELECT pg_terminate_backend(123); SELECT pg_terminate_backend(456);——容易漏、难验证、没条件过滤。应该用动态SQL生成带判断的终止语句:
SELECT 'SELECT pg_terminate_backend(' || pid || '); -- ' || usename || '@' || client_addr || ', blocked for ' || (now() - query_start) AS kill_cmd
FROM pg_stat_activity
WHERE state = 'active'
AND wait_event_type = 'Lock'
AND query ILIKE '%UPDATE%'
AND now() - query_start > interval '30 seconds';复制输出结果手动执行前,务必检查三件事:
- 确认
usename和client_addr对应的是预期应用,不是监控脚本或DBA自己的连接 - 排除
application_name含pg_dump、pg_basebackup等关键维护任务 - 避免误杀
state = 'idle in transaction'的会话——它虽不活跃,但持锁更久,应优先处理而非忽略
为什么加了 lock_timeout 还是会被卡住?
SET lock_timeout = '5s'; 确实能让新发起的UPDATE在等锁超时后报错canceling statement due to lock timeout,但它只对“后续语句”生效。已经卡住的UPDATE进程,其锁等待早已开始,这个设置对其无效。
真正起作用的时机是:你在应用代码里,在执行UPDATE前主动设置了lock_timeout,比如Python中用psycopg2.connect(..., options='-c lock_timeout=5s'),或SQL里先SET lock_timeout = '5s';再BEGIN; UPDATE ...;。否则,正在运行的阻塞进程只能靠pg_terminate_backend干预。
另外注意:lock_timeout和statement_timeout互不替代——前者只管“等锁”,后者管“执行总耗时”。一个UPDATE可能很快拿到锁,但因数据量大执行了2分钟,这时lock_timeout完全不起作用。

















