Database Transaction Isolation Levels


Anomalies
Before looking at transaction isolation levels, I think it is important to understand the three main anomalies that can occur when multiple transactions execute concurrently.
-
Dirty read and dirty writes: A transaction reads data written by another transaction before that transaction commits. The second transaction might eventually roll back, meaning the first transaction has read data that was never actually committed. Dirty writes (resolved by current db engines at any isolation level). A dirty write occurs when one transaction overwrites data that has been modified by another transaction but has not yet committed.
-
Non-repeatable read: Transaction one reads the same row twice but gets different values because transaction two has updated and committed that row between the two reads.
-
Phantom read: Transaction one runs the same query twice but gets additional or missing rows because transaction two has inserted or deleted records matching the query's conditions.
Transaction Isolation Levels
Database systems provide different transaction isolation levels to control how concurrent transactions interact. The SQL standard defines four isolation levels, ordered from weakest to strongest.
Each level provides stronger guarantees against concurrency anomalies, usually at the cost of reduced concurrency or additional overhead.
1. Read Uncommitted
This is the weakest isolation level. Transactions can read data modified by other transactions even if those changes haven't been committed meaning that dirty reads, non-repeatable reads and phantom reads are all possible.
For example:
I. Balance is £500. Transaction A updates a customer's account balance from £500 to £100.
II. Transaction B reads the account balance and sees £100.
III. Transaction A rolls back.
IV. The actual balance is still £500, but transaction B has already read a value that was never committed.
This is a good example of a dirty read, is nasty.
2. Read Committed
Read Committed prevents dirty reads by ensuring that transactions can only see committed but it doesn't guarantee that repeated reads within the same transaction return the same result.
For example:
I. Transaction A reads a customer's account balance: £500.
II. Transaction B updates the balance to £300 and commits.
III. Transaction A reads the same account balance again and gets £300.
Both reads are valid because transaction B committed its change, but transaction A has observed two different values within the same transaction. This is known as a non-repeatable read. Phantom reads are also possible.
3. Repeatable Read
Repeatable Read guarantees that if a transaction reads the same row multiple times, it will see the same values throughout its execution. This prevents both dirty reads and non-repeatable reads but phantom reads may still be possible.
For example: I. Transaction A queries all customers with a balance greater than £1,000 and gets 10 rows. II. Transaction B inserts another customer with a balance of £2,000 and commits. III. Transaction A executes the same query again and gets 11 rows.
The additional row is a phantom read. An important detail is that database implementations differ. For example, PostgreSQL's Repeatable Read implementation also prevents phantom reads through snapshot isolation.
4. Serializable
Serializable is the strongest standard isolation level.
It guarantees that concurrent transactions produce results equivalent to some serial execution, meaning the transactions behaved as if they had executed one after another. This prevents dirty reads, non-repeatable reads and phantom reads but it comes with a trade-off. The database may need to block transactions or abort some of them when it detects conflicting operations. For example, two transactions attempting to update related account balances concurrently might not both be allowed to commit. One may need to retry.
Serializable provides the strongest consistency guarantees, but it will introduce additional contention and transaction retries, hurting the performance.
Multi-Version Concurrency Control or MVCC
Most modern relational databases use some form of Multi-Version Concurrency Control (MVCC) to manage concurrent transactions. Instead of making readers wait for writers, MVCC maintains multiple versions of a record. A transaction can read an appropriate version of the data without necessarily blocking another transaction that is updating it.
For example, PostgreSQL uses MVCC to provide consistent snapshots of data. When a transaction updates a row, the database creates a new row version rather than immediately overwriting the version visible to existing transactions. This improves concurrency because readers and writers can operate simultaneously in many situations. MVCC itself doesn't guarantee serializable execution. The isolation level determines what snapshot a transaction sees and how conflicting operations are handled.
Snapshot Isolation and Write Skew
Snapshot isolation is an important concept when studying transaction isolation because it is stronger than basic Read Committed but doesn't necessarily provide full serializability.
Under snapshot isolation, each transaction reads from a consistent snapshot of the database. This prevents dirty reads and non-repeatable reads, but it can still allow an anomaly known as write skew. Consider a hospital where at least one doctor must remain on call. Two doctors are currently on call, but always has to be one on duty, the invariant.
- Transaction A checks that two doctors are on call.
- Transaction B checks the same condition and also sees two doctors on call.
- Transaction A takes Doctor 1 off call.
- Transaction B takes Doctor 2 off call.
- Both transactions commit.
Each transaction made a valid decision based on its snapshot. However, the final state violates the invariant, the business rule because no doctors are left on call. This is write skew.
Serializable isolation prevents this kind of anomaly by ensuring that the outcome is equivalent to a valid serial execution.
Takeaway
Choosing an isolation level is a trade-off between consistency and concurrency. Read Committed is often sufficient for many applications, while Repeatable Read provides stronger guarantees for transactions that need consistent reads. Serializable should be considered when correctness depends on complex relationships between concurrent operations and weaker isolation levels cannot guarantee the required business rules.
Thank you if you made it so far.