Consistency Phenomena and Isolation Levels (a note to myself)

In ACID, Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level is defined by the following phenomena:

Dirty Read (A::Write R1 > B::Read R1 > A::Rollback)

Scenario with Transaction A & B running concurrently:

• A executes a query Q and inserts row R1
• B reads R1
• A decides to rollback, thus R1 no longer exists but B holds it!

Non-Repeatable Read (A::Select -> B::Update -> A::Select)

Scenario with Transaction A & B running concurrently:

• A executes a query Q and gets these rows: R1, R2
• B changes row R1 to RX
• A executes the query Q and will get these rows: RX, R2

Phantom Read (A::Select -> B::Insert/Delete -> A::Select)

This is very similar to non-repeatable read but instead of Update, transaction B changes rows with Insert and/or Delete.

Scenario with Transaction A & B running concurrently:

• A executes a query Q and gets these rows: R1, R2
• B inserts row R3
• A executes the query Q and get these rows: R1, R2, R3

Based on these phenomena, The SQL standard defines four isolation levels:

1. Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
2. Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.
3. Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.
4. Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

And this is the relationship between ISOLATION and above phenomena:

All the credits to: