Transaction isolation is foundational to handling concurrent transactions in databases.
The SQL-92 standard defines four levels of transaction isolation (in decreasing order of strictness): Serializable, Repeatable Read, Read Committed, and Read Uncommitted.
YSQL supports Serializable, Snapshot, and Read Committed isolation levels, where Snapshot isolation is the current default.
Read Committed support is currently in Beta and can be enabled by setting the YB-TServer flag yb_enable_read_committed_isolation to true.
We can verify the effective isolation level in YSQL by showing the value of the yb_effective_transaction_isolation_level variable.
Here is an example when the YB-TServer flag yb_enable_read_committed_isolation is set the default value of false.
yugabyte=# SHOW yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
repeatable read
(1 row)
And here is an example when the YB-TServer flag yb_enable_read_committed_isolation is set to true.
yugabyte=# SHOW yb_effective_transaction_isolation_level;
yb_effective_transaction_isolation_level
------------------------------------------
read committed
(1 row)
UPDATE: The yb_effective_transaction_isolation_level variable is depracated in YugabyteDB 2.18. Use the yb_get_effective_transaction_isolation_level() function instead to get the effective isolation level in YSQL.
Example:
yugabyte=# SELECT yb_get_effective_transaction_isolation_level();
yb_get_effective_transaction_isolation_level
----------------------------------------------
read committed
(1 row)
Have Fun!
