Updated on 2025-05-29 GMT+08:00

SET TRANSACTION

Description

Sets characteristics of a transaction. Available transaction characteristics include the transaction isolation level and transaction access mode (read/write or read only). You can set the current transaction characteristics using LOCAL or the default transaction characteristics of a session using SESSION.

Precautions

The current transaction characteristics must be set in a transaction, that is, START TRANSACTION or BEGIN must be executed before SET TRANSACTION is executed. Otherwise, the setting does not take effect.

Syntax

Set the isolation level and access mode of the transaction.
1
2
3
4
{ SET [ LOCAL | SESSION ] TRANSACTION | SET SESSION CHARACTERISTICS AS TRANSACTION }
  { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
  | { READ WRITE | READ ONLY }
  };

Parameters

  • LOCAL

    Specifies that the specified statement takes effect only for the current transaction.

  • SESSION

    Specifies that the specified parameters take effect for the current session.

  • ISOLATION LEVEL
    Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.
    • The isolation level cannot be changed after data is modified using INSERT, DELETE, UPDATE, FETCH, or COPY in the current transaction.

    Value range:

    • READ COMMITTED: Only committed data is read. This is the default value.
    • READ UNCOMMITTED: After this isolation level is set, its behavior is the same as that of READ COMMITTED.
    • REPEATABLE READ: Only the data committed before transaction start is read. Uncommitted data or data committed in other concurrent transactions cannot be read.
    • SERIALIZABLE: Currently, this isolation level is not supported in GaussDB. It is equivalent to REPEATABLE READ.
  • READ WRITE | READ ONLY

    Specifies the transaction access mode (read/write or read only).

    The access mode of the default transaction feature of the session can be set only when the database is started or by sending the HUP signal.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- Start a transaction.
gaussdb=# START TRANSACTION;

-- Set the isolation level of the transaction to READ COMMITTED and the access mode to READ ONLY.
gaussdb=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;

-- Query the current transaction isolation level.
gaussdb=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

-- Commit the transaction.

gaussdb=# COMMIT;