PolarDB:Transaction management

Overview

In the procedures invoked by the CALL command or anonymous code blocks invoked by the DO command, you can end transactions by using the COMMIT and ROLLBACK commands. A new transaction is automatically started after a transaction is ended by using the preceding commands. Therefore, a separate START TRANSACTION command does not exist. Take note that BEGIN and END have different meanings in PL/SQL.

The following sample code provides a simple example:

    CREATE PROCEDURE transaction_test1()
    IS
    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();

A new transaction starts with the default characteristics, such as the default transaction isolation level. If a transaction is committed in a loop, you may want the system to automatically start a new transaction with the same characteristics as the ended transaction. In this case, you can use the COMMIT AND CHAIN and ROLLBACK AND CHAIN commands

You can perform transaction control only on CALL and DO invocations from the top level or nested CALL and DO invocations without intervening commands. For example, if the invocation stack is CALL proc1() → CALL proc2() → CALL proc3(), the second and third procedures can perform transaction control actions. However, if the invocation stack is CALL proc1() → SELECT func2() → CALL proc3(), the last procedure cannot perform transaction control due to the SELECT command in between.

Special considerations apply to cursor loops. The following sample code provides an example:

    CREATE PROCEDURE transaction_test2()
    IS
    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();

Cursors are automatically closed when a transaction is committed. However, if a cursor is created as part of a loop, the cursor is automatically converted to a holdable cursor by the first COMMIT or ROLLBACK command. In this case, the cursor is fully evaluated at the first COMMIT or ROLLBACK command rather than row by row. The cursor is automatically removed after the loop. Therefore, the cursor is mostly invisible to users.

Transaction commands are not allowed in cursor loops driven by commands that are not read-only, such as the UPDATE ... RETURNING command.

A transaction cannot be ended in a block that contains exception handlers.