Transaction Isolation Levels

Transaction Isolation Levels: Read Uncommitted

A transaction isolation level is the read guarantee a database gives when two transactions run in parallel. Data consistency covers a generic stream of operations. An isolation level is limited to the data inside transactions.

A transaction starts with a begin statement and ends with a commit. The commit makes its operations stick, which makes the transaction durable. A transaction is also atomic: either all of its operations execute or none do, and they are rolled back.

Read uncommitted is the loosest guarantee. The database keeps a single copy of the data, and every read takes the latest value. It is fast, because there is no read or write contention.

The drawback shows up when a transaction fails:

1. T1 writes 50 to key 2, which held 17.
2. T2 reads key 2 and gets 50.
3. T1 fails and is rolled back. A write reversal sets key 2 to 17.
4. No later read of the database returns 50.

T2 has seen a value that never happened.

Transaction Isolation Levels: Read Committed

Read committed lets a transaction read only the committed pieces of data in the database.

The database does not keep a single copy of the data. Each transaction keeps its own copy of what it writes:

1. T1 writes 50 to key 2. The value goes into T1's copy. The original table still holds 12 and 17.
2. T2 writes 50 to key 1 in its own copy.
3. T2 reads key 2 from the table and gets 17.
4. T1 reads key 1 before T2 commits and gets 12.
5. If T2 commits first, its copy goes through to the table and T1 reads 50.

This is a higher level of isolation than read uncommitted. Higher isolation is usually slightly slower and more expensive.

A movie hall with a capacity of 50 stops booking at 50 committed transactions. Counting uncommitted ones could leave 48 people and two vacant seats after a rollback. A count of likes on a post can return 55 and then 54. It trades isolation for efficiency.

Transaction Isolation Levels: Repeatable Read

Repeatable read guarantees that repeated reads of the same key inside one transaction return the same value.

Read committed does not give that guarantee:

1. T2 reads key 2 and gets 17.
2. T1 writes 50 to key 2 and commits.
3. T2 reads key 2 again and gets 50, the latest committed value.

Two consecutive reads of one key gave different values. A transaction that makes decisions on a key it reads and then writes can be messed up by this.

Under repeatable read the transaction behaves as if a snapshot of the entire database is taken when it begins. It works on that copy and changes the database on commit. The 17 in the snapshot is a committed value, though not the latest one. The implementation is called snapshot isolation.

When two concurrent transactions write the same key, one with 25 and one with 50, the contention is resolved by rejecting the value that comes second, or by rolling back a transaction. This is optimistic concurrency control: the transaction is optimistic that the value will not change.

Transaction Isolation Levels: Serializable

Serializable is the highest transaction isolation level. Either all the operations run in a serial manner, or every operation is ensured not to meddle with another transaction's operation. A read of the sum of the values between 1 and 2 puts a lock on all the values between 1 and 2.

It is inefficient. It is used when phantoms are not acceptable. A phantom appears like this:

1. T1 reads the sum of the two values and gets 29.
2. T2 writes 50 and then 25 for key 1 in its local copy, and commits. The value in the table changes from 12 to 25.
3. T1 reads the sum again. It sees 25 plus 17, which is 42.

Two reads of the same aggregation query in one transaction gave different results. An insert of a new key at the same time has the same effect: the key is a phantom to the query. It is as if an additional 13 seats were booked out of nowhere.

Executing the transactions sequentially makes sure there are no phantoms.

Transaction Level Implementations

Each isolation level is implemented by a different way of storing a value.

1. Read uncommitted needs a single entry in the database, which is overridden whenever there is an update.
2. Read committed keeps a local copy of the changed values. The committed value stays in the database, and the newer value stays in the local copy till the commit goes through. There is one master copy and a copy of changed values for each transaction.
3. Repeatable read keeps a version of the values a transaction reads and does not change. Every key stores all the values it has had across commits. A transaction keeps seeing the version from when it began, and a new transaction is given the next version.
4. Serializable needs causal ordering. Transactions that have an entry in common are put in order in a queue, and each waits for the ones before it. A read with no relation to the others runs in a separate queue.

Serializable is slow, and the data is very consistent.

Transaction Isolation Levels: Conclusion

A database offers four transaction isolation levels: read uncommitted, read committed, repeatable read and serializable.

Down this order the isolation level increases, and as it increases the efficiency decreases. Serializable gives the highest level of isolation for the lowest level of efficiency. Read uncommitted is the highest level of efficiency. Efficiency goes up from 4 to 3 to 2 to 1.

The choice depends on the application. Some applications go for serializable. Most applications run on repeatable read.

Isolation levels are very similar to the consistency levels, but they are specific to transactions: how transactions behave, and how the data inside them behaves, when it is worked on simultaneously.