Postgresql 中文操作指南

43.8. Transaction Management #

在由 CALL 命令调用的过程中以及在匿名代码块中（DO 命令），可以使用 COMMIT 和 ROLLBACK 命令结束事务。使用这些命令结束事务后会自动开始一个新事务，所以没有单独的 START TRANSACTION 命令。（请注意，BEGIN 和 END 在 PL/pgSQL 中有不同的含义。）

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

这是一个简单的示例：

Here is a simple example:

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

新事务将按照默认事务特征（例如事务隔离级别）启动。在循环中提交事务的情况下，最好自动开始一个具有与前一个相同特性的新事务。COMMIT AND CHAIN 和 ROLLBACK AND CHAIN 命令可以实现这一点。

A new transaction starts out with default transaction characteristics such as transaction isolation level. In cases where transactions are committed in a loop, it might be desirable to start new transactions automatically with the same characteristics as the previous one. The commands COMMIT AND CHAIN and ROLLBACK AND CHAIN accomplish this.

事务控制仅适用于 CALL 或 DO 的调用）或嵌套的 CALL 或 DO 调用，且不包含其他介入命令。例如，如果调用堆栈为 CALL proc1() → CALL proc2() → CALL proc3()，则第二个和第三个过程可以执行事务控制操作。但是，如果调用堆栈为 CALL proc1() → SELECT func2() → CALL proc3()，则最后一个过程无法进行事务控制，因为中间有 SELECT。

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

光标循环具有特殊考量因素。请考虑以下示例：

Special considerations apply to cursor loops. Consider this example:

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

正常情况下，光标会自动在事务提交时关闭。但是，在此类循环中创建的光标会由第一个 COMMIT 或 ROLLBACK 自动转换为可保存光标。这意味着光标在第一个 COMMIT 或 ROLLBACK 时会得到完全评估，而不是逐行评估。光标仍然会在循环后自动移除，所以用户几乎不会注意到。

Normally, cursors are automatically closed at transaction commit. However, a cursor created as part of a loop like this is automatically converted to a holdable cursor by the first COMMIT or ROLLBACK. That means that the cursor is fully evaluated at the first COMMIT or ROLLBACK rather than row by row. The cursor is still removed automatically after the loop, so this is mostly invisible to the user.

光标循环（由非只读命令（例如 UPDATE … RETURNING）驱动）中不允许使用事务命令。

Transaction commands are not allowed in cursor loops driven by commands that are not read-only (for example UPDATE … RETURNING).

事务无法在具有异常处理程序的块中结束。

A transaction cannot be ended inside a block with exception handlers.