AnalyticDB for PostgreSQL supports standard attributes of database transactions and three isolation levels. These attributes include atomicity, consistency, isolation, and durability, which are collectively referred to as ACID. AnalyticDB for PostgreSQL uses a distributed massively parallel processing (MPP) architecture to horizontally scale nodes and ensure transaction consistency between nodes. This topic describes the transaction isolation levels and transaction-related operations supported by AnalyticDB for PostgreSQL.

Isolation levels

AnalyticDB for PostgreSQL provides the following three transaction isolation levels:

    • READ UNCOMMITTED: follows standard SQL syntax. However, this isolation level is implemented the same as the READ COMMITED isolation level in AnalyticDB for PostgreSQL.
    • READ COMMITTED: follows standard SQL syntax and is implemented the same as the READ COMMITED 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.

Example:

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

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

  • BEGIN and START: each start a transaction block.
  • END and COMMIT: each commit a transaction.
  • ROLLBACK: rolls back a transaction with no changes retained.
  • SAVEPOINT: creates a savepoint within a transaction. You can revoke the SQL statements executed after the savepoint was 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.