Transaction isolation is a fundamental concept for managing concurrent transactions in databases. The SQL-92 standard specifies four levels of transaction isolation, ranked from most to least strict: Serializable, Repeatable Read, Read Committed, and Read Uncommitted.
YSQL supports the isolation levels Serializable, Snapshot (i.e. Repeatable Read), and Read Committed.
Read Committed isolation is supported only when the YB-TServer flag yb_enable_read_committed_isolation is set to true. By default, this flag is false, causing Read Committed isolation in YugabyteDB’s transactional layer to default to the stricter Snapshot isolation.
Consequently, the default isolation level for the YSQL API is effectively Snapshot, as the YSQL and PostgreSQL syntactic default of Read Committed is mapped to Snapshot isolation.
What if you want to default to Snapshot while allowing users the flexibility to switch to Read Committed?
No problem! You simply have to set the following gFlags:
- yb_enable_read_committed_isolation=true
- ysql_default_transaction_isolation=’repeatable read’
Now, when I log into the database, the default isolation level will be Snapshot, but I can easily switch it to Read Committed if needed.
Example:
[root@cloud-server-0 yb2]# alias y
alias y='ysqlsh -h $(hostname -I)'
[root@cloud-server-0 yb2]# y -c "SELECT yb_get_effective_transaction_isolation_level();"
yb_get_effective_transaction_isolation_level
----------------------------------------------
repeatable read
(1 row)
[root@cloud-server-0 yb2]# y -c "SET default_transaction_isolation='read committed'" -c "SELECT yb_get_effective_transaction_isolation_level();"
SET
yb_get_effective_transaction_isolation_level
----------------------------------------------
read committed
(1 row)
Have Fun!
