五月 22, 2023
在从 Oracle 迁移到 PostgreSQL 时，遇到 Oracle 自治事务是很常见的。本文解释了自治事务以及如何在 PostgreSQL 中获得此功能。
数据持久化用例
数据库事务提供了一种捆绑一组 SQL 语句的机制，以便提交所有 SQL 语句或回滚所有语句。例如，假设在将资金从账户 A 转移到账户 B 时，您需要执行以下步骤：
- 从账户 A 中减去所需金额
- 将所需金额添加到账户 B
在此方案中，您需要确保两个步骤都成功完成或均不成功。您可以通过在数据库事务中运行这两个 SQL 语句来实现此目的。这是一个非常有用的功能。大多数用例都需要它。但是，在某些情况下，即使事务失败，应用程序也需要能够保留某些数据。
PostgreSQL 和 Oracle 都遵循相同的事务原则，但为了处理此用例，Oracle 提供了一个名为自治事务的功能。PostgreSQL 没有直接等价的自治事务，但您可以使用子事务获得类似的结果。
什么是自治事务？
Oracle 提供自治事务功能。该功能使子程序能够执行 SQL 操作并提交或回滚这些操作，而无需提交或回滚主事务。
请考虑以下方案：
- 必须根据插入触发器中的业务逻辑将包含一些关键信息的行添加到表中。
- 执行插入 SQL 查询以添加所需的行。
- 如果触发器因任何原因失败，则包括此插入在内的所有操作都将回滚。
如果要求插入成功，而不管主事务是否成功完成，则可以在自治事务中调用插入 SQL 语句。下面是一个具有自主事务的 Oracle 过程示例：
CREATE OR REPLACE PROCEDURE insert_critical_info(v_critical_info varchar2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO critical_info_table (critical_info) VALUES (v_critical_info);
commit;
END;
/
通过此更改，插入是独立提交的，并且不依赖于主事务的成功完成。
除了上述方案之外，自治事务还用于审核访问。例如，从触发器或存储过程中调用自治事务，并将审核信息作为参数。自治事务存储并提交此信息，而不管原始事务是否成功完成。
PostgreSQL 中的自治事务
PostgreSQL 没有明确支持自治事务。我们建议使用子事务进行重构，以消除使用自治事务的要求。
子事务具有的特点：单个会话运行多个独立事务，就好像多个不同的会话正在执行每个事务。
行为
子事务只能在一个现有的事务中发生。
在现有事务（称为 T0）中，用户可以决定启动子事务。然后 T0 暂停并推送到事务堆栈中， 并启动一个新事务（称为 T1）。
在将来的某个时候，用户可以提交子事务；提交 T1 后，T0 将从事务堆栈中弹出，并且恢复。
用户还可以决定提交父事务 T0，这时 T1 会被提交，然后从事务堆栈中弹出 T0，然后提交。
所有事务同步发生；任何时候只有一个事务可以是活动的，而在堆栈中保持有若干个（零个或更多）已暂停的事务。
T0 和 T1 的所有可能组合都可以发生；例如，可以提交 T1 和回滚 T0。
可以嵌套子事务，最高可达全局资源限制 （例如事务堆栈大小），该限制可在服务器上设置。
示例 1（一个子事务）
下图描述了一个事务执行子事务的示例。连续线表示活动事务，而虚线表示活动交易 表示已在堆栈中暂停和推送的事务。时间从上往下进行。
BEGIN (start ordinary tx T0);
|
INSERT INTO t VALUES (1);
:\
: BEGIN SUBTRANSACTION (start AST tx T1, pushes T0 into stack);
: |
: INSERT INTO t VALUES (2);
: |
: COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T1, pops tx T0 from stack);
:/
COMMIT / ROLLBACK; (ends tx T0)
取决于 COMMIT 还是 ROLLBACK，我们可以得到 4 种不同的结果：
SELECT sum(x) from t;
示例 2（多个子事务）
父事务可以有多个子事务，只需重复应用推送/弹出事务的逻辑即可。
BEGIN (start ordinary tx T0);
|
INSERT INTO t VALUES (1);
:\
: BEGIN SUBTRANSACTION (start AST tx T1, pushes T0 into stack);
: |
: INSERT INTO t VALUES (2);
: |
: COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T1, pops tx T0 from stack);
:/
|
:\
: BEGIN SUBTRANSACTION (start AST tx T2, pushes T0 into stack);
: |
: INSERT INTO t VALUES (4);
: |
: COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T2, pops tx T0 from stack);
:/
COMMIT / ROLLBACK; (ends tx T0)
注意
如果我们对下一个语句是什么有额外的了解，例如当 T0 在 PL/PGSQL 函数中执行时，可能会有不同的语义。 在这种情况下，行为可能是子事务的提交立即启动新的子事务，并且父事务 T0 仅在函数返回时从堆栈中弹出。 然后，该函数等效于一系列事务 T1，T2,…都是 T0 的子事务，无需从 T1 和 T2 之间、T2 和 T3 之间的堆栈中弹出/推送 T0，等等。
可见性
可见性规则的工作方式与多会话分别执行独立事务的情况相同。T1 看不到 T0 的影响，因为后者尚未提交。T0 可能会看到 T1 的影响， 取决于其自身的事务隔离模式。
现在，可能会发生单会话的死锁，因为子事务可以与其会话中暂停的事务之一纠缠在一起。
警告
子事务打破了在开始和结束一个事务的过程中，同一会话中没有其他活跃事务的假设；这在事务访问会话级资源的场景（例如，使用 ON COMMIT DROP / DELETE ROWS 的临时表）中增加了发生冲突的可能性 。
使用 exception 实现自治事务
在 PostgreSQL 的 PL/pgSQL 函数中，可以使用一个带有EXCEPTION子句的BEGIN块俘获错误并且从中恢复。每个异常处理的代码块就是一个子事务，我们可以使用异常处理代码块，实现和 Oracle 类似的自治事务。
CREATE OR REPLACE FUNCTION insert_critical_info(v_critical_info varchar)
RETURNS void AS $$
DECLARE
BEGIN
INSERT INTO critical_info_table (critical_info) VALUES (v_critical_info);
EXCEPTION WHEN others THEN -- 此处可以捕获任何具体的异常
RAISE NOTICE 'insert_critical_info error';
END;
$$ LANGUAGE plpgsql;