What are Isolation Levels?
Isolation levels control what one transaction can see when other transactions are running at the same time. They determine how much transactions are isolated from each other.
When multiple users access the database simultaneously, problems can occur. Isolation levels are the solution.
The Problem: Concurrent Access
Imagine two people trying to buy the last item in stock at the same time.
Both users saw stock = 1 and both tried to buy. This should not happen.
Three Common Problems
Isolation levels prevent these three problems:
1. Dirty Read Reading data that another transaction has not yet committed. If that transaction rolls back, you read data that never existed.
2. Non-Repeatable Read You read a row, another transaction changes it, you read again and get different data.
3. Phantom Read You run a query, another transaction adds rows, you run the same query and get more rows.
The Four Isolation Levels
SQL defines four isolation levels, from least strict to most strict.
1. Read Uncommitted
The lowest isolation level. A transaction can see changes made by other transactions even before they commit.
What can happen:
- Dirty reads: Yes
- Non-repeatable reads: Yes
- Phantom reads: Yes
Real-world example: You check your bank balance while a transfer is in progress. You see the money added, but the transfer fails and rolls back. The money was never really there.
When to use: Almost never. Only for rough estimates where accuracy does not matter.
2. Read Committed
A transaction can only see changes that other transactions have committed. This is the default level in most databases.
What can happen:
- Dirty reads: No
- Non-repeatable reads: Yes
- Phantom reads: Yes
Real-world example: You read a product price of 100 dollars. Another user changes it to 120 dollars and commits. You read again and see 120 dollars. The value changed during your transaction.
When to use: General purpose applications. Good balance of safety and performance.
3. Repeatable Read
Once you read a row, you will always see the same data for that row during your transaction, even if other transactions change it.
What can happen:
- Dirty reads: No
- Non-repeatable reads: No
- Phantom reads: Yes (in some databases)
Real-world example: You are generating a report. You read a customer's orders. Even if they place a new order during your report generation, your report stays consistent with what you initially read.
When to use: Reports, data analysis, anywhere you need consistent reads.
4. Serializable
The highest isolation level. Transactions run as if they were executed one after another, not concurrently.
What can happen:
- Dirty reads: No
- Non-repeatable reads: No
- Phantom reads: No
Real-world example: Two people try to book the last seat on a flight. With Serializable, one completes their booking before the other even starts. No double booking is possible.
When to use: Financial transactions, inventory management, anywhere data accuracy is critical.
How to Set Isolation Level
-- Set for the next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- Your transaction here
SELECT * FROM accounts WHERE user_id = 1;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;Different databases have slightly different syntax:
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- MySQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;Real-world Example: Bank Transfer
For a bank transfer, you want the highest safety.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Check balance
SELECT balance FROM accounts WHERE user_id = 1;
-- Result: 500
-- Ensure sufficient funds and transfer
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;With Serializable, no other transaction can interfere with this transfer.
Trade-offs
Higher isolation = More safety but slower performance
Read Uncommitted: Fastest, but dangerous Read Committed: Fast, safe for most uses Repeatable Read: Slower, very consistent Serializable: Slowest, maximum safety
Most applications use Read Committed. Only increase the level when you have specific requirements for data consistency.
Summary
Isolation levels control transaction visibility:
- Read Uncommitted: See everything, even uncommitted changes (risky)
- Read Committed: See only committed changes (default)
- Repeatable Read: Data you read stays the same
- Serializable: Transactions run one at a time (safest)
Choose based on your needs:
- Most applications: Read Committed
- Reports and analytics: Repeatable Read
- Financial and critical data: Serializable