With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.

I will show you what this looks like with an example. Please note the code will not work for you because I dropped the foreign key constraint within the person.person table just so I could show you the example.

In my first query window I have 2 select statements separated with a 10 second delay within a transaction. I will execute this and during the execution I will execute an insert statement in query window 2.

--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

I flip over to query window 2 to run the insert statement.

 -- 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() )

I go back to window 1 and wait until the second SELECT statement has finished. As you can see in the screenshot below, the second execution of the exact same query returned a different result: the row I just inserted is visible even though the entire batch ran in a single transaction.

This may be desirable and should be expected under the default isolation level which is read committed. To prevent phantom reads, you would need to use the serializable isolation level. Dr Arun will NOT be shown within the last select statement. Before executing the below code I manually deleted the row where BusinessEnitityID = 992229.

 --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

I then run the insert code in a different window. I then go back to the select statement output and see the following:

I have the same view of the data across both select statements within the transaction but this is at the cost of reduced concurrency.

If I re-issue a select statement, I will now see the row.

 --NOW CHECK –SHOULD BE THERE
SELECT TOP (6) *
FROM Person.Person
WHERE LastName LIKE 'A%'
ORDER BY LastName

Thanks to Hugo for the review.

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