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