PostgreSQL：事务、锁、隔离级别、MVCC、VACUUM

PostgreSQL：事务、锁与可串行化隔离深度解析

开发者在 PostgreSQL 中经常会遇到一些不寻常的查询行为：例如卡顿、并发操作中 UPDATE 结果出乎意料，或者事务失败。深入理解隔离级别、锁以及 VACUUM 的工作原理等内部机制，对于构建可靠且高性能的应用程序至关重要。本文将深入剖析这些概念，并辅以实用的 SQL 示例进行说明，同时强调 PostgreSQL 近期的改进。

事务中的时间：now() 与 clock_timestamp()

PostgreSQL 提供了两个主要的函数来获取当前时间，它们在事务上下文中的行为有所不同。now() 函数返回当前事务的开始时间，并在整个事务执行期间保持不变。这确保了单个事务中操作的时间戳一致性，例如在设置 created_at 和 updated_at 时。

BEGIN;
SELECT now();
-- ... other operations ...
SELECT now(); -- Will return the same time as the first call
SELECT clock_timestamp(); -- May differ
COMMIT;

与 now() 不同，clock_timestamp() 函数提供的是实际的当前执行时间，即使在单个查询或事务中也可能发生变化。理解这一区别有助于避免处理时间数据时出现错误。

PostgreSQL 中的事务隔离级别

PostgreSQL 相比其他关系型数据库管理系统（RDBMS），其隔离级别模型更为简化，仅提供三种：

• 读已提交 (Read Committed)：默认级别，事务中的每个命令都会看到一份新的数据快照。在 PostgreSQL 中，脏读从根本上是不可能发生的，因此读未提交 (Read Uncommitted) 等同于读已提交。
• 可重复读 (Repeatable Read)：确保事务中的所有读操作都看到相同的数据快照，该快照在第一个查询执行时固定。这可以防止幻读和不可重复读。
• 可串行化 (Serializable)：最严格的级别，保证并发执行的事务产生的结果与它们按顺序执行的结果相同。这是通过可串行化快照隔离 (Serializable Snapshot Isolation, SSI) 机制实现的。

PostgreSQL 的一个重要特性是它支持在事务中执行 DDL（数据定义语言）操作。这使得创建、修改或删除数据结构并在必要时回滚成为可能，从而增强了迁移和复杂模式更改的安全性。

锁机制与并发控制

尽管 PostgreSQL 使用多版本并发控制（MVCC），但它仍然积极地为写操作使用锁。当两个事务尝试更新同一行时，第二个事务将等待第一个事务完成。考虑以下示例：

-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... do not commit ...

-- Session 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- This query will hang, waiting for Session 1 to complete.

下表展示了 PostgreSQL 中主要的锁类型、其用途以及冲突模式：

| 锁类型 | 获取时机 | 冲突对象 |

| :-------------------- | :------------------------------------------ | :---------------------------- |

| 访问共享锁 (ACCESS SHARE) | SELECT | 访问独占锁 (ACCESS EXCLUSIVE) |

| 行共享锁 (ROW SHARE) | SELECT FOR UPDATE/SHARE | 独占锁 (EXCLUSIVE)、访问独占锁 (ACCESS EXCLUSIVE) |

| 行独占锁 (ROW EXCLUSIVE) | INSERT、UPDATE、DELETE | 共享锁 (SHARE) 及以上 |

| 共享更新独占锁 (SHARE UPDATE EXCLUSIVE) | CREATE INDEX CONCURRENTLY、ANALYZE、VACUUM | 共享更新独占锁 (SHARE UPDATE EXCLUSIVE) 及以上 |

| 共享锁 (SHARE) | CREATE INDEX | 行独占锁 (ROW EXCLUSIVE) 及以上 |

| 共享行独占锁 (SHARE ROW EXCLUSIVE) | CREATE TRIGGER、ALTER TABLE (部分) | 行共享锁 (ROW SHARE) 及以上 |

| 独占锁 (EXCLUSIVE) | 只允许读 | 行共享锁 (ROW SHARE) 及以上 |

| 访问独占锁 (ACCESS EXCLUSIVE) | DROP TABLE、ALTER TABLE、VACUUM FULL | 所有 |

锁的严格程度越高，其在表中的级别越高，冲突操作的范围也越广。具有冲突锁的操作不能在同一对象上同时执行；其中一个会等待。值得注意的是，CREATE INDEX CONCURRENTLY 允许在不阻塞 DML 操作的情况下构建索引，这与标准的 CREATE INDEX 不同。

读取异常：幻读与不可重复读

在读已提交 (Read Committed) 隔离级别下，可能会发生读取异常，导致结果不一致：

• 幻读 (Phantom Reads)：当一个事务两次执行相同的查询时，第二次查询看到了在两次查询之间由另一个已提交事务插入的新行。

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- 例如，330

-- Session 2 (concurrently):

INSERT INTO t_test2 VALUES (1, 30); COMMIT;

-- Session 1:

SELECT sum(value) FROM t_test2; -- 现在是 360，新行出现了。

COMMIT;

```

• 不可重复读 (Non-Repeatable Reads)：当一个事务两次读取相同的数据时，第二次读取看到了由另一个已提交事务修改过的数据。

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- 返回一行

-- Session 2 (concurrently):

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10; COMMIT;

-- Session 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- 空！数据已更改。

COMMIT;

```

为了防止这些异常，可以使用可重复读 (Repeatable Read) 隔离级别。在此级别下，事务操作的是一个固定的数据快照，在事务完成之前不会改变，无论其他事务做了什么更改。

可串行化隔离级别的特殊性

可串行化 (Serializable) 级别旨在提供最大程度的隔离，模拟事务的顺序执行。然而，如果 PostgreSQL 检测到可能违反执行顺序的潜在冲突，这可能导致串行化错误（ERROR: could not serialize access）。在这种情况下，其中一个事务将被中止，必须重试。

可串行化级别的一个有趣之处是假冲突 (false conflicts)，它由谓词锁（SSI）引起。PostgreSQL 在页面级别而不是单个行级别存储这些锁。如果两个事务处理逻辑上不同的数据集，但这些数据物理上位于同一个磁盘页面上（这在小表中经常发生），即使逻辑上没有冲突，也可能发生串行化冲突。在数据分布在不同页面的大表中，此类冲突则不那么常见。

解决并发问题

让我们考虑一个经典的并发问题：两个事务同时尝试基于过时读取的数据进行修改。例如，Alice 和 Bob 正在值班，如果至少还有一个人在岗，他们都想离开。如果两个事务都使用 Repeatable Read 隔离级别，并且都看到有两人在岗，它们都将成功提交，最终导致没有人值班。

为了解决此类问题，可以使用以下方法：

• 可串行化 (Serializable)：第二个事务将因串行化错误而失败，然后可以重试。这保证了正确的结果，但需要在应用程序中进行错误处理。
• SELECT ... FOR UPDATE：在读取时使用 FOR UPDATE 会显式锁定选定的行，阻止其他事务修改它们，直到当前事务完成。第二个会话将等待第一个会话完成，然后它将看到更新后的数据。

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- 锁定行

-- ... 后续操作 ...

COMMIT;

```

• 原子操作：将条件检查和操作合并到一个查询中。如果在 UPDATE 时不满足条件，查询将不会影响任何行，从而消除了竞态窗口。

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

```

咨询锁 (Advisory Locks)

PostgreSQL 提供了一种咨询锁机制（pg_advisory_lock、pg_advisory_xact_lock），允许控制对不直接与表或行绑定的任意资源的并发访问。这些锁作用于整数值，可用于协调进程。

• pg_advisory_lock：会话级锁。持续存在，直到显式释放（pg_advisory_unlock）或连接关闭。
• pg_advisory_xact_lock：事务级锁。在 COMMIT 或 ROLLBACK 时自动释放。

重要的是要记住，咨询锁仅在单个数据库内有效。对于具有多个 PostgreSQL 实例的分布式系统，需要外部解决方案（例如 Redis、ZooKeeper、etcd）。

VACUUM 在 MVCC 中的作用

PostgreSQL 中的 MVCC（多版本并发控制）意味着每次 UPDATE 或 DELETE 操作时，旧版本的行不会立即删除，而是被标记为“死元组 (dead tuples)”。这些死元组占用空间，必须进行清理以释放空间供新数据使用，并防止表膨胀 (table bloat)。VACUUM 进程负责这项任务。

默认情况下，VACUUM 不会减小表文件的物理大小。它只是将释放的空间标记为可重用。这意味着后续的 INSERT 或 UPDATE 操作可以占用这些空间，而不会增加文件大小。

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB

UPDATE t_test SET id = id + 1; -- 创建新行版本
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (大小翻倍)

VACUUM t_test; -- 清理死元组，但文件大小不变
SELECT pg_size_pretty(pg_relation_size('t_test')); -- 仍为 ~7 MB

表文件物理大小的减小只有在 VACUUM 能够“截断尾部”时才会发生——也就是说，如果释放的页面位于文件的末尾。例如，在删除大量 id 较大的行之后：

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (大小减小)

自动 autovacuum 是一个至关重要的后台进程，它持续监控表的更改并运行 VACUUM 以维护数据库健康。在不深入理解其后果的情况下禁用它，在现代 PostgreSQL 版本中是一种反模式。

PostgreSQL 演进：过去 5 年的变化

尽管事务和锁的基本原理保持不变，但 PostgreSQL 仍在不断发展，提供旨在提高性能和可用性的改进：

• VACUUM 优化：PostgreSQL 13 引入了 VACUUM 期间的并行索引处理，PostgreSQL 16 增加了跳过自上次通过以来未更改的页面功能。这些改进显著加速了大型表上的 VACUUM 操作，使得手动管理变得不那么重要。
• REINDEX CONCURRENTLY (PG 12)：允许在不阻塞表上的 DML 操作的情况下重建索引，这对于高负载系统至关重要。
• 改进的锁监控：系统视图 pg_stat_activity 和 pg_locks 增加了新字段，pg_wait_sampling 扩展提供了详细的等待统计信息，简化了性能问题诊断。

关键要点：

• now() 与 clock_timestamp()：now() 固定事务开始时间以保持一致性，clock_timestamp() 提供实际当前时间。
• 隔离级别：PostgreSQL 提供 读已提交 (Read Committed)（默认）、可重复读 (Repeatable Read)（防止读取异常）和 可串行化 (Serializable)（提供最大一致性，但有串行化错误的风险）。
• 锁：尽管有 MVCC，PostgreSQL 仍使用写锁。理解锁表及其冲突对于优化并发访问至关重要。
• 可串行化与假冲突：可串行化 级别可能由于页面级谓词锁而导致错误，需要重试事务。
• VACUUM 与 MVCC：VACUUM 清理死元组，但通常不减小文件大小；它只是将空间标记为可重用。只有当释放的页面位于文件末尾时，才会发生物理大小减小。

— Editorial Team