ReaderMX.
开源软件联盟PostgreSQL分会

一个PID锁定全局：在Postgres中找到锁的来源

开开源软件联盟PostgreSQL分会约 11 分钟

译者：王志斌

翻译自：

https://www.crunchydata.com/blog/one-pid-to-lock-them-all-finding-the-source-of-the-lock-in-postgres

在Crunchy Bridge的客户成功工程团队中，我们不时会遇到客户在其Postgres数据库上出现锁问题的情况。锁可能会对查询产生级联影响。如果一个进程锁定了一个表，那么一个查询可能会等待前一个进程，前一个进程又可能等待前一个进程，依此类推。重大的锁问题很快就可以导致整个生产环境中的Postgres实例或应用程序崩溃。
在本文中，让我们看看为什么会发生锁，并且更重要的是如何彻底解决锁问题以及导致所有其他进程阻塞的那个进程。那个阻塞一切的进程！一旦找到锁的来源，我将向您介绍终止最初引发所有麻烦的进程的选项。

找到锁的源头

通常情况下，您可能不会立即意识到存在锁问题。如果出现了异常情况，查询未返回结果，或者您的应用程序运行缓慢，那么查找被锁定的语句就是一个很好的起点。

1. 1. 找到正在等待的进程

通常情况下，您可能不会立即意识到存在锁问题。如果出现了异常情况，查询未返回结果，或者您的应用程序运行缓慢，那么查找被锁定的语句就是一个很好的起点。

  1. SELECT
  2.   pid,
  3.   datname,
  4.   usename,
  5.   application_name,
  6.   client_addr,
  7.   client_port,
  8.   to_char (now (),'YYYY-MM-DD HH24:MI:SS')as now,
  9.   to_char (now ()- xact_start,'DD HH24:MI:SS MS')as xact_time,
  10.   to_char (now ()- query_start,'DD HH24:MI:SS MS')as query_time,
  11.   state,
  12.   to_char (now ()- state_change,'DD HH24:MI:SS MS')as state_time,
  13.   wait_event,
  14.   wait_event_type,
  15.   left (query,40)
  16. FROM
  17.   pg_stat_activity
  18. WHERE
  19.   state !='idle'
  20. and pid != pg_backend_pid ()
  21. ORDER BY
  22.   query_time desc;

如果一个连接处于活动状态并等待锁，则wait_event和wait_event_type列将是不为NULL的。如果是这种情况（并且在多次运行查询后仍然是这样，以确保您没有只捕获到短暂的锁等待），记录受影响的PID。
以下是一个非常简单的示例，我在一个事务中运行了一个更新操作，然后在另一个会话中为相同的表添加了一个列。在这种情况下，ALTER TABLE不会继续进行，直到前一个线程的事务提交或回滚。以下是结果 - 请注意PID为295998的进程，它是“活动的”，但wait_event=relation且 wait_event_type=Lock。

  1.   pid   | datname  | usename  | application_name |   client_addr   | client_port |         now         |    xact_time    |   query_time    |        state        |   state_time    | wait_event | wait_event_type |                   left
  2. --------+----------+----------+------------------+-----------------+-------------+---------------------+-----------------+-----------------+---------------------+-----------------+------------+-----------------+------------------------------------------
  3. 295995| postgres | postgres | psql             |149.42.105.253|49327|2023-11-0920:41:10|0000:02:11535|0000:02:01755| idle in transaction |0000:02:01755|ClientRead|Client| RELEASE pg_psql_temporary_savepoint
  4. 295998| postgres | postgres | psql             |149.42.105.253|49344|2023-11-0920:41:10|0000:01:55550|0000:01:01138| active              |0000:01:01138| relation   |Lock| alter table sampledata add column data02
  5. (2 rows)

2. 找到锁定表的那个PID

现在我们知道PID（295998）正在等待关系（表）上的锁，但我们不知道当前持有它正在等待锁的进程是什么。为了找到它，我们首先通过使用等待进程的ID查询pg_locks视图：

  1. SELECT
  2. *
  3. FROM
  4.   pg_locks
  5. WHERE
  6.   pid =295998
  7.   AND granted IS NOT true;

Here’s the result of that query:

  1. locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |        mode         | granted | fastpath |          waitstart
  2. ----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+---------------------+---------+----------+------------------------------
  3.  relation |5|16501||||||||6/6743|295998|AccessExclusiveLock| f       | f        |2023-11-0920:40:08.98843+00
  4. (1 row)

locktype 列显示PostgreSQL正在等待的内容的其他列。在这个示例中，locktype是relation，因此我们查看relation列，以查看阻塞进程在哪个关系（OID 为16501）上持有活动锁。

3. 找到持有现有锁的进程

现在我们知道哪个对象被锁定了，我们可以再次使用关系OID查询pg_locks视图，以查看是什么持有了当前的锁：

  1. SELECT
  2. *
  3. FROM
  4.   pg_locks
  5. WHERE
  6.   relation =16501
  7.   AND granted IS true;

以下是结果:

  1. locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |       mode       | granted | fastpath | waitstart
  2. ----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+------------------+---------+----------+-----------
  3.  relation |5|16501||||||||3/243227|295995|RowExclusiveLock| t       | f        |
  4. (1 row)

这显示PID为295995 是持有锁的进程。

4. 查找阻塞进程正在执行的操作

现在我们知道哪个进程已经获得了锁，我们可以回到pg_stat_activity来查看该PID正在执行什么操作：

  1. SELECT
  2.   pid,
  3.   state,
  4.   wait_event,
  5.   wait_event_type,
  6.   left (query,40)
  7. FROM
  8.   pg_stat_activity
  9. WHERE
  10.   pid =295995;

以下是结果:

  1. pid   |        state        | wait_event | wait_event_type |                left
  2. --------+---------------------+------------+-----------------+-------------------------------------
  3. 295995| idle in transaction |ClientRead|Client| RELEASE pg_psql_temporary_savepoint

最后一列显示了该会话执行的最后一个语句，这在本例中是更新后的保存点释放，但在大多数情况下，它将显示一个活动事务。

一把锁管所有

以上的语句一旦你知道你要找的内容，就相当直接了当，但它们也可以组合成单个语句用于一般的阻塞/被阻塞查询。Postgres维基上有一些很好的组合版本。
通常情况下，您可能会发现被阻塞的语句被另一个语句（以及另一个，还有另一个……）所阻塞。在这些情况下，仍然可以追踪到导致所有其他进程阻塞的那个进程，但这可能是一次艰难而意想不到的旅程。对于这些情况，Crunchy Data的一位同事Brian Pace编写了一个查询，帮助显示正在等待其他锁的锁，直到最初锁定的PID。

  1. WITH sos AS (
  2.     SELECT array_cat(array_agg(pid),
  3.            array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
  4.     FROM pg_locks
  5.     WHERE NOT granted
  6. )
  7. SELECT a.pid, a.usename, a.datname, a.state,
  8.        a.wait_event_type ||': '|| a.wait_event AS wait_event,
  9.        current_timestamp-a.state_change time_in_state,
  10.        current_timestamp-a.xact_start time_in_xact,
  11.        l.relation::regclass relname,
  12.        l.locktype, l.mode, l.page, l.tuple,
  13.        pg_blocking_pids(l.pid) blocking_pids,
  14. (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
  15.        coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(casewhen locktype='transactionid'then1else array_length(pg_blocking_pids(l.pid),1)+1end,0),a.pid||'.0') lock_depth,
  16.        a.query
  17. FROM pg_stat_activity a
  18.      JOIN sos s on (a.pid = any(s.pids))
  19.      LEFT OUTER JOIN pg_locks l on (a.pid = l.pid andnot l.granted)
  20. ORDER BY lock_depth;

语句的示例输出：

  1. pid   |   usename   | datname  |        state        |     wait_event      |  time_in_state  |  time_in_xact   |  relname   |   locktype    |        mode         | page | tuple |     blocking_pids      | last_session | lock_depth |                       query
  2. --------+-------------+----------+---------------------+---------------------+-----------------+-----------------+------------+---------------+---------------------+------+-------+------------------------+--------------+------------+----------------------------------------------------
  3. 879401| application | postgres | idle in transaction |Client:ClientRead|00:29:53.512147|00:30:01.31748||||||||879401.0|select*from sampledata where id=101for update;
  4. 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;
  5. 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;
  6. 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;
  7. 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;
  8. 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;
  9. (6 rows)

在这个虚构的例子中，我们有以下进程：
879401 - 处于“空闲事务”状态的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的巫师，我们具备解除锁定的技能。

Commit

如果语句显示为“空闲事务”，则可能存在一个未提交的事务，它是由BEGIN语句开始的。在这种情况下，您可以使用以下语句提交事务：

  1. COMMIT;

Rollback

如果您执行了一些意外的更新操作，或者遇到了错误，那么您可以中止事务并回滚已经进行的任何更改，使用以下语句：

  1. ROLLBACK;

取消进程

如果这不是您启动的事务，在大多数情况下，您可以取消正在运行的查询，使用以下语句：

  1. SELECT pg_cancel_backend(PID);

终止后端连接和进程

如果上面的取消语句不起作用，您可以执行终止后端语句，这将结束该进程及其关联的数据库连接。

  1. SELECT pg_terminate_backend(PID);

为什么PostgreSQL进行了锁定？

PostgreSQL的多版本并发控制系统非常先进，大部分情况下允许您在不锁定表的情况下查询、更新和插入行。锁定主要有两种类型：

  • 共享锁 - 资源可以同时被多个后端/会话访问

  • 排他锁 - 资源一次只能被单个后端/会话访问
    通常导致我们遇到麻烦并阻塞其他查询和进程的锁类型是排他锁。如果您想了解概述，请参阅David的文章《Postgres锁：何时引起关注？》。可能有数百种方法可以在表上放置排他锁，但以下是我们在与客户合作中最常见的几种方式：

  • Alter Table
    到目前为止，我看到的最常见的会导致排他锁的事件是ALTER TABLE命令，该命令可以直接对数据库发出，也可以在运行迁移时通过应用程序的ORM发出。ALTER TABLE本身会获取ACCESS EXCLUSIVE锁（请参阅ALTER TABLE文档），这几乎会阻止该表上的每个其他进程。

  • ORM framework
    ORM框架可能隐藏会产生死锁的循环依赖关系。在事务范围内执行时，应用程序端出现错误，导致锁定并使未来事务完成所需的时间变长。

  • Create index
    如果您没有使用CREATE INDEX CONCURRENTLY，创建索引可能会锁定表。

  • Vacuum
    VACUUM FULL将对表获取ACCESS EXCLUSIVE锁，因此应仅在极少数情况下使用。

  • Other
    PostgreSQL文档中有一个表格，显示了不同的锁定模式，它们如何相互阻塞，以及导致这些锁定的一些语句类型的示例。

采取主动措施处理锁

让我们看一些未来管理锁定的几点建议。

记录锁等待

您可以通过打开log_lock_waits来记录任何时候您的查询正在等待的锁。在日志中出现的 lock_waits可能是进程争用的一个很好的指标。启用这个功能几乎没有额外的开销，对生产数据库非常安全。在Crunchy Bridge 集群上，默认情况下设置为“on”：

  1. log_lock_waits = on

设置锁超时

我们通常建议客户在会话中设置一个 lock_timeout，这样它将在一定时间后取消事务并释放它持有的任何锁。这有助于防止其他进程被困在它们后面的调用链中。

  1. ALTER SYSTEM SET lock_timeout ='10s';

总结

  • 通过查找在pg_stat_activity中等待锁的进程，即查找那些处于活动状态但wait_event 或 wait_event_type不为NULL 的进程。

  • 使用此查询来查找锁的来源（真的将此查询保存在某处，您可能某天会用到它）。

  1. WITH sos AS (
  2.     SELECT array_cat(array_agg(pid),
  3.            array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
  4.     FROM pg_locks
  5.     WHERE NOT granted
  6. )
  7. SELECT a.pid, a.usename, a.datname, a.state,
  8.        a.wait_event_type ||': '|| a.wait_event AS wait_event,
  9.        current_timestamp-a.state_change time_in_state,
  10.        current_timestamp-a.xact_start time_in_xact,
  11.        l.relation::regclass relname,
  12.        l.locktype, l.mode, l.page, l.tuple,
  13.        pg_blocking_pids(l.pid) blocking_pids,
  14. (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
  15.        coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(casewhen locktype='transactionid'then1else array_length(pg_blocking_pids(l.pid),1)+1end,0),a.pid||'.0') lock_depth,
  16.        a.query
  17. FROM pg_stat_activity a
  18.      JOIN sos s on (a.pid = any(s.pids))
  19.      LEFT OUTER JOIN pg_locks l on (a.pid = l.pid andnot l.granted)
  20. ORDER BY lock_depth;

  • 通过取消进程的PID或对持有锁并阻止其他进程的进程的执行COMMIT或ROLLBACK来结束锁。

  • 谨慎使用ALTER TABLE命令、不带CONCURRENTLY的CREATE INDEX，或者来自ORM 的失控进程可能持有排他锁并阻塞常规数据库处理。

  • 设置 lock_timeout 可能是一个好主意，并且通常建议进行主动日志记录以跟踪持续存在的问题。
    感谢我的同事 Brian Pace 提供了这个出色的级联锁查询。

ImageImage

KEEP THE THREAD GOING

更多来源文章