【PGCCC】PostgreSQL 中表级锁的剖析

本博客解释了 PostgreSQL 中的锁定机制，重点关注数据定义语言 (DDL) 操作所需的表级锁定。

锁定还是解锁的艺术？

人们通常将数据库锁与物理锁进行比较，这甚至可能导致您订购有关锁的历史、波斯锁和撬锁技术的书籍。我们大多数人可能都是通过深入研究“锁定”一词来理解 PostgreSQL 中与物理锁没有太大关系的概念，物理锁主要与安全性有关。然而，Postgres 锁完全是关于并发性的，以及控制哪个事务可以持有锁，而另一个事务可以执行其操作，理想情况下不会相互阻塞。但正如我们所知，没有一个世界是完美的，无论是门锁还是AccessShareLock…

但是，既然我已经买了那些关于锁的书，我想我应该可以对开锁艺术和数据库锁定机制进行一些比较。首先我要说的是：要想撬开锁，不管是哪种锁，你都需要深入了解它的内部工作原理；销钉、弹子和机制是如何相互作用的。通过操纵它们，你可以找到正确的位置来解锁门或保险箱，而无需钥匙！同样，要想管理数据库锁，你需要了解数据库的内部工作原理，主要是 Postgres 中的并发工作原理。

MVCC 简介

PostgreSQL 使用多版本并发控制 (MVCC)来避免传统上与数据库相关的许多锁定问题。MVCC 的工作原理如下：

• 写入数据的新副本而不是修改行。
• 确保读取不会阻塞写入，并且写入不会阻塞读取。

当事务更新数据时，Postgres 会创建行的新版本，同时保留旧版本。每个行版本都包含用户不可见但对 MVCC 至关重要的系统列：

• xmin：创建此版本的交易 ID
• xmax：删除/更新此版本的事务ID（如果当前版本则为空）
• cmin, cmax：事务内的命令 ID
• ctid：行版本的物理位置
  使用默认的读已提交隔离级别，每次开始执行语句时，Postgres 都会创建一个快照，其中包含：
• 当前所有活跃交易ID
• 最新提交的事务ID
• 正在进行的交易列表

让我们看看它在“读已提交”隔离级别下的实际工作原理：

MVCC 示例

总而言之，存在一行，其中 Alice 的薪水为 50000。第一个事务 ( txid:100) 启动并获取其语句的快照SELECT，并看到 Alice 的薪水为 50000，因为该行的薪水xmax为空（意味着它是当前的），该行的薪水xmin（99）小于我们的事务 ID（100）并且事务99已经提交。

第二个事务（txid:101）将工资更新为 60000。这将创建两个行版本；旧版本标记为xmax=101，新版本标记为xmin=101。然后第二个事务提交，使其更改对任何新快照都可见。

当第一个事务执行其第二条SELECT语句时（在事务 2 提交之前），它仍然看到薪水 50000，因为事务101尚未提交。在事务 2 提交之后，当事务 1 执行其第三条SELECT语句时，它会获得一个新快照（读取已提交行为），现在看到薪水 60000，因为：

• 每个语句都会获得一个新的快照
• 交易101现已提交
• 行版本为xmin=101当前版本（xmax为空）
• 旧版本xmax=101在新快照中不再可见
  此行为特定于“读已提交”隔离级别，其中每个语句都会获得一个新的快照，并且可以看到来自其他事务的已提交的更改，即使在同一个事务中也是如此。

但是，当然，MVCC 方法也有一些含义：

• 死行版本会不断累积，直到被VACUUM
• 由于保留多个版本，数据库大小可能会暂时增大
• 定期VACUUM维护对于性能至关重要
  最后，MVCC 设计的优点显而易见，它允许 Postgres 为长时间运行的查询提供一致的快照（希望它们不会运行太长时间 😄）、为混合读写工作负载提供高并发性以及无需过多锁定即可实现强隔离。虽然 MVCC 设计并非 PostgreSQL 独有，但得益于这种方法，我们解决了大多数并发性问题。

锁定的目的

所有锁定，无论其类型如何，都会降低吞吐量，并可能增加延迟，这意味着性能损失，因为没有什么是免费的。如果我的目的是确保我的数据没有损坏，并且每个人在查询时都能得到正确的结果，我必须同意，当多个事务针对同一张表或同一行时，我必须锁定访问权限，以确保我们花一些时间来保持事物的顺序，而不是快速显示错误的结果。

但是我们也不能长时间保持锁定，因为这也会带来后果。让我们想象一下两个极端：我们从不锁定任何交易。一份长期运行的财务报告正在计算各部门的平均值，而人力资源部门正在处理年终加薪，同时，我们尝试添加一个新的审计列。在 PostgreSQL 中，即使没有显式锁定，添加列也需要AccessExclusiveLock在表上锁定。如果我们以某种方式绕过这一点（想象一个假设的不安全模式），我们将看到真正的混乱：工资更新可能会失败，因为它们在交易中遇到不同的表结构，并且后续查询可能会面临数据损坏，因为系统目录与实际表数据不一致。听起来很忙乱，但不锁定任何东西会造成不同程度的混乱。
现在，让我们考虑另一个极端：我们锁定每个操作，每个操作都等待另一个操作；一个操作完成，另一个操作开始。我们为一个正在读取的查询阻止整个表。如果几个用户（可能使用小型数据库）从来不需要同时查询同一张表，那么这也是可以接受的；就像运行夜间报告的单用户会计系统一样。

当我们需要同时为多个用户提供服务时，我们必须接受一定程度的隔离，因此我们要确保遵守一个标准，以确保不会出现脏读或幻读等情况。这就是为什么 Postgres 提供从“已提交读”到“可序列化”等不同隔离级别的原因。

DDL 锁

MVCC 方法使 PostgreSQL 能够高效地执行并发 DML 操作。写入操作不会就地修改数据，而是创建数据的新副本，从而允许读取和写入操作不相互阻塞地进行。但是，即使在基于 MVCC 的系统中，某种程度的锁定也是不可避免的。读取操作可能不会阻塞写入操作，但它们仍会获取表、类型和视图等数据库对象上的轻量级锁定。

因此，MVCC 可以防止写入阻塞读取，但无法防止 DDL 命令占用对象锁定。同一 DDL 命令的不同变体可能需要不同级别的锁定强度。

对于 DDL 操作（例如ALTER TABLE或VACUUM FULL），通常需要更强的锁定，这可能会阻止其他操作。此类操作可能会阻止其他 DDL 命令、DML 操作，甚至试图访问同一对象的 SELECT 查询。由于每个 DDL 命令（有时甚至是子命令）都有自己特定的锁定要求，因此复杂性会增加，因此了解繁忙数据库中架构修改的影响至关重要。

表级锁定模式

让我们看一下各种表级锁模式，每种模式都服务于不同的操作：

• ACCESS SHARE：操作使用的最基本的锁SELECT，多个事务可以同时持有此锁，限制最少。

• ROW SHARE：由使用SELECTFOR UPDATE/SHARE，与除排他锁之外的大多数其他锁兼容。

• ROW EXCLUSIVE：DML操作必需（INSERT/UPDATE/DELETE/MERGE）

• 共享更新独占VACUUM：由、ANALYZE和 等维护操作使用，与、、、CREATE INDEX
  CONCURRENTLY冲突。ShareLockShareRowExclusiveLockExclusiveLockAccessExclusiveLock

• 分享：需要CREATE INDEX SHARE ROW EXCLUSIVE：创建触发器时使用

• EXCLUSIVE：对于以下操作是必需的REFRESH MATERIALIZED VIEW CONCURRENTLY ACCESS

• EXCLUSIVE：最强的锁，由DROP TABLE、TRUNCATE、某些ALTER TABLE命令和 等操作使用VACUUM FULL。限制性最强，阻止所有并发访问，并且是大多数架构修改所必需的。

我认为，了解哪些操作获得哪些锁很重要，但可以轻松检查。 理解锁模式如何相互作用可能更为重要。 不同的锁模式与其他不同的模式发生冲突。

冲突的锁定模式
有几个关键点需要理解，ACCESS EXCLUSIVE与所有内容都相冲突，包括ACCESS SHARE ( SELECT)。Postgres 进行了许多优化，以便在可能的情况下采用较弱的锁定模式。然而，没有什么是完美的，它仍然会对某些 DDL 操作采取强锁。一旦事务采取了强锁，即使语句已完成，它也会持有该锁。

一个重要的教训是，DDL 操作可能会在整个事务期间阻塞写入和读取。因此，避免将需要强锁的命令与同一事务中的其他命令混合使用至关重要。例如，如果您需要同时执行表更改和数据更新，最好将它们分成不同的事务以最大限度地减少阻塞。

了解 Postgres 中的锁队列

当事务请求的锁与另一个事务已持有的锁冲突时，它将进入锁队列。默认情况下，请求事务将无限期等待，直到锁可用。这些等待的锁形成一个队列，但不幸的是，这个队列在系统视图中不直接可见pg_locks。相反，您可以使用该pg_blocking_pids()函数来识别哪些后端正在阻止特定后端。

队列中位于前面的锁可能会阻塞位于其后面的锁，从而导致级联延迟。例如：

1. 长期运行SELECT持有ACCESS SHARE LOCK。
2. 需要ALTER TABLE DETACH PARTITION简短的访问独占锁
3. 但它们发生冲突因此ALTER TABLE被放入锁队列中。
4. 另外 20个后端正在尝试执行简单的主键查找SELECT。
5. 但它们与ALTER TABLE的锁发生冲突，所以它们排在ALTER TABLE操作后面。
6. 现在，对给定表的所有访问都排队在后面，并且在长时间运行SELECT和ALTER TABLE两者都完成之前不会进行处理。

这种累积等待会严重影响性能，尤其是在高并发环境中。用于lock_timeout限制事务等待锁定的时间。对于 DDL 操作，设置lock_timeout通常足以防止复杂的等待情况。但是，在实现超时时，您的应用程序必须准备好妥善处理故障，通常是通过为超时的 DDL 操作实现重试逻辑。

从中得到的最大启示是，任何长时间运行的查询都可能在模式更改期间造成阻塞，但可以通过设置适当的值来减轻这种累积等待效应lock_timeout。

将所有东西锁在一起

我注意到这篇博客已经很长了，我还有很多话要说。所以，我将在这里结束它，并写一篇后续文章来讨论锁争用以及如何在必要时最大限度地减少锁的影响。

到目前为止，我们已经探讨了 MVCC 如何帮助处理并发事务，以及各种锁定模式如何保护数据完整性。我们已经看到，锁定的范围从更宽松的ACCESS SHARE到更严格的ACCESS EXCLUSIVE，每种锁定在维护数据一致性方面都发挥着至关重要的作用。虽然 MVCC 避免了许多传统的锁定问题，但某种程度的锁定仍然是不可避免的。关键是找到正确的平衡，锁定太少会导致数据损坏的风险，而锁定太多会造成不必要的瓶颈。

总而言之，以下是本博客的要点：

• MVCC 将保护您免受写入阻止读取的损害，但不能保护您免受 DDL 所采取的对象锁的损害。
• 同一个 DDL
• 命令的不同变体可能需要非常不同的锁强度。 DDL 可能会阻止事务整个运行期间的写入和/或读取。
• 不要将需要强锁的命令与在同一事务中工作的其他命令混合。
• 用于lock_timeout限制等待锁的时间。 用于lock_timeoutDDL
• 命令通常就足够了。必须能够处理故障，例如再次重试 DDL。
  #PG证书#PG考试#PostgreSQL培训#PostgreSQL考试#PostgreSQL认证