作者：劳伦斯·阿尔贝（Laurenz Albe）
高级顾问与支持工程师
Laurenz Albe 是 CYBERTEC 公司的高级顾问与支持工程师。自 2006 年以来，他一直在使用 PostgreSQL 并为其贡献代码，曾为核心编写补丁，并开发了 oracle_fdw。他拥有维也纳大学的数学硕士学位和维也纳技术大学的计算机科学硕士学位。闲暇时，他喜欢给孩子们读书，并思考语言的起源。
发表于：2025.02
事务系统是关系数据库的核心组件。它提供的服务有助于开发保证数据完整性的应用程序。SQL 标准规范了数据库事务的某些功能，但许多细节未指定。因此，关系数据库的事务系统可能存在很大差异。如今，许多人都试图摆脱 Oracle 数据库并转向 PostgreSQL。要将应用程序从 Oracle 迁移到 PostgreSQL，了解各个事务系统之间的差异至关重要。否则，您可能会遇到可能危及性能和数据完整性的意外情况。所以我认为对 Oracle 和 PostgreSQL 中的事务功能进行比较会很有用。
ACID：数据库事务提供的服务
不，这既不是化学问题，也不是药物滥用问题。ACID代表以下几个方面的缩写：
· 原子性：保证单个数据库事务中的所有语句形成一个单元，以便所有语句要么全部成功，要么全部不生效。这应该涵盖所有类型的问题，包括硬件故障。
· 一致性：保证任何数据库事务都不会违反数据库中定义的约束。
· 隔离性：保证并发事务不会引起某些异常（事务的串行执行不会引起数据库的可见状态）
· 持久性：保证已提交（完成）的数据库事务永远不会被撤消，即使系统崩溃或局部硬件故障也是如此
我们将在下文详细讨论这些类别。
事务之间的相似之处
首先，描述一下 Oracle 和 PostgreSQL 中事务管理的相同方面可能会很有用。幸运的是，许多重要的属性都属于这一类：
· 两种数据库系统都使用多版本控制：读取和写入不会互相阻塞。相反，在更新或删除事务进行时，读取会获得旧数据。
· 两个数据库系统都会持有锁直到事务结束。
· 这两个数据库系统都将行锁存储在行本身上，而不是锁表中。因此，锁定行可能会导致额外的磁盘写入，但无需锁升级。
· 两种数据库系统都支持SELECT ... FOR UPDATE显式并发控制。有关差异的讨论，请继续阅读。
· 两种数据库系统都使用READ COMMITTED默认事务隔离级别，其在两个系统上的行为非常相似。
原子性的比较
这两个数据库的原子性工作方式存在一些细微的差别：
自动提交
在 Oracle 中，任何DML语句都会隐式启动数据库事务，除非已经有一个事务处于打开状态。您必须使用COMMIT或ROLLBACK明确结束这些事务。没有特定的语句来启动事务。
相比之下，PostgreSQL 运行在自动提交（autocommit）模式下：除非你显式地使用 START TRANSACTION 或 BEGIN 开启一个多语句事务，否则每条语句都会在自己的事务中运行。在这种单语句事务结束时，PostgreSQL 会自动执行 COMMIT。
许多数据库API允许您关闭自动提交。由于 PostgreSQL 服务器不支持禁用自动提交，因此客户端会通过自动发送BEGIN适当的内容来模拟它。使用这样的 API，您不必担心差异。
语句级回滚
在 Oracle 中，导致错误的 SQL 语句不会中止事务。相反，Oracle 会回滚失败语句的影响，事务可以继续。要回滚事务，您必须处理错误并主动调用ROLLBACK。
另一方面，如果事务中的 SQL 语句导致错误，PostgreSQL 将中止整个事务。它将忽略中止事务中的所有后续语句，直到您使用ROLLBACK或COMMIT结束事务（两者都将回滚事务）。
大多数编写良好的应用程序都不会遇到这种差异问题，因为您通常希望回滚导致错误的任何事务。但是，PostgreSQL 的行为在某些情况下可能会令人讨厌：想象一下一个长时间运行的批处理作业，其中错误的输入数据可能会导致错误。您可能希望能够处理错误而不必回滚到目前为止所做的一切。在这种情况下，您应该在 PostgreSQL 中使用（符合 SQL 标准的）保存点。这可能需要对您的应用程序进行某些修改。请注意，您应该谨慎使用 PostgreSQL 中的保存点：它们是通过子事务实现的，这会严重影响性能。
事务DDL
在 Oracle 数据库中，任何DDL语句都会自动执行COMMIT，因此无法回滚 DDL 语句。
在 PostgreSQL 中不存在这样的限制。除了少数例外情况（例如 VACUUM、CREATE DATABASE、CREATE INDEX CONCURRENTLY 等），你可以回滚任何 SQL 语句。
一致性比较
这个领域几乎没有差异；Oracle 和 PostgreSQL 都确保没有事务违反约束。
也许值得一提的是，Oracle 允许您使用ALTER TABLE启用和禁用约束。例如，您可以禁用约束，执行违反约束的数据修改，然后使用ENABLE NOVALIDATE启用它（对于主键和唯一约束，只有在DEFERRABLE时才有效）。在 PostgreSQL 中，只有超级用户可以禁用实现外键和可延迟唯一和主键约束的触发器。还需要超级用户来设置session_replication_role = replica，这是禁用此类触发器的另一种方法。
主键和唯一约束的验证时间
以下 SQL 脚本在 Oracle 数据库中运行时没有错误：
CREATE TABLE tab (id NUMBER PRIMARY KEY);
INSERT INTO tab (id) VALUES (1);
INSERT INTO tab (id) VALUES (2);
COMMIT;
UPDATE tab SET id = id + 1;
COMMIT;复制
等效脚本在 PostgreSQL 中会导致错误：
CREATE TABLE tab (id numeric PRIMARY KEY);
INSERT INTO tab (id) VALUES (1);
INSERT INTO tab (id) VALUES (2);
UPDATE tab SET id = id + 1;
ERROR: duplicate key value violates unique constraint "tab_pkey"
DETAIL: Key (id)=(2) already exists.复制
原因是 PostgreSQL（违反 SQL 标准）在每行之后检查约束，而 Oracle 则在语句末尾检查约束。要使 PostgreSQL 的行为与 Oracle 相同，请将约束创建为DEFERRABLE。然后 PostgreSQL 将在语句末尾检查它。
隔离性的比较
这也许是 Oracle 和 PostgreSQL 最大的不同之处。Oracle 对事务隔离的支持相当有限。
事务隔离级别比较
SQL标准定义了四种事务隔离级别：READ UNCOMMITTED（未提交读）、READ COMMITTED（已提交读）、REPEATABLE READ（可重复读）和SERIALIZABLE（可序列化）。然而，与标准通常的详细程度相比，这些隔离级别本身被定义得相当含糊。例如，标准指出，在READ UNCOMMITTED隔离级别下，“脏读”（读取其他事务未提交的数据）是“可能的”，但并不清楚这是否是一个强制要求。
Oracle 仅提供隔离级别READ COMMITTED和SERIALIZABLE。然而，后者是谎言；Oracle 提供快照隔离。例如，以下并发事务都成功（第二个会话缩进）：
CREATE TABLE tab (name VARCHAR2(50), is_highlander NUMBER(1) NOT NULL);
-- start a new serializable transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM tab WHERE is_highlander = 1;
COUNT(*)
----------
0
-- start a new serializable transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM tab WHERE is_highlander = 1;
COUNT(*)
----------
0
-- the count is zero, so let's proceed
INSERT INTO tab VALUES ('MacLeod', 1);
COMMIT;
-- the count is zero, so let's proceed
INSERT INTO tab VALUES ('Kurgan', 1);
COMMIT;复制
没有一种事务的串行执行能够产生相同的结果：在第二种情况下，计数器的值会变成1。
除了不正确之外，Oracle的实现还很粗糙。例如，如果你创建一个表时没有指定SEGMENT CREATION IMMEDIATE，然后尝试在一个SERIALIZABLE事务中插入第一行数据，你会收到一个序列化错误。从技术上讲，这是合法的，因为你必须准备好在更高的隔离级别上接受序列化错误。但似乎Oracle会在某些难以正确实现的情况下（例如索引页分裂）抛出序列化错误。本质上，在Oracle数据库中，SERIALIZABLE几乎是不可用的。
PostgreSQL支持所有四种隔离级别，但它会默默地将READ UNCOMMITTED升级为READ COMMITTED（这是否符合SQL标准可能还有待商榷）。SERIALIZABLE在PostgreSQL中是真正的可序列化的。PostgreSQL的REPEATABLE READ表现得像Oracle的SERIALIZABLE，但它的实际效果要好得多。
并发数据修改的比较（READ COMMITTED）
默认的事务隔离级别READ COMMITTED是低隔离级别。这意味着仍然可能发生许多异常。我在上一篇文章中描述了一种这样的异常。我不想在这里重复整个事情：本质上，情况如下：
· 一个事务已修改表行，但尚未提交
· 第二个事务执行一条尝试锁定行的语句（可能是SELECT ... FOR UPDATE）并挂起
· 第一个事务提交
那么第二个事务会得到什么结果呢？在 Oracle 和 PostgreSQL 中，如果使用，您将看到最新提交的数据READ COMMITTED，但有一个细微的差别：
· PostgreSQL 仅重新评估被锁定的行，这种方式速度很快，但可能会导致结果不一致，如我的文章中所述
· Oracle重新执行整个查询，虽然速度较慢，但能提供一致的结果
持久性比较
两种数据库系统都通过事务日志实现持久性（在Oracle中称为“重做日志（REDO log）”，在PostgreSQL中称为“预写日志（write-ahead log）”）。在这一方面，Oracle和PostgreSQL提供了相同的保证。
事务之间的其他差异
Oracle 和 PostgreSQL 之间的比较还显示出其他一些差异：
事务大小和持续时间的限制
这一领域的差异是由 Oracle 和 PostgreSQL 实现多版本控制的完全不同方式造成的。Oracle 有一个UNDO 表空间，它将修改后的行的旧版本复制到该表空间中，而 PostgreSQL 则在表本身中保留一行的多个版本。
因此，Oracle 事务中的数据修改次数受 UNDO 表空间大小的限制。对于大规模删除或更新，Oracle 的常见做法是分批执行，并在两次删除或COMMIT更新之间进行。在 PostgreSQL 中，没有这样的限制，但大规模更新会使表“膨胀”，因此您可能还希望分批更新（并在两次删除或更新VACUUM之间运行）。但是，在 PostgreSQL 中没有理由限制大规模删除的大小。
长时间运行的数据库事务是每个关系数据库中的问题，因为它们会持有锁并增加阻塞其他会话的机会。长时间运行的事务也更容易出现死锁。在 PostgreSQL 中，长时间运行的事务比在 Oracle 中更成问题，因为它们还会拖延“自动清理”（autovacuum）维护作业的进度。这可能会导致难以修复的表膨胀。
SELECT ... FOR UPDATE比较
两个数据库系统都支持该命令，用于在读取的同时锁定表行。此外，Oracle 和 PostgreSQL 都支持 NOWAIT 和 SKIP LOCKED 子句。PostgreSQL 缺少 WAIT 子句；如果需要实现类似功能，必须动态调整参数 lock_timeout。
最重要的区别在于，在 PostgreSQL 中，如果你打算更新某一行，不应该使用 FOR UPDATE。除非你计划删除一行或修改主键或唯一键列，否则正确的锁定模式是 FOR NO KEY UPDATE。
事物 ID 回绕
事务 ID 回绕仅存在于 PostgreSQL 中。PostgreSQL 的多版本实现通过存储每行的事务 ID来管理行版本的可见性。这些数字由 32 位计数器生成，最终将回绕。PostgreSQL 必须执行特殊的维护操作以避免在回绕期间丢失数据。在事务性很强的系统中，这可能会成为一个需要特别注意和调整的麻烦事。
结论
在大多数方面，Oracle 和 PostgreSQL 中的事务工作方式非常相似。但存在差异，如果您计划迁移到 PostgreSQL，则应该查看这些差异。本文中的比较将帮助您发现此类迁移过程中的潜在问题。
