Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 158

You can use the commented-out code in Session 2 to reset the data.
Execute this code now. To get a repeatable read, change the trans-
action isolation level in Session 1 as indicated here:

SET TRANSACTION ISOLATION LEVEL
--READ COMMITTED;
REPEATABLE READ;

Now rerun Session 1 and Session 2 as before. You should notice
that Session 2 has been blocked from performing its update until
after the transaction has been completed. The i rst read in Session
1 is now repeatable. Your results from Session 1 should now match
those in Figure 6-5.

FIGURE 6-5

Phantom Reads
Phantom reads occur when a row is inserted into or deleted from a range of data by one transaction
that is being read by another set of data. Recall the earlier work queue scenario. Suppose a user reads
the work queue searching for new work items and gets back 10 records. Another user inserts a new
work order. Shortly afterward, the i rst user refreshes the list of new work orders. There are now 11.
This additional row is a phantom row.

Often this outcome is desirable. In cases when you need to be able to rely on the range of data
previously read, however, it is not. The following example uses the Person.Person table to
demonstrate a phantom (code i le Ch6PhantomReads.sql ):

/*SESSION 1*/
USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
--SERIALIZABLE;

BEGIN TRANSACTION;

SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person
ORDER BY LastName;

WAITFOR DELAY '00:00:05.000';

SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix