SQL Isolation Levels

47
1

Introduction

A database often handles several transactions at once. While one transaction updates an account, another may read its balance or use it to make a decision. The database must decide which changes each transaction can see.

SQL isolation levels define the guarantees for these interactions. Stronger isolation prevents more concurrency problems, but it can also lead to additional waiting, checks, or transaction retries.

What Is an Isolation Level?

An isolation level controls how a transaction interacts with changes made by other transactions.

A transaction groups related operations into one unit of work. COMMIT makes its changes permanent, while ROLLBACK cancels its uncommitted changes.

The four standard isolation levels are:

  1. Read Uncommitted

  2. Read Committed

  3. Repeatable Read

  4. Serializable

Isolation is not the same as access permission. It controls concurrent transaction behavior, not which users may access a table.

Example Table and Common Problems

The examples use an Account table containing an account identifier, holder name, and balance.

Account: initial data

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

AccountID is the primary key. T1 and T2 represent separate transactions.

The main problems discussed with isolation levels are:

Problem

Meaning

Example

Dirty read

Reading another transaction’s uncommitted change.

T1 reads Asha’s balance as 800.00 before T2 commits. T2 later rolls back.

Non-repeatable read

Reading the same row twice and seeing another transaction’s committed change.

T1 reads 1000.00, then reads 1200.00 after T2 commits an update.

Phantom read

Repeating a search and getting a different set of matching rows.

A search for balances of at least 700 returns an additional account after T2 inserts and commits it.

Serialization anomaly

The combined result cannot be explained by any serial order of the transactions.

Two transactions make decisions from the same old data and together break a shared rule.

The examples assume the reading transaction does not change the data itself. A transaction can normally see its own changes, even before committing.

Read Uncommitted

Read Uncommitted is the weakest standard isolation level. It permits dirty reads, meaning a transaction may see another transaction’s unfinished changes.

Suppose T2 changes Asha’s balance to 800.00 without committing:

Step

T1

T2

1

Updates Asha’s balance to 800.00.

2

May read 800.00.

Has not committed.

3

Rolls back, restoring 1000.00.

T1 may have used a value that never became committed data.

MySQL supports this behavior at Read Uncommitted. PostgreSQL does not allow dirty reads: requesting Read Uncommitted in PostgreSQL gives Read Committed behavior.

This level is unsuitable when decisions depend on reliable committed values. It also does not mean that every database operation runs without locks.

Read Committed

Read Committed prevents a transaction from reading another transaction’s uncommitted changes.

In MySQL and PostgreSQL, an ordinary SELECT uses a snapshot of committed data for that statement. A later statement can use a newer snapshot.

For example:

Step

T1

T2

1

Reads Asha’s balance as 1000.00.

2

Changes the balance to 1200.00 and commits.

3

Reads Asha’s balance again and gets 1200.00.

Both values were committed when read. There is no dirty read, but there is a non-repeatable read.

Read Committed can also allow phantom reads. It is PostgreSQL’s default isolation level, although server and session settings can change the default.

Repeatable Read

Repeatable Read prevents dirty reads and non-repeatable reads.

For ordinary reads in an explicit Repeatable Read transaction, MySQL and PostgreSQL use a stable snapshot. After the first read establishes that snapshot, later reads do not pick up changes committed by other transactions.

Using the same example:

Step

T1

T2

1

Reads Asha’s balance as 1000.00.

2

Changes the balance to 1200.00 and commits.

3

Reads Asha’s balance again and still gets 1000.00.

T1 is reading an older committed version, not an uncommitted value.

The SQL standard permits phantom reads at Repeatable Read, but implementations may offer stronger guarantees. PostgreSQL prevents them at this level. MySQL’s ordinary consistent reads also use a stable snapshot, while locking reads and writes follow additional rules.

Repeatable Read is MySQL's default isolation level unless configuration changes it. A stable snapshot does not, by itself, guarantee serializability.

Read committed vs repeatable read

Read committed vs repeatable read

Serializable

Serializable is the strongest standard isolation level. Successfully committed transactions must have the same effect as some execution in which those transactions ran one at a time.

This does not require the database to physically run all transactions sequentially. It may use locks, conflict checks, or transaction aborts to enforce the guarantee.

Consider a rule that Asha and Ravi must retain a combined balance of at least 500.00:

Transaction

Decision based on the initial total of 1500.00

T1

Withdraw 800.00 from Asha. This appears to leave 700.00.

T2

Withdraw 400.00 from Ravi. This appears to leave 1100.00.

If both act on the same old total, the result would be:

HolderName

Balance

Asha

200.00

Ravi

100.00

Combined balance

300.00

Each decision looks safe alone, but together they break the rule. This is a form of write skew.

If both transactions use Serializable isolation and correctly perform the check and update within their transactions, both cannot successfully commit based on those conflicting decisions. The database must coordinate their work or abort conflicting work.

After a retry, the application must read the data and check the rule again. Serializable does not create business rules automatically or correct faulty transaction logic.

Serializable protects shared rules

Serializable protects shared rules

Comparing the Four Levels

The following table shows the SQL standard’s minimum guarantees, not every database’s exact behavior.

Isolation level

Dirty reads

Non-repeatable reads

Phantom reads

Serialization anomalies

Read Uncommitted

May occur

May occur

May occur

May occur

Read Committed

Prevented

May occur

May occur

May occur

Repeatable Read

Prevented

May occur

May occur

May occur

Serializable

Prevented

May occur

May occur

May occur

Database implementations can be stronger than these minimum guarantees. In particular, PostgreSQL’s Read Uncommitted and the Repeatable Read behavior discussed above require those database-specific distinctions.

This table also does not fully describe every lost-update pattern. Writing an old application-calculated value back to a row may require an atomic update, a version check, suitable locks, or stronger isolation.

Four isolation levels

Four isolation levels

A Compact SQL Example

Objective: Compare repeated reads at Read Committed and Repeatable Read.

The example uses two separate database connections:

  • Session A: Reads Asha’s balance twice.

  • Session B: Adds 200 and commits between those reads.

1. Create the Initial Data

Consider the following queries to create and populate the account table.

CREATE TABLE Account (
AccountID INT PRIMARY KEY,
HolderName VARCHAR(50) NOT NULL,
Balance DECIMAL(10, 2) NOT NULL
);
START TRANSACTION;
INSERT INTO Account (AccountID, HolderName, Balance)
VALUES
(1, 'Asha', 1000.00),
(2, 'Ravi', 500.00);
COMMIT;

Initial table:

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

2. Session A: Read at Read Committed

Consider the following queries to start a Read Committed transaction and read Asha’s balance.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT Balance
FROM Account
WHERE AccountID = 1;
-- Keep the transaction open.

In MySQL, SET TRANSACTION without SESSION or GLOBAL applies to the next transaction.

Expected result:

Balance

1000.00

3. Session B: Update and Commit

Consider the following queries to increase Asha’s balance by 200.

START TRANSACTION;
UPDATE Account
SET Balance = Balance + 200
WHERE AccountID = 1;
COMMIT;

The committed balance is now 1200.00.

4. Session A: Read Again

Consider the following queries to repeat the read within Session A’s original transaction.

SELECT Balance
FROM Account
WHERE AccountID = 1;
COMMIT;

Expected result:

Balance

1200.00

Session A sees the change committed before its second query began. This demonstrates a non-repeatable read at Read Committed.

5. Repeat with Repeatable Read

First, restore Asha’s balance after both previous transactions have ended.

Consider the following queries to prepare the same starting value for the second demonstration.

START TRANSACTION;
UPDATE Account
SET Balance = 1000.00
WHERE AccountID = 1;
COMMIT;

Now start Session A with Repeatable Read.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT Balance
FROM Account
WHERE AccountID = 1;
-- Keep the transaction open.

The first read returns:

Balance

1000.00

Next, execute step 3 in Session B to add 200 and commit. Then execute step 4 in Session A to read again and end its transaction.

This time, Session A’s second read returns:

Balance

1000.00

The latest committed balance is 1200.00, but Session A continues using its existing snapshot until its transaction ends.

Comparison of the two demonstrations:

Session A isolation level

First read

Second read after Session B commits

Read Committed

1000.00

1200.00

Repeatable Read

1000.00

1000.00

These results apply to the ordinary SELECT queries shown. Adding FOR UPDATE would turn them into locking reads and change the interaction.

Summary

SQL isolation levels define how transactions interact with concurrent changes. Read Committed prevents dirty reads, Repeatable Read adds stronger protection for repeated reads, and Serializable guarantees an outcome equivalent to some serial execution.

The same level name can provide different details across databases. Understanding the standard guarantees, MySQL and PostgreSQL behavior, and the needs of the transaction helps avoid both incorrect results and unnecessary blocking.

CS Core

Read Similar Blogs

Comments0