Manage transactions and isolation levels - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL supports ACID (atomicity, consistency, isolation, and durability) properties across its distributed massively parallel processing (MPP) architecture, ensuring transaction consistency as queries scale horizontally across nodes.

Isolation levels

AnalyticDB for PostgreSQL supports three isolation levels. The default is READ COMMITTED.

READ UNCOMMITTED: Follows standard SQL syntax. However, this isolation level is implemented the same as READ COMMITTED in AnalyticDB for PostgreSQL.
READ COMMITTED (default): Follows standard SQL syntax and is implemented as READ COMMITTED.
SERIALIZABLE: Follows standard SQL syntax. However, this isolation level is implemented the same as REPEATABLE READ in AnalyticDB for PostgreSQL.

Note AnalyticDB for PostgreSQL in Serverless mode supports only the READ COMMITTED isolation level.

Set the isolation level

To start a transaction block with a specific isolation level:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Or set it after BEGIN:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction control statements

AnalyticDB for PostgreSQL supports the following SQL statements for transaction management:

Statement	Description
`BEGIN` or `START TRANSACTION`	Starts a transaction block
`COMMIT` or `END`	Commits the transaction and makes all changes permanent
`ROLLBACK`	Rolls back the transaction and discards all changes
`SAVEPOINT <name>`	Creates a named savepoint within the transaction
`ROLLBACK TO SAVEPOINT <name>`	Rolls back to the savepoint, undoing changes made after it
`RELEASE SAVEPOINT <name>`	Releases a savepoint (changes made after it are kept on commit)

Use savepoints

Savepoints let you roll back part of a transaction without aborting the whole thing.

Roll back to a savepoint

In this example, only values 1 and 3 are inserted. The ROLLBACK TO SAVEPOINT undoes the second INSERT (value 2), but the transaction continues and commits the third INSERT (value 3).

BEGIN;
    INSERT INTO table1 VALUES (1);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (2);
    ROLLBACK TO SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3);
COMMIT;

Release a savepoint

RELEASE SAVEPOINT removes the savepoint marker but does not roll back any changes. All inserts in this example are committed — values 3 and 4 are both inserted.

BEGIN;
    INSERT INTO table1 VALUES (3);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (4);
    RELEASE SAVEPOINT my_savepoint;
COMMIT;