Transaction isolation Levels
Yugabyte DB supports two isolation levels.
- Snapshot isolation, also known as SI, which is an transaction isolation level that guarantees that all reads made in a transaction will see a consistent snapshot of the database, and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made by transactions that committed since that snapshot.
- Serializable isolation level, which would guarantee that transactions run in a way equivalent to a serial (sequential) schedule.
Note that transaction isolation level support varies between the APIs.
- The YSQL API supports both Serializable and Snapshot Isolation using the PostgreSQL isolation level syntax of
REPEATABLE READSrespectively. Note that YSQL Serializable support was added in v1.2.6.
- The YCQL API supports only Snapshot Isolation using the
Locks for isolation levels
In order to support these two isolation levels, the lock manager internally supports three types of locks:
Snapshot isolation write lock
This type of a lock is taken by a snapshot isolation transaction on values that it modifies.
Serializable read lock
This type of a lock is taken by serializable read-modify-write transactions on values that they read in order to guarantee they are not modified until the transaction commits.
Serializable write lock
This type of a lock is taken by serializable transactions on values they write, as well as by pure-write snapshot isolation transactions. Multiple snapshot-isolation transactions writing the same item can thus proceed in parallel.
The following matrix shows conflicts between locks of different types at a high level.
|Snapshot Isolation Write||Serializable Write||Serializable Read|
|Snapshot Isolation Write||✘ Conflict||✘ Conflict||✘ Conflict|
|Serializable Write||✘ Conflict||✔ No conflict||✘ Conflict|
|Serializable Read||✘ Conflict||✘ Conflict||✔ No conflict|
Fine grained locking
We make further distinction between locks acquired on a DocDB node that is being written to by any
transaction or read by a read-modify-write serializable transaction, and locks acquired on its
parent nodes. We call the former types of locks “strong locks” and the latter “weak locks”. For
example, if an SI transaction is setting column
col1 to a new value in row
row1, it will
acquire a weak SI write lock on
row1 but a strong SI write lock on
row1.col1. Because of this distinction, the full conflict matrix actually looks a bit more complex:
|Strong SI write||Weak SI write||Strong Serializable write||Weak Serializable write||Strong Serializable read||Weak Serializable read|
|Strong SI write||✘ Conflict||✘ Conflict||✘ Conflict||✘ Conflict||✘ Conflict||✘ Conflict|
|Weak SI write||✘ Conflict||✔ No conflict||✘ Conflict||✔ No conflict||✘ Conflict||✔ No conflict|
|Strong Serializable write||✘ Conflict||✘ Conflict||✔ No conflict||✔ No conflict||✘ Conflict||✘ Conflict|
|Weak Serializable write||✘ Conflict||✔ No conflict||✔ No conflict||✔ No conflict||✘ Conflict||✔ No conflict|
|Strong Serializable read||✘ Conflict||✘ Conflict||✘ Conflict||✘ Conflict||✔ No conflict||✔ No conflict|
|Weak Serializable read||✘ Conflict||✔ No conflict||✘ Conflict||✔ No conflict||✔ No conflict||✔ No conflict|
Here are a couple of examples explaining possible concurrency scenarios from the above matrix:
- Multiple SI transactions could be modifying different columns in the same row concurrently. They acquire weak SI locks on the row key, and strong SI locks on the individual columns they are writing to. The weak SI locks on the row do not conflict with each other.
- Multiple write-only transactions can write to the same column, and the strong serializable write locks that they acquire on this column do not conflict. The final value is determined using the hybrid timestamp (the latest hybrid timestamp wins). Note that pure-write SI and serializable write operations use the same lock type, because they share the same pattern of conflicts with other lock types.