SET TRANSACTION

Description

Sets the transaction characteristics. Transaction characteristics include transaction isolation level and transaction access mode (read/write or read-only). You can set the default transaction feature among sessions or the global transaction feature among sessions of the current database.

Precautions

The settings of the global transaction characteristics among sessions of the current database take effect after reconnection. When m_format_dev_version is set to 's2', SET TRANSACTION sets the next transaction feature by default and does not allow the next transaction feature to be used within a transaction. If an implicit transaction error is reported, that is, a single SQL statement reports an error, the next transaction feature is retained.

Syntax

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

Parameters

• LOCAL

  Specifies that the command takes effect only for the current session. The function is the same as that of SESSION.

• SESSION

  Specifies that the command takes effect only for the current session.

• GLOBAL

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

• ISOLATION LEVEL
  Specifies the transaction isolation level, which determines the data that the transaction can view if there are other concurrent transactions.

  

  The isolation level cannot be changed after data is modified using SELECT, INSERT, DELETE, UPDATE, or COPY in the current transaction.

  Value range:

  • READ COMMITTED: Only committed data can be read. This is the default.
  • 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 the transaction starts can be read. Uncommitted data or data committed in other concurrent transactions cannot be read.
  • SERIALIZABLE: Currently, this isolation level is not supported in an M-compatible database. It is equivalent to REPEATABLE READ.
• READ WRITE | READ ONLY

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

Examples

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

-- Set the transaction isolation level and access mode for the current session.
m_db=# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
m_db=# SET SESSION TRANSACTION READ ONLY;

-- Set the transaction isolation level and access mode globally for sessions of the database.
m_db=# SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
m_db=# SET GLOBAL TRANSACTION READ ONLY;

-- Set m_format_dev_version to 's2' and set the next transaction isolation level and read/write mode.
m_db=# SET m_format_dev_version='s2';
m_db=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

-- Set m_format_dev_version to 's2' and do not allow the next transaction feature to set within a transaction.
m_db=# SET m_format_dev_version='s2';
m_db=# START TRANSACTION;
m_db=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY; -- ERROR:  Transaction characteristics can't be changed while a transaction is in progress.
m_db=# COMMIT;