Last modified: August 22, 2025

This article is written in: 🇺🇸

Serializable and Repeatable Read in Database Systems

Transaction isolation levels are essential for maintaining data integrity and managing concurrency in database systems. Two of the highest isolation levels are Serializable and Repeatable Read, each offering different guarantees to prevent anomalies that can occur when multiple transactions interact with the same data concurrently.

After reading the material, you should be able to answer the following questions:

  1. What is the Serializable isolation level, and how does it ensure complete isolation of transactions in a database system?
  2. How does the Repeatable Read isolation level differ from Serializable, and what types of data anomalies does it prevent?
  3. In what scenarios would you choose to use Serializable isolation over Repeatable Read, and why?
  4. What are phantom reads, and how does the Serializable isolation level prevent them compared to Repeatable Read?
  5. What are the performance implications of using higher isolation levels like Serializable, and how can applications balance consistency with system performance?

The Need for Isolation Levels

In environments where multiple transactions execute at the same time, issues like dirty reads, non-repeatable reads, and phantom reads can arise. Without proper isolation, one transaction might read data that another transaction is modifying, leading to inconsistent or incorrect results. Isolation levels define how transactions are isolated from one another to prevent these problems.

Serializable Isolation Level

The Serializable isolation level is the strictest level, ensuring that transactions are completely isolated from each other. It guarantees that the outcome of executing transactions concurrently is the same as if they were executed sequentially in some order.

Time Transaction T1 Transaction T2
T1 BEGIN TRANSACTION
SELECT SUM(balance) FROM accounts;
(Total = \$10,000)
T2 BEGIN TRANSACTION
INSERT INTO accounts (id, balance)
VALUES (101, \$1,000);
T3 (Blocked: waiting for T1 to complete)
T4 COMMIT
T5 (Unblocked) INSERT completes
COMMIT

In this scenario:

Repeatable Read Isolation Level

The Repeatable Read isolation level ensures that if a transaction reads a row, it will see the same data throughout the transaction, even if other transactions modify it. However, it doesn't prevent new rows (phantoms) from being inserted by other transactions.

Time Transaction T1 Transaction T2
T1 BEGIN TRANSACTION
SELECT * FROM orders WHERE customer_id = 1;
(Returns 5 rows)
T2 BEGIN TRANSACTION
INSERT INTO orders (order_id, customer_id) VALUES (101, 1);
COMMIT
T3 SELECT * FROM orders WHERE customer_id = 1;
(Returns 5 rows)
T4 COMMIT

In this example:

Comparing Serializable and Repeatable Read

Both isolation levels aim to maintain data consistency but differ in their handling of concurrent transactions and the types of anomalies they prevent.

Serializable Isolation Level:

Repeatable Read Isolation Level:

Practical Examples and Commands

To set the isolation level to Serializable:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Transaction operations here
COMMIT;

To set the isolation level to Repeatable Read:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
-- Transaction operations here
COMMIT;

When to Use Each Isolation Level

Choosing between Serializable and Repeatable Read depends on the specific needs of your application.

Balancing Performance and Consistency

Higher isolation levels like Serializable provide greater data integrity but can impact performance due to increased locking and decreased concurrency. Lower isolation levels improve performance but may expose the application to data anomalies.

Understanding Phantom Reads

Here’s a revised section that fixes the table layout and adds an explicit Database State column, plus a clear narrative of what each transaction is trying to achieve and what actually happens:

Understanding Phantom Reads

Phantom reads happen when Transaction T1 re-executes a query and sees new rows inserted by another transaction (T2), despite using Repeatable Read.

Time Transaction T1 (T1’s view) Transaction T2 Database State (Electronics)
T1 BEGIN TRANSACTION
– Reads count of Electronics products:
COUNT = 10		 10 rows (IDs: 101–110)
T2 BEGIN TRANSACTION
– Inserts a new Electronics product:
INSERT (201,'Electronics')
COMMIT		 11 rows (IDs: 101–110, 201)
T3 Re-reads same query:
SELECT COUNT(*) ... = 11
(Phantom row!)		 11 rows (IDs: 101–110, 201)
T4 COMMIT Final state preserved: 11 rows

What T1 intended: T1 began under Repeatable Read to get a stable snapshot of “all Electronics” and expected any re-reads to still return 10. Its purpose might be to calculate inventory before placing a bulk order or generating a report.

What actually happened: Because T2 committed an insert of a new Electronics product before T1 re-ran its SELECT, T1’s second read sees 11 rows. That extra “phantom” row wasn’t visible on the first read, breaking T1’s expectation of repeatability.

Strategies to Prevent Phantom Reads

If phantom reads pose a problem, consider using the Serializable isolation level or implementing additional locking mechanisms.

Using Serializable Isolation Level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Transaction operations
COMMIT;

Under Serializable, T1 would not see the new product inserted by T2 during its transaction.

Table of Contents

    Serializable and Repeatable Read in Database Systems
    1. The Need for Isolation Levels
    2. Serializable Isolation Level
    3. Repeatable Read Isolation Level
    4. Comparing Serializable and Repeatable Read
    5. Practical Examples and Commands
    6. When to Use Each Isolation Level
    7. Balancing Performance and Consistency
    8. Understanding Phantom Reads
    9. Understanding Phantom Reads
    10. Strategies to Prevent Phantom Reads