Phantom Read in SQL Server

A Phantom Read occurs when a transaction executes the same query twice and gets a different set of rows because another transaction inserted, deleted, or updated rows that now satisfy the query condition. Unlike a Non-Repeatable Read (where an existing row changes), a Phantom Read introduces new rows or removes rows from the result set. SQL Server prevents Phantom Reads using the SERIALIZABLE isolation level or Snapshot-based isolation.

Let’s see the demo

Suppose a bank manager wants to count all accounts with balances greater than ₹40,000.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN;  SELECT COUNT(*)  FROM Account  WHERE Balance>40000;

It returns 3 and transaction is still open not yet committed.

In the other session we are adding few records into this table.

INSERT INTO dbo.Account (     AccountNumber,CustomerName, AccountType,Balance, MobileNumber,EmailAddress ) VALUES ('SB1000031','Rohit Sharma','Savings',75000.00,'9876543210','rohit@gmail.com'),('SB1000032','Bagesh Singh','Savings',50000.00,'9876543211','bagesh@gmail.com')

2 records inserted successfully.

Run the same query again

SELECT COUNT(*) FROM Account WHERE Balance>40000;

Session 1 never modified anything.

Two new rows appeared. This new row is called PHANTOM ROW. Therefore, here Phantom Read Occurred.

To prevent this, we are using SERIALIZABLE.

Let’s see the here

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN;  SELECT COUNT(*) FROM Account WHERE Balance>40000; --commit

Getting the below records

Commit is not yet done.

Now inserting few records in other session.

Not able to insert due to transaction is already open which has not yet committed.

When we are running the session 1 select statement we are getting the same result.

No phantom rows can appear.

Isolation Levels vs Phantom Reads

Isolation Level	Phantom Read Possible?
READ UNCOMMITTED	Yes
READ COMMITTED	Yes
REPEATABLE READ	Yes
SNAPSHOT	No
SERIALIZABLE	No