With Halloween just around the corner, it’s the perfect time to discuss a spooky SQL Server phenomenon known as phantom reads. A phantom read occurs when rows have been inserted after a read operation and become visible in a follow-up read operation within the same transaction. In this article, we will explore what phantom reads are and how they can impact your SQL Server experience.

Let’s start with an example to illustrate how phantom reads can occur. Please note that the code provided in this example is for demonstration purposes only and may not work in your environment.

-- Window 1
BEGIN TRAN
SELECT TOP (6) *
FROM Person.Person
WHERE LastName LIKE 'A%'
ORDER BY LastName
WAITFOR DELAY '00:00:10.000'
SELECT TOP (6) *
FROM Person.Person
WHERE LastName LIKE 'A%'
ORDER BY LastName
COMMIT TRAN

-- Window 2
INSERT INTO [Person].[Person]([BusinessEntityID],[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName]
,[Suffix],[EmailPromotion],[AdditionalContactInfo],[Demographics],[rowguid],[ModifiedDate])
VALUES
( 992229,'SP','0','DR','Arun','Aaaron','A',Null,0,Null,Null,NEWID(),GETDATE() )

In this scenario, we have two query windows. In the first window, we start a transaction and execute two SELECT statements with a 10-second delay in between. Meanwhile, in the second window, we insert a new row into the Person.Person table. When we go back to the first window and execute the second SELECT statement, we can see that the row we just inserted is now visible, even though the entire batch ran in a single transaction. This is because the default isolation level in SQL Server is read committed, which allows for phantom reads.

To prevent phantom reads, you can use the serializable isolation level. Let’s see how this affects our example:

-- Stop the phantom
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT TOP (6) *
FROM Person.Person
WHERE LastName LIKE 'A%'
ORDER BY LastName
WAITFOR DELAY '00:00:10.000'
SELECT TOP (6) *
FROM Person.Person
WHERE LastName LIKE 'A%'
ORDER BY LastName
COMMIT TRAN

In this modified code, we set the transaction isolation level to serializable before executing the SELECT statements. When we run the same insert statement in a different window, and then go back to the select statement output, we can see that we have the same view of the data across both select statements within the transaction. However, this comes at the cost of reduced concurrency.

It’s important to note that phantom reads are not always undesirable. In some cases, you may want to see the most up-to-date data, even if it was inserted after your initial read operation. In such cases, the default read committed isolation level is sufficient. However, if you need to ensure consistent data throughout a transaction, you should consider using the serializable isolation level.

Phantom reads are just one of the many concepts and challenges you may encounter when working with SQL Server. Understanding how different isolation levels can impact your data consistency is crucial for building robust and reliable database applications.

Thank you for reading! If you have any questions or would like to learn more about SQL Server concepts, feel free to leave a comment below.

Filed under: Admin, SQL SERVER
Tagged: Phantom Reads, Read Committed, SQL, SQL Server, Transaction Isolation Level, TSQL