SQL Server Phantom Reads In Repeatable read ??? I dont think so….

According to BOL Repeatable transaction isolation level will give a repeated (consistent) view of the data for that particular resultset in a transaction.Here is the exact definition

“Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
……………Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads.”

Pay attention to the second part where it says the transaction will result in phantom rows..

Well its not always that way.Let me show you how.

Lets create a single table T with single coulmn Id and insert a single record.We will name it as Query1

Use Master
Go
 
Create Table T(Id int)
Go
 
Insert T
Select 0

Now in another new window run the following query.We will name it as Query2

Begin Tran
 
Select * From T(RepeatableRead)

Waitfor Delay '00:00:05'
 
Select * From T(RepeatableRead)
 
Commit

Now while the first query is running in a new window run the following query.Name it as Query3

Begin Tran
 
Insert Into T
Select 1
 
 
Commit

When Query2 finishes we can see that the resultset matches the exact definition of BOL.Nothing surprising here

Now let us tweak Query3 a bit and add an update clause to it.Before the running the following query drop the table T and recreate it using Query 1.

Begin Tran
 
Update T Set Id=-1
 
Insert Into T
Select 1


Commit

Now repeat the above steps again.Here is the result…

Well what have we got here ? The output for Query2 is honoring the first part of the definition but not the second part i.e the one with phantom read.Ideally the output for the last query should be 0 and 1 but that is not the case. But if you run a select statement in a new session you can see that the the value has been inserted as well as updated.

So the conclusion from above blog is that phatom reads will not occur when  combined with data modification queries(update,delete) if combined in a same transaction….So beware when using this isolation level.You can get surprising results…

Thanks for reading

Regards

Sachin Nandanwar

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook