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 current transaction characteristics using LOCAL or the default transaction characteristics of a session using SESSION.

Precautions

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

Syntax

{ SET [ LOCAL | SESSION ] 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 transaction.

• SESSION

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

• ISOLATION LEVEL
  Specifies a transaction isolation level, which determines what data is visible to a transaction 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 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 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).

  

  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

-- 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;