In this article, we go through a quick and high-level overview of transaction isolation level by looking at an example of data integrity phenomena, the need for locks, and how transaction level helps optimize read operations and their trade-offs.

Data Integrity Phenomenon

Imagine a scenario where Soji performs a financial transaction that clears a loan of 100 naira for Ige.

First, Soji initiates transaction A to retrieve the amount owed, which is 100 naira. Concurrently, Ige is also trying to clear that loan and initiate transaction B to do the same thing at the same time.

Soji then sends 100 naira to Iges’ account, which sets the account balance to zero naira locally.
Concurrently, Ige also sends 50 naira and sets the balance locally at 50 naira.

Both Soji and Ige then send the request to the database to update the record value. Olasoji’s transaction updates the new balance to 0, and after Ige’s transaction updates the new balance to 50.

Olasoji just lost 100 naira. Conversely, Ige would have lost 50 naira.

Locks

To prevent such data integrity phenomena, we need to create barriers that allow transactions to be processed one at a time and enqueue new requests coming in.

Locks help to guarantee that requests are processed serially. Using a lock in the data phenomenon example would have guaranteed that either Ige’s or Soji’s request would be first, while the order would be second.

Locks help guarantee atomicity, isolation, and consistency.

Transaction Isolation Level

This brings us to the first transaction isolation level:

SERIALIZABLE

The serializable transaction isolation prevents all data integrity phenomena by guaranteeing that inserts, updates, deletes, or reads happen serially.
Processing one request at a time would slow down the server. Read requests coming together would return the same result; it would be better if they were all processed concurrently.
To implement this optimization, we need to separate the locks into two: write locks (exclusive) and read locks (shared).
Write locks allow one request to be processed at a time, while read locks block write operations and allow multiple read operations to be processed concurrently.

Serializable Request Schedule

acquires write locks for records 1, 2, 3, and 4.
Try the operation.
finally release write locks for records 1, 2, 3, and 4.

Which brings us to the second transaction isolation level:

REPEATABLE_READ

The repeatable-read transaction isolation level acquires all read locks for records being read and releases them once the request is done with its operation.
Notice that once all the locks are acquired, new records can be inserted, meaning repeating the operation could return new records. This phenomenon is known as phantom-read.

Repeatable-Read Request Schedule

acquires read locks for records 1, 2, 3, and 4.
Try the operation.
finally release read locks for records 1, 2, 3, and 4.

READ_COMMITTED

The read-committed transaction isolation level is similar to the repeatable read transaction isolation level only that instead of acquiring all locks early in the operation, read-committed transaction isolation acquires them on demand and releases them early once it is done.
Some data integrity issues found when using the read-committed transaction isolation level are non-repeatable reads, lost updates, and phantom reads.

Read-Committed Request

acquires read locks for records 1
Try the operation that needs Record 1.
finally release read locks for records 1.

acquires locks for records 2
Try the operation that needs Record 2.
finally release locks for records 2.

...

READ_UNCOMMITTED

The read-uncommitted transaction isolation level acquires no read locks and can access data that is being altered by other ongoing transactions.
All data integrity phenomena are experienced at this level.

Multi-version Locking

So far, we have talked about pessimistic locking. Multi-version locking is similar to CopyOnWriteArray and can be used as an alternative to read uncommitted.

Understanding Transaction Isolation Level

Table of contents