【PGCCC】表级锁剖析：减少锁定影响

原创 PostgreSQL培训考试认证中心 2025-02-06

我之前撰写有关PostgreSQL 中表级锁的剖析的博客。在这篇博客中，我们讨论了数据库系统使用锁定机制的原因以及 Postgres 如何利用 MVCC 来避免大多数并发问题，从而减少锁定的必要性。然后我们讨论了 DDL 锁，并解释了 Postgres 锁队列的工作原理。

在这篇后续文章中，我们将讨论锁争用，以探索减少生产系统中锁定影响的方法，以减少与 DDL 更改相关的潜在停机风险。

锁争用

当多个事务争夺对同一数据库资源（如表或行）的访问权限时，就会发生锁争用，并且至少一个事务必须等待，因为它需要的锁与其他事务持有的锁冲突。在 PostgreSQL 中，这种情况通常发生在需要独占锁的架构修改（DDL 操作）期间，或者在对同一行执行大量并发 DML 操作期间。发生争用时，事务会形成一个队列，等待轮到它们获取所需的锁。高锁争用会导致吞吐量下降、延迟增加，严重的情况下还会导致应用程序超时或停机。这在高流量生产系统中尤其成问题，因为长时间运行的查询或时间不当的架构更改可能会导致一连串等待事务，从而有效地阻止对关键表的访问。

减少锁定冲击

在 PostgreSQL 中进行架构更改或运行维护任务时，尽量减少锁定至关重要，以免阻塞其他查询并降低性能。并非所有操作都需要相同级别的锁定，PostgreSQL 提供了工具和技术来尽量减少锁定影响。

使用 CONCURRENTLY 命令

与不带的相同语句相比，CREATE INDEX CONCURRENTLY或等命令获取的限制较少的锁，从而允许其他操作继续进行。但是，这些命令：ALTER TABLE DETACH PARTITION CONCURRENTLYCONCURRENTLY

需要更长时间才能完成。
是非事务性的（不能处于事务块中，不能回滚）。
需要额外小心处理故障，这可能会留下部分变化（有FINALIZE清理或完成工作之类的命令）。

拆分复杂操作

减少锁争用的最有效策略之一是将大型 DDL 操作分解为更小、更少阻塞的步骤。

假设您需要添加一个NOT NULL具有默认值的列：

ALTER TABLE mytable ADD COLUMN newcol timestamptz NOT NULL DEFAULT clock_timestamp();复制

此单个命令需要ACCESS EXCLUSIVE锁并将重写整个表。对于大型表，这可能会导致严重的停机时间，因为它：

阻止所有并发访问（甚至SELECTs）
在整个表重写过程中持有锁
对于大型表可能需要几分钟甚至几小时

我们可以将上述的单个繁重操作分解为三个较少阻塞的步骤：

ALTER TABLE mytable ADD COLUMN newcol timestamptz DEFAULT clock_timestamp();
UPDATE mytable SET newcol = clock_timestamp() WHERE newcol IS NULL;
ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL;复制

首先，添加具有默认值的可空列：ALTER TABLE mytable ADD COLUMN newcol timestamptz DEFAULT clock_timestamp();
然后，填充任何NULL值：UPDATE mytable SET newcol = clock_timestamp() WHERE newcol IS NULL;您实际上应该分批执行此更新，请记住任何长时间运行的查询都可能导致问题。
最后添加NOT NULL约束：ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL;

这种方法有几个优点：

初始列添加非常快，只需要短暂的ACCESS EXCLUSIVE锁
数据填充可以使用常规ROW EXCLUSIVE锁进行，从而允许并发操作
如果出现问题，每个步骤都可以回滚

这里有几个好的做法需要注意。对大型表进行批量更新以避免长时间运行的事务始终是一个好主意。在我们的零停机时间、多版本架构更改工具pgroll中，当我们进行回填时，我们会分批进行，以避免对表中的每一行都进行行锁定。该工具允许设置自定义回填批量大小 ( --backfill-batch-size) 和延迟 ( --backfill-batch-delay) 来控制回填的速度。

这种拆分 DDL 操作的模式可以应用于许多其他架构更改。一般原则是找到方法将需要ACCESS EXCLUSIVE锁的操作分解为更小的步骤，这些步骤可以使用限制较少的锁或更短的锁持续时间。这可以减少强锁的持续时间，并防止长时间运行的操作阻塞其他操作。

在执行复杂操作时，密切关注锁争用始终是一个好主意。有时，您甚至可能希望明确锁定某些内容以防止意外的并发访问。并且不要忘记设置适当的lock_timeout值以确保事务不会永远等待。

单独验证约束

正如我们之前所讨论的，通过调整查询，有多种方法可以实现相同的结果。在上一节中，作为最后一步，我们NOT NULL使用以下查询添加了约束：

ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL;复制

该命令仍然需要很长时间并且会阻塞写入，但它是一种改进，因为与原始命令不同，它不会阻塞读取，并且由于它只进行一次表扫描，因此它比原始命令花费的时间更短。

我们可以通过利用CHECK约束来进一步优化这一点：

ALTER TABLE mytable ADD CONSTRAINT mytable_newcol_not_null CHECK (newcol IS NOT NULL) NOT VALID;
ALTER TABLE mytable VALIDATE CONSTRAINT mytable_newcol_not_null;
ALTER TABLE mytable ALTER COLUMN newcol SET NOT NULL; --optional
ALTER TABLE mytable DROP CONSTRAINT mytable_newcol_not_null; --optional复制

首先，我们可以添加一个NOT VALID检查约束：ALTER TABLE mytable ADD CONSTRAINT mytable_newcol_not_null CHECK (newcol IS NOT NULL) NOT VALID;
然后验证约束：ALTER TABLE mytable VALIDATE CONSTRAINT mytable_newcol_not_null;扫描期间VALIDATE CONSTRAINT不会阻止写入。

如果您希望NOT NULL在列上设置一个集合（而不是CHECK约束），那么现在设置它只是元数据操作，因为 Postgres 足够聪明，可以识别有效的 CHECK约束（但仍会执行短暂的ACCESS EXCLUSIVE锁定）。之后，您还应该删除现在冗余的CHECK约束（另一个短暂的ACCESS EXCLUSIVE锁定）。

拆分操作需要专业知识，但通常需要尽量减少锁定。尝试找到锁定较少的方法。如果没有来自普通 SQL 的重度锁定，有些事情是不可能或很难做到的。

Postgres 不断进步

PostgreSQL 对 DDL 进行了许多优化，并且在每个版本中都在不断改进。让我们看一个稍作修改的先前示例：

ALTER TABLE mytable ADD COLUMN newcol int NOT NULL DEFAULT 1;复制

此命令仍然需要ACCESS EXCLUSIVE锁并阻止其他操作。但是，在现代 PostgreSQL 版本中，它执行得非常快，因为 Postgres 认识到可以将常量默认值（如）存储为元数据而无需重写表。锁定持续时间很短，使此操作在生产中的破坏性大大降低。

相比之下，较旧的 PostgreSQL 版本会触发此相同命令的全表重写，类似于我们之前的DEFAULT clock_timestamp()示例。性能差异很大，尤其是对于大型表。

如果可能的话，请始终运行最新版本的 Postgres：

新版本包括减少所需锁模式的优化。
新增CONCURRENTLY了 DDL 操作变体
锁定机制本身得到改进
以前长时间运行的操作可能会变成仅快速更改元数据

运行较旧的 PostgreSQL 版本意味着您可能要处理较新版本中已解决的锁定问题。每个主要版本都会对架构修改操作进行改进，通常可使其运行速度更快，对生产工作负载的干扰更小。

结论：平衡锁、性能和正确性

PostgreSQL 的锁定系统是确保数据一致性和并发性的强大工具，但它也是争用和瓶颈的根源。通过了解锁定的类型、其影响以及最小化锁定的策略，您可以有效地管理锁定并保持数据库的性能。

#PG证书#PG考试#PostgreSQL培训#PostgreSQL考试#PostgreSQL认证

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者