DBMS_TRANSACTION - PolarDB - Alibaba Cloud Documentation Center

DBMS_TRANSACTION is a built-in package compatible with Oracle syntax that provides fine-grained control over transactions inside PL/SQL program blocks — stored procedures and functions. The savepoint feature lets you roll back part of a transaction without discarding all changes.

Supported subprograms

PolarDB for PostgreSQL (Compatible with Oracle) supports four subprograms from the Oracle DBMS_TRANSACTION package:

Subprogram	Description
`SAVEPOINT(savepoint_name)`	Marks a savepoint in the current transaction
`ROLLBACK_SAVEPOINT(savepoint_name)`	Rolls back to the specified savepoint, undoing operations after it
`COMMIT()`	Commits the current transaction, making all changes permanent
`ROLLBACK()`	Rolls back the entire transaction, undoing all uncommitted changes

PolarDB supports only these four subprograms. Other Oracle DBMS_TRANSACTION subprograms — including BEGIN_WORK and SET_TRANSACTION — are not supported.

Prerequisites

Before you begin, ensure that you have:

Oracle syntax compatibility 2.0 enabled
Minor engine version 2.0.14.17.34.0 or later

To check your minor engine version, go to the console or run:

SHOW polardb_version;

If your version does not meet this requirement, upgrade the minor engine version.

Usage notes

To use SAVEPOINT and ROLLBACK_SAVEPOINT inside PL/SQL blocks, first enable the polar_default_pl_stmt_transaction_rollback parameter. Without it, savepoint operations within PL/SQL are not supported.
To use COMMIT or ROLLBACK inside EXECUTE IMMEDIATE, first enable the polar_enable_commit_in_execute_immediate parameter.
COMMIT and ROLLBACK end the current transaction. To run more statements afterward, start a new transaction.
Avoid overusing SAVEPOINT — each savepoint adds transaction overhead. Plan savepoint granularity based on your actual rollback requirements.

SAVEPOINT

Marks a savepoint in the current transaction. You can roll back to this point later without discarding the entire transaction.

Syntax

DBMS_TRANSACTION.SAVEPOINT(savepoint_name VARCHAR2);

Parameters

Parameter	Description
`savepoint_name`	The name of the savepoint.

Example

DECLARE
BEGIN
    -- Insert test data
    INSERT INTO test_table (id, name) VALUES (1, 'Alice');

    -- Set a savepoint
    DBMS_TRANSACTION.SAVEPOINT('sp1');

    -- Perform an operation that might fail
    INSERT INTO test_table (id, name) VALUES (2, 'Bob');

    -- Commit the transaction
    COMMIT;
END;

After the first insert, sp1 marks the savepoint. If the second insert fails, call ROLLBACK_SAVEPOINT('sp1') to undo only the failed operation. The final COMMIT makes all remaining changes permanent.

ROLLBACK_SAVEPOINT

Rolls back the transaction to a specified savepoint, undoing all operations performed after that point.

Syntax

DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(savepoint_name VARCHAR2);

Parameters

Parameter	Description
`savepoint_name`	The name of the savepoint to roll back to.

Example

DECLARE
BEGIN
    -- Insert the first record
    INSERT INTO test_table (id, name) VALUES (1, 'Alice');

    -- Set a savepoint
    DBMS_TRANSACTION.SAVEPOINT('sp1');

    -- Insert the second record (this might fail)
    INSERT INTO test_table (id, name) VALUES (2, 'Bob');

    -- Roll back to savepoint sp1
    DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('sp1');

    -- Commit the transaction
    COMMIT;
END;

After the rollback to sp1, the record for 'Bob' is discarded. The final COMMIT saves only the record for 'Alice'.

COMMIT

Commits the current transaction, writing all changes permanently to the database.

Syntax

DBMS_TRANSACTION.COMMIT();

Example

DECLARE
BEGIN
    -- Insert two records
    INSERT INTO test_table (id, name) VALUES (1, 'Alice');
    INSERT INTO test_table (id, name) VALUES (2, 'Bob');

    -- Commit the transaction
    DBMS_TRANSACTION.COMMIT();
END;

If you do not call COMMIT explicitly, the transaction is automatically committed when the program block ends. This behavior depends on your database configuration.

ROLLBACK

Rolls back the entire transaction, undoing all uncommitted changes.

Syntax

DBMS_TRANSACTION.ROLLBACK();

Example

DECLARE
BEGIN
    -- Insert the first record
    INSERT INTO test_table (id, name) VALUES (1, 'Alice');

    -- Simulate an error
    IF 1 = 1 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Manually triggered error');
    END IF;

    -- Commit the transaction (will not be executed)
    DBMS_TRANSACTION.COMMIT();
EXCEPTION
    WHEN OTHERS THEN
        -- Roll back the entire transaction
        DBMS_TRANSACTION.ROLLBACK();
        DBMS_OUTPUT.PUT_LINE('Transaction has been rolled back');
END;

The EXCEPTION handler catches the error and calls ROLLBACK() to discard all uncommitted changes. No records are added to test_table.