SET TRANSACTION
Function
SET TRANSACTION 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.
{ SET [ LOCAL ] 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.
Value range: a string. It must comply with the naming convention.
ISOLATION_LEVEL
Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.
NOTE: The isolation level cannot be changed after data is modified using SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY in the current transaction.
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 openGauss. 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.
openGauss=# START TRANSACTION;
openGauss=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
openGauss=# COMMIT;