Summarized transaction isolation levels!

Hello! I'm Kenji Kawata, and I've been working at GROWTH VERSE for about 1.5 years.
Recently, I wanted to deepen my understanding of transaction isolation levels, so I studied the topic on my own and summarized what I learned in this tech blog to share the knowledge with others.
I hope this article helps you understand the concept of isolation levels in transactions.
Feel free to leave any questions or comments!
You can read this article of Japanese version from here

Table of Contents

• What Is a Transaction
• What Are the ACID Properties
• Transaction Isolation Levels
• How Repeatable Read Prevents Phantom Reads
• Summary

What Is a Transaction

A transaction is a concept that treats a series of operations on a database as a single unit of work. It ensures that either all operations succeed or none of them are applied.For example, consider a transaction involving two bank accounts: withdrawing 10,000 yen from user A’s account and depositing 10,000 yen into user B’s account. The system must guarantee that both operations succeed or both fail — partial success is not acceptable.

Without transaction mechanisms, there is a risk of data inconsistency if an error or system failure occurs during the process. This situation is called as data inconsistency.To prevent this, transactions are essential for maintaining data consistency.

What Are the ACID Properties

ACID properties consist of four key characteristics that ensure data consistency and reliability in transaction processing.ACID stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity

Atomicity ensures that either all operations in a transaction are completed successfully or none of them are applied.If any error occurs during the process, the system rolls back to the state before the transaction began.

Consistency

A transaction ensures that the database always remains in a consistent state.
For example, a bank account balance should never become a negative value as a result of a deposit.

Isolation

A transaction ensures that when multiple transactions are executed at the same time, they do not interfere with each other.
For example, if multiple transactions attempt to update the same data concurrently, isolation ensures that data integrity is maintained and inconsistencies are avoided.

Durability

Once a transaction is committed, its results are permanently recorded in the database, even in the event of a system failure.
This guarantees the durability and reliability of the data.

Transaction Isolation Levels

A transaction isolation level is a setting used to manage data inconsistency issues when multiple transactions are executed concurrently.
The ACID properties mention both consistency and isolation, but in practice, it's rare to fully guarantee consistency and completely isolate all transactions.
The isolation level that achieves this strict separation is called SERIALIZABLE.

If you want to ensure complete consistency and full isolation, the system must process transactions one by one, without executing them concurrently.
However, this approach results in very poor performance because transactions cannot be processed in parallel.
There is a trade-off between performance and consistency, so it's important to understand your consistency requirements and choose the appropriate isolation level accordingly.

Transaction Isolation Levels

There are four types of transaction isolation levels.
From top to bottom below, each level represents a higher degree of isolation.

• READ UNCOMMITTED
  • Allows reading uncommitted data from other transactions
  • This is the lowest isolation level among all levels
  • It prioritizes performance over consistency
• READ COMMITTED
  • Only committed data from other transactions can be read
  • This is the default isolation level in many databases such as PostgreSQL
  • Commonly used in general business processes; it offers a good balance between consistency and performance
• REPEATABLE READ
  • A query returns the same result within the same transaction, ensuring row-level consistency
  • Consistency is maintained via row locks or snapshot mechanisms
  • Often used when you need consistent results for repeated queries, such as in batch processing
• SERIALIZABLE
  • The highest isolation level
  • Results in lower throughput due to increased locking or conflict detection overhead
  • Suitable for highly sensitive operations like bank transactions or inventory management, where strong consistency is critical

The table below summarizes the relationship between isolation levels and the problems associated with each level.

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	occurring	occurring	occurring
READ COMMITTED	not occurring	occurring	occurring
REPEATABLE READ	not occurring	not occurring	it may occur
SERIALIZABLE	not occurring	not occurring	not occurring

The following explanation summarizes each type of problem related to transaction isolation.

Dirty Read

This occurs when uncommitted changes made by one transaction are read by another transaction.

Example

1. Transaction A withdraws 10,000 yen from user A’s bank account and updates the balance
2. Transaction B reads user A’s bank account and sees the updated balance
3. Transaction A is rolled back due to an error. Therefore, transaction B has read an invalid balance

Non-repeatable Read

This occurs when different results are returned when reading the same data multiple times within the same transaction, due to modifications by another transaction.

Example

1. Transaction A reads the balance of user A’s bank account (100,000 yen)
2. Transaction B deposits 20,000 yen into user A’s account (balance becomes 120,000 yen)
3. Transaction A reads the balance again and gets 120,000 yen — a different result than before

Phantom Read

This occurs when new data inserted by another transaction causes the same query to return different results within the same transaction.
Example

1. Transaction A queries bank accounts with balances over 1,000,000 yen and finds 5 accounts
2. Transaction B creates a new account and deposits 2,000,000 yen
3. Transaction A runs the same query again and now finds 6 accounts

In my opinion, I personally don’t find non-repeatable reads or phantom reads to be serious issues, since I’m used to PostgreSQL’s READ COMMITTED isolation level.
However, it's important to understand that high data consistency is crucial in many systems.
I hope this helps you understand the difference:

• Non-repeatable reads occur when the same data returns different results due to updates by other transactions
• Phantom reads occur when the same query returns different results because new data was inserted by other transactions

How Repeatable Read Prevents Phantom Reads

By the way, MySQL InnoDB uses REPEATABLE READ as its default isolation level, and it also prevents phantom reads.
This is achieved through two mechanisms.

Record Snap Shot by MVCC(MultiVersion Concurrency Control)

InnoDB provides a snapshot of the data at the beginning of the transaction.
Each transaction reads only the data visible in its snapshot and ignores any new rows inserted by other transactions after it started.

START TRANSACTION;
SELECT * FROM orders WHERE amount > 100;
-- A new row is inserted and committed by another transaction
SELECT * FROM orders WHERE amount > 100;
-- The new row is not visible due to MVCC
COMMIT;

MVCC is also used to implement repeatable reads, but whether it also prevents phantom reads depends on the specific database implementation.
To prevent phantom reads completely, range locking is required — this is explained in the next section.

Row Locking to Prevent Phantom Reads (Range Locking)

To completely prevent phantom reads caused by newly inserted rows, you need to use range locking.
A normal SELECT statement is not enough — you must use statements like SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE.
This ensures that no other transaction can insert new rows within the specified range, thereby completely preventing phantom reads.

START TRANSACTION;
SELECT * FROM orders WHERE amount > 100 FOR UPDATE;
-- In the meantime, another transaction cannot insert new rows within the amount > 100 range.
COMMIT;

Summary

Thank you for reading!