SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Phantom Reads

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.

GHOST

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:

serial

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

helloPhantom

Thanks to Hugo for the review.

 

 


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...