AnalyticDB for PostgreSQL provides atomicity, consistency, isolation, and durability (ACID) properties and three isolation levels. AnalyticDB for PostgreSQL uses a distributed massively parallel processing (MPP) architecture to horizontally scale nodes and ensure transaction consistency among nodes. This topic describes the transaction isolation levels and transaction-related operations supported by AnalyticDB for PostgreSQL.

Isolation levels

AnalyticDB for PostgreSQL supports the following transaction isolation levels. The default isolation level is READ COMMITTED.

  • READ UNCOMMITTED: follows standard SQL syntax. However, this isolation level is implemented the same as the READ COMMITTED isolation level in AnalyticDB for PostgreSQL.
  • READ COMMITTED: follows standard SQL syntax and is implemented the same as the READ COMMITTED isolation level in AnalyticDB for PostgreSQL.
  • SERIALIZABLE: follows standard SQL syntax. However, this isolation level is implemented the same as the REPEATABLE READ isolation level in AnalyticDB for PostgreSQL.
Note AnalyticDB for PostgreSQL in Serverless mode supports only the READ COMMITTED isolation level.

Example:

Execute the following statements to start a transaction block with the SERIALIZABLE isolation level: 
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
or
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

AnalyticDB for PostgreSQL provides the following SQL statements for you to manage transactions:

  • BEGIN or START: starts a transaction block.
  • END or COMMIT: commits a transaction.
  • ROLLBACK: rolls back a transaction without changes.
  • SAVEPOINT: creates a savepoint within a transaction. You can revoke the SQL statements executed after the savepoint is created.
  • ROLLBACK TO SAVEPOINT: rolls back a transaction to a savepoint.
  • RELEASE SAVEPOINT: releases a savepoint from a transaction.

Examples:

Execute the following statements to create a savepoint in a transaction and revoke the SQL statements executed after the savepoint is created: 
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;

In this example, the values 1 and 3 are inserted, but the value 2 is not.

Execute the following statements to create a savepoint in a transaction and then release the savepoint: 
BEGIN;
    INSERT INTO table1 VALUES (3);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (4);
    RELEASE SAVEPOINT my_savepoint;
COMMIT;

In this example, the values 3 and 4 are inserted.