三月 4, 2025
摘要：在本教程中，您将学习如何解决 PostgreSQL 中的死锁问题。
目录
在本文中，让我们来看看为什么会发生死锁，更重要的是如何找到锁问题的根源，和阻塞所有其他会话的进程。在找到锁的源头后，将为您提供终止那个最初造成所有麻烦的进程的方法。
查找锁的源头
通常，您不会立即知道自己遇到了锁问题。如果出现问题、查询未返回或应用程序运行缓慢，则找出被锁阻塞的语句是一个很好的起点。
1. 找到正在等待的进程
查看pg_stat_activity视图，找出状态为active但wait_event或wait_event_type为非 NULL 的进程：
SELECT
pid,
datname,
usename,
application_name,
client_addr,
client_port,
to_char(now(), 'YYYY-MM-DD HH24:MI:SS') as now,
to_char(now() - xact_start, 'DD HH24:MI:SS MS') as xact_time,
to_char(now() - query_start, 'DD HH24:MI:SS MS') as query_time,
state,
to_char(now() - state_change, 'DD HH24:MI:SS MS') as state_time,
wait_event,
wait_event_type,
left(query, 40)
FROM
pg_stat_activity
WHERE
state != 'idle'
and pid != pg_backend_pid()
ORDER BY
query_time desc;
如果连接处于活动状态，并且正在等待获取一个锁，则wait_event和wait_event_type列将为非 NULL。如果是这种情况（请确保运行多次查询后还保持这种状态，而不只是捕获到短暂的锁等待），请记录受影响的 PID。这里是一个非常简单的示例，在一个事务中运行了一个 UPDATE 语句，然后在不同的会话中向同一表中添加了一列。在这种情况下，ALTER TABLE在前一个进程的事务提交或回滚之前，不会继续。结果如下，请注意 PID 295998 是 “活动” 的，但是 wait_event = relation 和 wait_event_type = Lock：
pid | datname | usename | application_name | client_addr | client_port | now | xact_time | query_time | state | state_time | wait_event | wait_event_type | left
--------+----------+----------+------------------+----------------+-------------+---------------------+-----------------+-----------------+---------------------+-----------------+------------+-----------------+------------------------------------------
295995 | postgres | postgres | psql | 149.42.105.253 | 49327 | 2025-02-25 20:41:10 | 00 00:02:11 535 | 00 00:02:01 755 | idle in transaction | 00 00:02:01 755 | ClientRead | Client | RELEASE pg_psql_temporary_savepoint
295998 | postgres | postgres | psql | 149.42.105.253 | 49344 | 2025-02-25 20:41:10 | 00 00:01:55 550 | 00 00:01:01 138 | active | 00 00:01:01 138 | relation | Lock | alter table sampledata add column data02
(2 rows)
2. 找到哪个 PID 正在等待锁
现在我们知道 PID (295998) 正在等待一个关系表上的锁，但我们不知道当前哪个进程持有它正在等待的锁。要找到它，我们首先使用在等待的进程 ID 查询pg_locks：
SELECT
*
FROM
pg_locks
WHERE
pid = 295998
AND granted IS NOT true;
这是该查询的结果：
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+---------------------+---------+----------+------------------------------
relation | 5 | 16501 | | | | | | | | 6/6743 | 295998 | AccessExclusiveLock | f | f | 2025-02-25 20:40:08.98843+00
(1 row)
locktype列显示了其他列描述的 Postgres 正在等待的内容。在此示例中，locktype是relation，因此我们查看relation列，以查看关系的 OID（16501），在其上面有一个活动的锁阻塞了进程。
3. 找到持有锁的进程
现在我们知道哪个对象被锁定了，我们可以使用关系 OID 来再次查询pg_locks，以查看当前持有锁的进程：
SELECT
*
FROM
pg_locks
WHERE
relation = 16501
AND granted IS true;
结果如下：
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+------------------+---------+----------+-----------
relation | 5 | 16501 | | | | | | | | 3/243227 | 295995 | RowExclusiveLock | t | f |
(1 row)
这表明 PID 295995 是持有锁的进程。
4. 找出阻塞进程在做什么
现在我们知道哪个进程被授予了锁，我们可以返回到pg_stat_activity来查看该 PID 的进程正在做什么：
SELECT
pid,
state,
wait_event,
wait_event_type,
left (query, 40)
FROM
pg_stat_activity
WHERE
pid = 295995;
结果如下：
pid | state | wait_event | wait_event_type | left
--------+---------------------+------------+-----------------+-------------------------------------
295995 | idle in transaction | ClientRead | Client | RELEASE pg_psql_temporary_savepoint
最后一列显示了该会话执行的最后一个语句，在本例中是一个 UPDATE 后的 保存点释放 语句，但在大多数情况下，它会显示一个活跃事务。
一个锁阻塞了所有会话
一旦你知道了你在寻找什么，上面的语句就非常简单了，但它们也可以组合成一个语句，来进行通用的锁阻塞查询。Postgres wiki 有一些很好的组合版本的这类查询。
很多时候，你可能会发现被阻塞的语句被另一个语句阻塞，然后又被另一个，再被另一个 … 。在这些情况下，仍然可以一直追溯到阻塞所有其他进程的一个 PID，但这可能会是一段艰巨、意想不到的过程。对于这些情况，以下的查询可以帮助您显示出正在等待其他锁的锁，并汇总得到持有初始锁的进程 PID：
WITH sos AS (
SELECT array_cat(array_agg(pid),
array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid), 1)])) pids
FROM pg_locks
WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
a.wait_event_type || ': ' || a.wait_event AS wait_event,
current_timestamp-a.state_change time_in_state,
current_timestamp-a.xact_start time_in_xact,
l.relation::regclass relname,
l.locktype, l.mode, l.page, l.tuple,
pg_blocking_pids(l.pid) blocking_pids,
(pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid), 1)] last_session,
coalesce((pg_blocking_pids(l.pid))[1] || '.' ||
coalesce(case when locktype = 'transactionid' then 1
else array_length(pg_blocking_pids(l.pid), 1) + 1 end,
0),
a.pid || '.0') lock_depth,
a.query
FROM pg_stat_activity a
JOIN sos s on (a.pid = any(s.pids))
LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;
该语句的输出示例：
pid | usename | datname | state | wait_event | time_in_state | time_in_xact | relname | locktype | mode | page | tuple | blocking_pids | last_session | lock_depth | query
--------+-------------+----------+---------------------+---------------------+-----------------+-----------------+------------+---------------+---------------------+------+-------+------------------------+--------------+------------+----------------------------------------------------
879401 | application | postgres | idle in transaction | Client: ClientRead | 00:29:53.512147 | 00:30:01.31748 | | | | | | | | 879401.0 | select * from sampledata where id=101 for update;
880275 | application | postgres | active | Lock: transactionid | 00:01:00.342763 | 00:01:00.459375 | | transactionid | ShareLock | | | {879401} | 879401 | 879401.1 | update sampledata set data = 'abc' where id = 101;
880204 | application | postgres | active | Lock: relation | 00:00:29.722705 | 00:00:29.722707 | sampledata | relation | AccessExclusiveLock | | | {879401,880275,879488} | 879488 | 879401.4 | alter table sampledata add column data03 integer;
880187 | application | postgres | active | Lock: relation | 00:00:03.580716 | 00:00:03.580718 | sampledata | relation | RowExclusiveLock | | | {880204} | 880204 | 880204.2 | update sampledata set data = 'abc' where id = 103;
879527 | application | postgres | active | Lock: relation | 00:00:14.974433 | 00:28:32.80346 | sampledata | relation | RowExclusiveLock | | | {880204} | 880204 | 880204.2 | update sampledata set data = 'abc' where id = 102;
879488 | application | postgres | active | Lock: tuple | 00:00:41.35361 | 00:00:41.47118 | sampledata | tuple | ExclusiveLock | 2 | 21 | {880275} | 880275 | 880275.2 | update sampledata set data = 'def' where id = 101;
(6 rows)
在这个构造的例子中，我们有：
879401 - 状态为 “idle in transaction” 的 PID - 这是一个事务内的一个SELECT... FOR UPDATE语句。它的blocking_pids字段为空，因为它没有被任何其他进程阻塞。这是本示例中阻塞其他所有会话的进程。
880275 - 正在尝试更新id=101的相同行 - 它会被阻塞，直到FOR UPDATE完成。
879488 - 再次尝试更新id=101的相同行 - 在阻塞它的进程完成之前，它无法执行。自从它后来出现以来，它一直在等待 880275。如果 880275 被取消，它只会汇总到下一个阻塞进程 879401。
880204 - 这里添加了一个ALTER TABLE语句 - 因为它需要一个访问独占锁，请注意它的blocking_pids字段显示了所有前面的三个语句，在每一个语句都完成之前，它将无法执行。
879527 - 被ALTER TABLE阻塞，因为它需要一个AccessExclusiveLock。请注意，即使它是不同的行（id=102），它仍然被阻塞。
880187 - 也被ALTER TABLE阻塞。它们是相同的lock_depth，因为它们都被同一语句阻塞，但彼此之间并不阻塞。
结束持有锁的进程
好了，现在我们已经在阻塞树的顶部找到了 PID，那个进程持有了我们其余锁的密钥。幸运的是，在 Postgres 中，我们确实拥有解开锁的多种技巧。
提交
如果语句显示为idle in transaction，则您可能打开了一个以BEGIN语句开头的未提交事务。在这种情况下，你可以提交事务：
COMMIT;
回滚
您可能执行了一些意想不到的更新，或者遇到了错误。在这种情况下，您可以中止事务，回滚掉已进行的任何更改：
ROLLBACK;
取消查询
如果这不是您启动的事务，则在大多数情况下，您可以通过以下方式，取消正在运行的查询：
SELECT pg_cancel_backend(PID);
终止后端连接和进程
如果上面的取消语句不起作用，您可以通过执行终止后端的语句，强制终止后端进程。这将会结束进程及其关联的数据库连接。
SELECT pg_terminate_backend(PID);