Query Failure with Read Uncommitted

First published on MSDN on Jun 12, 2007

Over the past month or so, I've looked at pretty much every isolation level except for read uncommitted or nolock.  Today I'm going to wrap up this series of posts with a discussion of read uncommitted.  Plenty has already been written about the dangers of nolock.  For example, see these excellent posts by Lubor Kollar of the SQL Server Development Customer Advisory Team and by Tony Rogerson .

I'd like to demonstrate just one additional hazard of nolock.  Begin by creating two tables as follows:

create table t1 (k int, data int)
insert t1 values (0, 0)
insert t1 values (1, 1)

create table t2 (pk int primary key)
insert t2 values (0)
insert t2 values (1)

Next, in session 1 lock the first row of t2 using the following update:

begin tran
update t2 set pk = pk where pk = 0

Now, in session 2 run the following query:

select *
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]))
|--Table Scan(OBJECT:([t1]))
|--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
commit tran

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.