SET TRANSACTION
Synopsis
Use the SET TRANSACTION statement to set the current transaction isolation level.
Syntax
set_transaction ::= SET TRANSACTION transaction_mode [ ... ]
transaction_mode ::= isolation_level
| read_write_mode
| deferrable_mode
isolation_level ::= ISOLATION LEVEL { READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE }
read_write_mode ::= READ ONLY | READ WRITE
deferrable_mode ::= [ NOT ] DEFERRABLE
set_transaction
transaction_mode
isolation_level
read_write_mode
deferrable_mode
Semantics
Supports Serializable, Snapshot and Read Committed Isolation$ using the PostgreSQL isolation level syntax of SERIALIZABLE, REPEATABLE READ and READ COMMITTED respectively. PostgreSQL's READ UNCOMMITTED also maps to Read Committed Isolation.
$ Read Committed Isolation is supported only if the gflag yb_enable_read_committed_isolation is set to true. By default this gflag is false and in this case the Read Committed isolation level of Yugabyte's transactional layer falls back to the stricter Snapshot Isolation (in which case READ COMMITTED and READ UNCOMMITTED of YSQL also in turn use Snapshot Isolation).
transaction_mode
Set the transaction mode to one of the following.
ISOLATION LEVELclause- Access mode
DEFERRABLEmode
ISOLATION LEVEL clause
SERIALIZABLE
Default in ANSI SQL standard.
REPEATABLE READ
Maps to Snapshot Isolation of YugabyteDB.
READ COMMITTED
Default in PostgreSQL and YSQL.
If yb_enable_read_committed_isolation=true, READ COMMITTED is mapped to Read Committed of YugabyteDB's transactional layer (i.e., a statement will see all rows that are committed before it begins). But, by default yb_enable_read_committed_isolation=false and in this case Read Committed of YugabyteDB's transactional layer falls back to the stricter Snapshot Isolation.
Essentially this boils down to the fact that Snapshot Isolation is the default in YSQL.
READ UNCOMMITTED
READ UNCOMMITTED maps to Read Committed of YugabyteDB's transactional layer (note that Read Committed in the transactional layer might in turn map to Snapshot Isolation if yb_enable_read_committed_isolation=false).
In PostgreSQL, READ UNCOMMITTED is mapped to READ COMMITTED.
READ WRITE mode
Default.
READ ONLY mode
The READ ONLY mode does not prevent all writes to disk.
When a transaction is READ ONLY, the following SQL statements are:
-
Disallowed if the table they would write to is not a temporary table.
- INSERT
- UPDATE
- DELETE
- COPY FROM
-
Always disallowed
- COMMENT
- GRANT
- REVOKE
- TRUNCATE
-
Disallowed when the statement that would be executed is one of the above
- EXECUTE
- EXPLAIN ANALYZE
DEFERRABLE mode
Use to defer a transaction only when both SERIALIZABLE and READ ONLY modes are also selected. If used, then the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to, or being canceled by a serialization failure.
The DEFERRABLE mode may be useful for long-running reports or back-ups.
Examples
Create a sample table.
yugabyte=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
Begin a transaction and insert some rows.
yugabyte=# BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
yugabyte=# INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (1, 3.0, 4, 'b');
Start a new shell with ysqlsh and begin another transaction to insert some more rows.
yugabyte=# BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
yugabyte=# INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3, 'a'), (2, 3.0, 4, 'b');
In each shell, check the only the rows from the current transaction are visible.
1st shell.
yugabyte=# SELECT * FROM sample; -- run in first shell
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
2nd shell
yugabyte=# SELECT * FROM sample; -- run in second shell
k1 | k2 | v1 | v2
----+----+----+----
2 | 2 | 3 | a
2 | 3 | 4 | b
(2 rows)
Commit the first transaction and abort the second one.
yugabyte=# COMMIT TRANSACTION; -- run in first shell.
Abort the current transaction (from the first shell).
yugabyte=# ABORT TRANSACTION; -- run second shell.
In each shell check that only the rows from the committed transaction are visible.
yugabyte=# SELECT * FROM sample; -- run in first shell.
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
yugabyte=# SELECT * FROM sample; -- run in second shell.
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)