• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. SET TRANSACTION

SET TRANSACTION

Function

SET TRANSACTION sets the characteristics of the current transaction. It has no effect on any subsequent transactions. Available transaction characteristics include the transaction separation level and transaction access mode (read/write or read only).

Precautions

None

Syntax

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

Parameter Description

  • LOCAL

    Indicates that the specified command takes effect only for the current transaction.

  • SESSION

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

    Value range: a string compliant with the naming convention.

  • ISOLATION_LEVEL_CLAUSE
    Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.
    NOTE:
    • The isolation level of a transaction cannot be reset after the first clause (INSERT, DELETE, UPDATE, FETCH) for modifying data is executed in the transaction.

    Value range:

    • READ COMMITTED: Only submitted data can be read. This is the default.
    • READ UNCOMMITTED: Unsubmitted data is probably read. This isolation level is provided to handle CN breakdown emergencies. On this isolation level, you are advised to only read data to prevent inconsistency.
    • REPEATABLE READ: Currently, the isolation level is not supported in DWS. It is equivalent to READ COMMITTED.
    • SERIALIZABLE: Currently, the isolation level is not supported in DWS. It is equivalent to READ COMMITTED.
  • READ WRITE | READ ONLY

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

Example

-- Start a transaction and set its isolation level to READ COMMITTED and access mode to READ ONLY.
START TRANSACTION;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
COMMIT;