Transaction isolation levels

Transaction isolation is foundational to handling concurrent transactions in databases. The SQL-92 standard defines four levels of transaction isolation, SERIALIZABLE, REPEATABLE READ, READ COMMITTED and READ UNCOMMITTED in decreasing order of strictness.

YugabyteDB supports three transaction isolation levels - Read Committed, Serializable (both map to the SQL isolation level of the same name) and Snapshot (which maps to the SQL isolation level REPEATABLE READ). Thus, YugabyteDB supports the three strictest of the above four isolation levels. These isolation levels are described below:

• Snapshot Isolation 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.
• Read Committed Isolation guarantees that each statement will see all data that has been committed before it is issued (note that this implicitly also means that the statement will see a consistent snapshot). Also, this isolation level internally handles read restart and conflict errors. In other words, the client doesn't see read restart and conflict errors (barring an exception). Details are in the READ COMMITTED isolation level section.
• Serializable Isolation guarantees that transactions run in a way equivalent to a serial (sequential) schedule.

Note that transaction isolation level support differs between the YSQL and YCQL APIs.

• YSQL supports Serializable, Snapshot and Read Committed^$ isolation levels (the PostgreSQL isolation level syntax of SERIALIZABLE, REPEATABLE READ and READ COMMITTED map to these three respectively).
• YCQL supports only Snapshot Isolation using the BEGIN TRANSACTION syntax.

^$ 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).

Internal locking in DocDB

In order to support these three 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 (and also read committed) 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.