SET TRANSACTION

Sets the characteristics of the current transaction.

Summary

SET TRANSACTION [transaction_mode] [READ ONLY | READ WRITE]

SET TRANSACTION SNAPSHOT snapshot_id

SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode 
     [READ ONLY | READ WRITE]
     [NOT] DEFERRABLE

where transaction_mode is one of the following:

ISOLATION LEVEL {SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}

describe

The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions.

Available transaction characteristics are transaction isolation level, transaction access mode (read/write or read-only), and delayable modes.

Note: A delayable transaction requires that the transaction be serializable. The YMatrix database does not support serializable transactions, so including the DEFERRABLE clause is invalid. The YMatrix database does not support the SET TRANSACTION SNAPSHOT command.

The isolation level of a transaction determines what data the transaction can see when other transactions run at the same time.

READ COMMITTED — A statement can only see lines submitted before starting. This is the default value.
REPEATABLE READ — All statements in the current transaction can only see rows submitted before the first query statement executed in the transaction or modified by the data modification statement in the transaction.

The SQL standard defines two additional levels, namely READ UNCOMMITTED and SERIALIZABLE. In the YMatrix database, READ UNCOMMITTED is considered as READ COMMITTED. If SERIALIZABLE is specified, the YMatrix database will fall back to REPEATABLE READ.

The transaction isolation level cannot be changed after the first query or data modification statement of a transaction is executed (SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY).

Transaction access mode determines whether a transaction is read/write or read-only. Read/write is the default setting. When the transaction is read-only, the following SQL commands are not allowed: INSERT, UPDATE, DELETE, and COPY FROM (if the table to be written is not a temporary table); all CREATE, ALTER and DROP commands; GRANT, REVOKE, TRUNCATE; if the commands to be executed by EXPLAIN ANALYZE and EXECUTE are not allowed in the above commands, they are not allowed. This is a high-level concept of read-only and does not block all writes to disk.

The DEFERRABLE transaction attribute is invalid unless the transaction is both SERIALIZABLE and READ ONLY. When all these properties are set on the transaction, the transaction may block when it first takes its snapshot, after which it can run without the normal overhead of the SERIALIZABLE transaction and there is no risk of serialization failure or being cancelled by serialization failure. Since the YMatrix database does not support serializable transactions, the DEFERRABLE transaction attribute is invalid in the YMatrix database.

Parameters

SESSION CHARACTERISTICS

Set default transaction characteristics for subsequent transactions of the session.

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

The SQL standard defines four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.
READ UNCOMMITTED allows transactions to view changes made by uncommitted concurrent transactions. This is not possible in the YMatrix database, so READ UNCOMMITTED is the same as READ COMMITTED.
READ COMMITTED is the default isolation level in the YMatrix database, ensuring that statements can only see rows submitted before they begin. If another concurrent transaction is submitted after the first execution of the statement, the same statement executed twice in one transaction may produce different results.
REPEATABLE READ isolation level ensures that transactions can only see rows committed before the transaction begins. REPEATABLE READ is the strictest level of transaction isolation supported by YMatrix databases. Due to serialization failure, applications using REPEATABLE READ isolation level must be prepared to retry the transaction.
ERIALIZABLE transaction isolation level ensures that all statements of the current transaction can only see rows submitted by the data modification statement before the first query is executed in this transaction or within this transaction. If the read and write modes between concurrent serializable transactions will cause any serial (one at a time) execution of such transactions to be impossible, one of the transactions will be rolled back and a serialization_failure error occurs. The YMatrix database does not fully support the standard-defined SERIALIZABLE, so if SERIALIZABLE is specified, the YMatrix database will fall back to REPEATABLE READ.

READ WRITE

READ ONLY

Determine whether a transaction is read/write or read-only. Read/write is the default setting. When the transaction is read-only, the following SQL commands are not allowed: INSERT, UPDATE, DELETE, and COPY FROM (if the table to be written is not a temporary table); all CREATE, ALTER and DROP commands; GRANT, REVOKE, TRUNCATE; If the commands to be executed by EXPLAIN ANALYZE and EXECUTE are not allowed in the above commands, they are not allowed.

[NOT] DEFERRABLE

Because SERIALIZABLE transactions are not supported, the DEFERRABLE transaction attribute is invalid in the YMatrix database. If DEFERRABLE is specified and the transaction is both SERIALIZABLE and READ ONLY, the transaction may block when it first takes its snapshot, after which it can run without the normal overhead of the SERIALIZABLE transaction and there is no risk of posing or being cancelled serializable failure. Any contributions or failures to be deserialized by them. This mode is ideal for long-running reports or backups.

Note

If SET TRANSACTION is executed without START TRANSACTION or BEGIN in advance, a warning will be issued and the command is invalid.

SET TRANSACTION can be omitted by specifying the required transaction mode in BEGIN or START TRANSACTION.

You can also set the default transaction mode of the session by setting the configuration parameters default_transaction_isolation, default_transaction_read_only and default_transaction_deferrable.

Example

Set the transaction isolation level of the current transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

compatibility

Both commands are defined in the SQL standard. SERIALIZABLE is the default transaction isolation level in the standard. In the YMatrix database, the default value is READ COMMITTED. The YMatrix database does not fully support the SERIALIZABLE level due to the lack of predicate locking, so when SERIALIZABLE is specified, it falls back to the REPEATABLE READ level. Essentially, the predicate locking system prevents phantom reading by restricting what is written, while the multi-version concurrency control model (MVCC) used in the YMatrix database prevents phantom reading by restricting what is read.

PostgreSQL provides a true serializable isolation level called serializable snapshot isolation (SSI), which monitors concurrent transactions and rolls back transactions that may introduce serialized exceptions. This isolation mode is not implemented in the YMatrix database.

In the SQL standard, you can set other transaction characteristics using the following command: the size of the diagnostic area. This concept is specific to embedded SQL and is therefore not implemented in the YMatrix database server.

The DEFERRABLE transaction mode is an extension of the YMatrix database language.