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.