Updated on 2025-09-22 GMT+08:00

Transactions

In a database management system, a transaction is the smallest logical unit of operation. It consists of a finite sequence of database operations that are typically executed between the start and end of a transaction. These operations are either all successfully executed or none of them are executed, making it an indivisible unit of execution.

When a transaction is committed, the database management system must ensure that all operations in the transaction are successfully completed. If any operation in the transaction fails, the system rolls back the database to the status before the transaction starts. The following are the four characteristics—ACID—of a transaction:
  • Atomicity (A) indicates that a transaction is composed of an indivisible unit of work. Operations performed in a transaction must be all finished or have not been performed.
  • Consistency (C) indicates that transactions must always keep the system in a consistent state.
  • Isolation (I) ensures that concurrently executed transactions are isolated from each other and do not interfere with each other.
  • Durability (D) indicates that once a transaction is committed, changes to the database must be permanently stored in the database.

Transaction Isolation Levels

  • Dirty read, phantom read, and non-repeatable read are described as follows:
    • Dirty read: occurs when a transaction reads data that another transaction has not yet committed.
    • Phantom read: happens when repeated queries within the same transaction return different row counts due to insertions or deletions by other transactions.
    • Non-repeatable read: arises when multiple reads of the same data within a transaction yield different results because another transaction has updated that data.
  • The isolation levels of GaussDB have the following features:
    • READ COMMITTED: default isolation level. A transaction can read only data that another transaction has committed, avoiding dirty reads. However, phantom reads and non-repeatable reads may still occur due to data changes between reads.
    • REPEATABLE READ: Consistent data can be queried throughout the transaction, avoiding dirty reads and non-repeatable reads.
    • SERIALIZABLE: Currently, GaussDB does not support this isolation level. Setting this isolation level is equivalent to REPEATABLE READ.

Transaction Control Statements

  • START TRANSACTION: starts a transaction. If the isolation level and read/write mode are specified, the new transaction uses these features. This statement is optional.
  • BEGIN: starts a transaction. If the isolation level and read/write mode are specified, the new transaction uses these features. This statement is optional.
    • If the system variable autocommit is set to off in a session, the automatic transaction commit function is disabled. In this case, the BEGIN or START TRANSACTION statement is not required to identify a transaction consisting of multiple SQL statements.
    • If the system variable autocommit is set to on in a session, the automatic transaction commit function is enabled. In this case, each SQL statement is an independent transaction. To group multiple SQL statements into a single transaction, run the BEGIN or START TRANSACTION command to start a transaction.
  • SAVEPOINT: creates a savepoint in the current transaction. A savepoint is a special mark in a transaction. It allows you to roll back all commands executed after it was established, restoring the transaction state to the moment of the savepoint.
  • COMMIT|END: commits and ends the current transaction, makes all modifications persistent and take effect, and clears all savepoints to release the transaction lock.
  • ROLLBACK: rolls back all modifications performed in a transaction and clears all savepoints to release the transaction lock.
  • ROLLBACK TO SAVEPOINT: rolls back to a savepoint and implicitly destroys all savepoints that were established after the named savepoint.

Syntax

  • START TRANSACTION
    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION
      [ 
        { 
           ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
           | { READ WRITE | READ ONLY }
         } [, ...] 
      ];
    
  • BEGIN
    1
    2
    3
    4
    5
    6
    7
    BEGIN [ WORK | TRANSACTION ]
      [ 
        { 
           ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
           | { READ WRITE | READ ONLY }
          } [, ...] 
      ];
    
  • SAVEPOINT
    SAVEPOINT savepoint_name;
  • ROLLBACK TO SAVEPOINT
    ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name;

Examples

-- Create a table.
gaussdb=# CREATE TABLE test1(c1 INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

-- Start a transaction with the isolation level being READ COMMITTED and the access mode being READ WRITE:
gaussdb=# START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
START TRANSACTION

-- Insert data.
gaussdb=# INSERT INTO test1 (c1) VALUES (1);
INSERT 0 1

-- Create a savepoint.
gaussdb=# SAVEPOINT my_savepoint;
SAVEPOINT

-- Insert data.
gaussdb=# INSERT INTO test1 (c1) VALUES (2);
INSERT 0 1

-- Roll back to the savepoint.
gaussdb=# ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK

-- View data. You can see that the data has been rolled back to the state before the savepoint.
gaussdb=# SELECT * FROM test1;
 c1 
----
  1
(1 row)
-- The isolation level is READ COMMITTED and the transaction has not ended. Therefore, no data is displayed in the table after a new session is created.
gaussdb=# SELECT * FROM test1;
 c1 
----
(0 rows)

-- Insert data.
gaussdb=# INSERT INTO test1 (c1) VALUES (3);
INSERT 0 1


-- End the transaction.
gaussdb=# COMMIT;
COMMIT

-- View data. The transaction ends, and the data can be queried in another session.
gaussdb=# SELECT * FROM test1;
 c1 
----
  3
  1
(2 rows)

-- Drop the table.
gaussdb=# DROP TABLE test1;
DROP TABLE