Transaction Isolation Levels in DBMS

Transaction isolation levels in databases control the visibility of data changes made by one transaction to other concurrent transactions. They are designed to manage the effects of concurrent access and ensure data consistency.

There are 4 isolation levels in SQL standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

These levels are designed to balance the need for concurrency and performance with the need for consistency and accuracy.

Why do we need these transaction isolation levels?

Dirty Reads: A dirty read occurs when one transaction reads data that has been modified by another transaction but not yet committed. This can lead to incorrect results if the other transaction is rolled back.
Non-Repeatable Reads: A non-repeatable read occurs when a transaction reads the same data multiple times and gets different results each time. This can happen if another transaction modifies the data between the reads.
Phantom Reads: A phantom read occurs when a transaction reads a set of rows that satisfy a certain condition, but another transaction inserts or deletes rows that also satisfy the condition. This can lead to unexpected results if the second transaction is committed.

1. Read Uncommitted

The lowest isolation level. Transactions can see uncommitted changes made by other transactions (known as “dirty reads”).

Rarely used in practice due to the risk of inconsistent data.

Example with Stock Price Updates

Situation: A stock trading application allows users to view stock prices in real-time. One transaction is updating the price of a stock, but it hasn’t been committed yet.
Problem: Another transaction reads the uncommitted price change. If the first transaction rolls back (e.g., due to an error), the second transaction has read an incorrect price.
Outcome: Users see incorrect stock prices, leading to potential financial losses or confusion.

2. Read Committed (Default)

Transactions can only see changes that have been committed by other transactions. This prevents dirty reads but allows non-repeatable reads and phantom reads.

This is the default isolation level in most databases.

Example with Bank Account Balance

Situation: A user checks their bank account balance twice during a transaction. Between the two reads, another transaction (e.g., a withdrawal) commits a change to the balance.
Problem: The user sees two different balances in the same session, even though no money was added or removed by them.
Outcome: While this prevents dirty reads, the non-repeatable read can confuse users or cause issues in applications that rely on consistent data.

3. Repeatable Read

Transactions can only see changes that have been committed by other transactions. This prevents dirty reads and non-repeatable reads but allows phantom reads.

Example with Inventory Management

Situation: An e-commerce platform checks the quantity of a product in stock twice during a transaction. Between the two reads, another transaction updates the stock quantity.
Problem: Without repeatable read, the second read might show a different stock quantity, leading to incorrect decisions (e.g., overselling).
Outcome: With repeatable read, the same quantity is returned in both reads, ensuring consistency. However, if a new product is added to the inventory (phantom read), it might not be visible.

4. Serializable

The highest isolation level. Transactions are completely isolated from each other, preventing dirty reads, non-repeatable reads, and phantom reads.

This level provides the highest level of consistency but can also lead to performance issues due to increased locking.

Example: Flight Booking System

Situation: Two users are trying to book the last seat on a flight at the same time. The system must ensure that only one booking succeeds.
Problem: Without serializable isolation, both transactions might read that the seat is available and proceed to book it, leading to overbooking.
Outcome: Serializable isolation ensures that transactions are executed as if they were sequential, preventing overbooking and maintaining data integrity.