精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

如何识别和解决PostgreSQL死锁与锁争用

掌握PostgreSQL锁争用和死锁。学习使用`pg_locks`识别阻塞会话，分析常见死锁场景，并发现实用的技术，如一致的事务排序和优化查询，以预防和解决这些关键的数据库问题。确保更流畅、更高效的PostgreSQL操作。

如何识别和解决PostgreSQL死锁与锁争用

PostgreSQL死锁和锁争用通常表现为应用程序突然卡住。请求堆积，工作进程处于active或idle in transaction状态，而数据库本身可能仍有大量CPU空闲。问题不在于原始容量。一个会话正在等待另一个会话释放锁，有时后面会排起长队。

处理此问题最快的方法是区分两种情况。锁争用意味着一个会话正在等待，但它最终可能会继续。死锁意味着两个或多个会话在循环中相互等待，因此PostgreSQL必须取消一个事务。你使用相同的基本工具调试两者，但修复方法通常不同。

PostgreSQL锁基础

PostgreSQL使用锁来保护表、行、事务和其他内部对象，同时许多会话并发工作。它还使用MVCC，因此普通读取器和写入器通常不会相互阻塞。这就是为什么PostgreSQL可以很好地处理高并发，但这也是为什么锁问题可能令人困惑：问题通常是一个特定的语句模式，而不是一般的“太多用户”。

锁类型

PostgreSQL使用各种锁级别，每个级别提供不同程度的保护。理解这些是诊断问题的关键：

  • AccessShareLock： 由普通的SELECT使用。它主要与ACCESS EXCLUSIVE冲突，这就是为什么许多读取可以在写入发生时运行。
  • RowExclusiveLock： 表上的INSERT、UPDATE和DELETE常用。这个名字很容易被误解；它并不意味着表中的每一行都被独占锁定。
  • ShareUpdateExclusiveLock： 由诸如不带FULL的VACUUM、ANALYZE和一些索引操作等操作使用。它允许正常的读取和写入，但与多个维护操作冲突。
  • ShareLock / ShareRowExclusiveLock / ExclusiveLock： 由特定DDL和约束相关操作使用的更强表级模式。
  • AccessExclusiveLock： 限制性最强的表锁。ALTER TABLE、DROP TABLE、TRUNCATE和VACUUM FULL可以获取这种锁。它既阻塞普通读取也阻塞写入。

行级锁与表级锁模式是分开的。一个UPDATE获取表级RowExclusiveLock以及它更改的行上的行锁。当人们说“这一行被锁定了”时，他们通常意味着另一个事务已修改或使用FOR UPDATE选择了该行，并且尚未提交。

锁模式

锁模式指示事务所需的访问类型。它们通常由诸如RowExclusiveLock、ShareLock、ExclusiveLock等名称表示。

识别锁争用和阻塞会话

当多个事务等待其他事务持有的锁时，就会发生锁争用。这可能会显著降低应用程序的速度。pg_locks系统视图是诊断这些问题的主要工具。

使用pg_locks

pg_locks视图提供有关数据库系统中所有活动锁的信息。它对于理解哪些会话持有锁以及哪些会话在等待非常宝贵。

以下是一个识别阻塞会话的常见查询：

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
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.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

查询解释：

  • 我们将pg_locks与pg_stat_activity连接两次：一次用于被阻塞的进程，一次用于阻塞进程。
  • WHERE NOT blocked_locks.granted子句过滤当前正在等待的锁。
  • blocking_locks.pid != blocked_locks.pid确保我们不会报告会话阻塞自身。
  • pg_locks上的连接条件匹配同一资源上的锁。

解释输出

  • blocked_pid / blocking_pid：涉及的会话的进程ID（PID）。
  • blocked_user / blocking_user：与这些PID关联的用户。
  • blocked_statement / blocking_statement：当前正在运行或等待的SQL查询。
  • blocked_mode / blocking_mode：请求和持有的锁模式。

如果此查询返回行，则存在锁争用。blocking_pid持有一个blocked_pid正在等待的锁。

理解和解决死锁

当两个或多个事务各自等待循环中另一个事务持有的锁，从而创建一个双方都无法自行解决的循环依赖时，就会发生死锁。PostgreSQL检测死锁并通过中止其中一个事务（通常是导致死锁且完成工作最少的事务）来自动解决它们。

常见死锁场景

  1. 两个事务以相反顺序更新不同表中的不同行：

    • 事务A：更新表1中的行X，然后尝试更新表2中的行Y。
    • 事务B：更新表2中的行Y，然后尝试更新表1中的行X。 如果事务A锁定行X，事务B锁定行Y，则当它们尝试获取对方持有的锁时，将发生死锁。

  2. UPDATE后跟SELECT ... FOR UPDATE：

    • 事务A：更新一行。
    • 事务B：在同一行上执行SELECT ... FOR UPDATE。 如果当SELECT FOR UPDATE尝试获取共享锁时UPDATE仍然持有行独占锁，并且存在其他依赖关系，则可能发生死锁。

检测死锁

PostgreSQL将死锁信息记录到其服务器日志中。你通常会看到如下消息：

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL自动选择一个事务中止，以便其他事务可以继续。事后不要依赖pg_stat_activity来获取完整信息；当你查看时，已取消的语句可能已经消失。服务器日志通常是循环的最佳记录。

解决死锁

当检测到死锁并且PostgreSQL通过中止事务来解决它时：

  1. 识别受害者： 检查PostgreSQL日志中的deadlock detected消息。它将指定哪个进程被中止。
  2. 重试中止的事务： 接收到SQLSTATE 40P01（deadlock_detected）的应用程序应在操作安全重试时重试整个事务。仅重试最后一条语句可能会使应用程序状态不一致。
  3. 分析原因： 解决的关键是防止未来的死锁。这涉及理解死锁发生的原因（如常见场景中所述）并调整应用程序逻辑或数据库设计。

防止锁争用和死锁的技术

预防胜于治疗。实施最小化锁争用和避免死锁情况的策略对于高性能PostgreSQL数据库至关重要。

1. 一致的事务排序

  • 规则： 始终在所有事务中以相同顺序访问和修改资源（表、行）。如果多个事务需要更新TableA和TableB，确保它们始终先更新TableA再更新TableB，反之亦然，以一致的方式进行。
  • 示例： 如果一个事务需要更新users和orders中的记录，始终先对users执行操作，然后对orders执行操作。避免一个事务先更新users再更新orders，而另一个事务先更新orders再更新users的情况。

2. 最小化事务持续时间

  • 规则： 保持事务尽可能短。事务打开的时间越长，它持有的锁就越多，从而增加争用的机会。
  • 操作： 仅在事务中执行必要的数据库操作。将非数据库相关工作（例如，外部API调用、不依赖于事务状态的复杂计算）移到事务边界之外。

3. 使用适当的隔离级别

  • 规则： 理解并选择正确的事务隔离级别。PostgreSQL提供：
    • READ UNCOMMITTED（在PostgreSQL中由READ COMMITTED模拟）
    • READ COMMITTED（默认）
    • REPEATABLE READ
    • SERIALIZABLE
  • 操作： 默认的READ COMMITTED在防止脏读的同时提供了良好的性能。REPEATABLE READ和SERIALIZABLE提供更强的一致性，但可能导致更多的serialization_failure错误（本质上是快照隔离的死锁）以及可能更多的锁争用。仅在绝对必要时使用它们。

4. 优化查询和索引

  • 规则： 慢查询持有锁的时间更长。确保你的查询高效且索引良好。
  • 操作： 使用EXPLAIN ANALYZE识别慢查询。添加适当的索引以加快数据检索，特别是对于WHERE子句和JOIN条件。

5. 谨慎使用SELECT ... FOR UPDATE

  • 规则： SELECT ... FOR UPDATE在事务持续期间锁定行。这对于防止竞态条件非常强大，但也可能是争用的主要来源。
  • 操作： 仅在你确实需要锁定行以防止其他事务在你的事务完成工作之前修改它们时使用它。考虑在某些场景下建议锁是否更合适。

6. 建议锁

  • 规则： 对于不直接映射到数据库对象锁的应用程序级锁定或更复杂的同步需求，PostgreSQL的建议锁可以是一个强大的工具。
  • 操作： 使用诸如pg_advisory_lock()、pg_advisory_lock_shared()和pg_advisory_unlock()等函数来实现自定义锁定机制。死锁检测机制不会自动检测这些锁，因此应用程序逻辑必须小心管理它们。

7. 批量操作

  • 规则： 不要发出许多单独的UPDATE或DELETE语句，考虑将它们批处理到单个语句中，或者在可能的情况下使用COPY进行批量加载/更新。
  • 操作： 单个UPDATE语句可能比单独的UPDATE循环更有效地获取锁。分析批量操作的锁定行为。

实用分类流程

当事件活跃时，从等待的会话开始，而不是你脑海中最早的理论：

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

如果某个阻塞PID反复出现，检查它：

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

需要注意的短语是idle in transaction。该会话没有积极执行有用的数据库工作，但它可能仍然持有锁。这通常来自打开事务、执行查询、调用外部API、仅在API返回后提交的应用程序代码。如果可能，将外部调用移到事务之外。

小心取消。SELECT pg_cancel_backend(pid)要求当前查询停止。SELECT pg_terminate_backend(pid)终止会话并回滚其打开的事务。在生产事件中，终止阻塞者可能是正确的做法，但首先捕获查询和事务年龄，以便稍后修复代码路径。

事件后的另一个好习惯：保存阻塞查询、被阻塞查询以及来自应用程序日志的事务边界。单独的SQL语句通常不够。一个无害的UPDATE users SET last_seen_at = now()如果位于也等待支付API的事务内部，可能会成为阻塞者。死锁修复通常位于事务流级别，而不是在一个孤立的语句内部。

持久的修复通常是简单的：保持事务简短，以一致顺序访问共享资源，添加索引以防止更新扫描过多，并使应用程序重试干净地处理40P01和序列化失败。PostgreSQL可以检测死锁，但它无法为你重新设计事务模式。这部分属于事件平息后的应用程序和模式审查。