Next, in session 1 lock the first row of t2 using the following update:
update t2 set pk = pk where pk = 0
Now, in session 2 run the following query:
from t1 with (nolock)
where exists (select * from t2 where t1.k = t2.pk)
This query uses the following plan:
|--Nested Loops(Left Semi Join, WHERE:([t1].[k]=[t2].[pk]))
|--Clustered Index Scan(OBJECT:([t2].[PK__t2__71D1E811]))
The table scan fetches the first row of t1 without acquiring any locks and then tries to join this row with t2. Since we've locked the first row of t2 and since the clustered index scan of t2 runs at the default read committed isolation level, the query blocks.
Finally, in session 1 delete the first row of t1 and commit the transaction:
delete t1 where k = 0
The query in session 2 is now free to continue. However, we deleted the row that it is trying to join while it was blocked. The query tries to retrieve more data from the deleted row and fails with the following error:
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
As you can see, not only can a read uncommitted or nolock scan cause unexpected results, it can even cause a query to fail entirely!
SQL Server 2000 can also generate this error if a query plan includes a bookmark lookup and if a row is deleted after it is returned by a non-clustered index seek but before the base table row is fetched by the bookmark lookup. SQL Server 2005 does not generate an error in this case. Recall that
in SQL Server 2005 a bookmark lookup is just a join
. Thus, if the bookmark lookup cannot find a matching base table row, it simply discards it just like any other join.