SET TRANSACTION

Function

SET TRANSACTION sets characteristics of a transaction. Available transaction characteristics include the transaction separation level and transaction access mode (read/write or read only). You can set the local features of the current transaction, the default global transaction features inside a session, or the global transaction features among sessions of the current database.

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. The settings of the global transaction features among sessions of the current database take effect after reconnection.

Syntax

{ SET [ LOCAL | SESSION | GLOBAL ] TRANSACTION|SET SESSION CHARACTERISTICS AS TRANSACTION }
  { ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE | REPEATABLE READ }
  | { READ WRITE | READ ONLY } };

Parameter Description

• 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.

  The SET SESSION TRANSACTION statement takes effect only after sql_compatibility is set to 'B' and b_format_behavior_compat_options is set to set_session_transaction. It is equivalent to SET SESSION CHARACTERISTICS AS TRANSACTION.

• GLOBAL

  Specifies that this command takes effect for global sessions of the current database.

  Application scope: This parameter takes effect in when sql_compatibility is set to 'B'. It takes effect for subsequent sessions.

• SESSION CHARACTERISTICS

  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 SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY in the current transaction.
  • The SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL in a transaction block does not take effect for the current transaction. It takes effect only after the COMMIT operation is performed.

  Value range:

  • READ COMMITTED: Only submitted data is read. It is the default value.
  • 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).

Examples

-- Create and switch to the test database.
gaussdb=# CREATE DATABASE mysql_compatible_db DBCOMPATIBILITY 'B';
gaussdb=# \c mysql_compatible_db

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

-- Set the transaction isolation level and read/write mode of the current session.
-- In mode B (sql_compatibility = 'B'), set b_format_behavior_compat_options to set_session_transaction.
gaussdb=# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
gaussdb=# SET SESSION TRANSACTION READ ONLY;

-- Set the transaction isolation level and read/write mode of all sessions of the current database (sql_compatibility = 'B').
gaussdb=# SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
gaussdb=# SET GLOBAL TRANSACTION READ ONLY;

-- Switch back to the initial database and delete the test database. Replace postgres with the actual database name.
gaussdb=# \c postgres
gaussdb=# DROP DATABASE mysql_compatible_db;