Compartilhar via

Query Failure with Read Uncommitted

  • Artigo

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.

Comments

  • Anonymous
    September 03, 2007
    PingBack from http://blogs.clarience.com/davide/?p=9

  • Anonymous
    December 18, 2007
    Ошибка, которая может возникнуть при использовании хинта NOLOCK - нечастая ситуация, но тем не менее...

  • Anonymous
    June 09, 2010
    Good one. But if we use NoLock on both the tables this would not have this impact mentioned.   select *    from t1 with (nolock)    where exists (select * from t2  with (nolock) where t1.k = t2.pk) But there is possibility of dirty reads.

  • Anonymous
    June 14, 2010
    Actually, FYI, the same error is possible even with nolock on both tables.  However, it is very difficult to reproduce since we cannot use the blocking of the scan on table t2 to give us time to delete the row from table t1.

  • Anonymous
    February 01, 2011
    Very nice post Craig Acttualy I can reproduce the error with the NOLOCK in both tables... Run the script from the connection 1, and leave it running... Run the script from the connection 2(with the nolock in both tables) and leave it runing for a while(almost 30 seconds) until you get the error :-). Following is the script: IF OBJECT_ID('Tab1') IS NOT NULL  DROP TABLE Tab1 IF OBJECT_ID('Tab2') IS NOT NULL  DROP TABLE Tab2 GO CREATE TABLE Tab1 (ID INT, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab1(ID) VALUES(0), (1) GO CREATE TABLE Tab2 (ID INT PRIMARY KEY, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab2(ID) VALUES(0), (1) GO -- Connection 1 WHILE 1 = 1 BEGIN  BEGIN TRAN  UPDATE Tab2 SET ID = ID  WHERE ID = 0  DELETE Tab1 WHERE ID = 0  COMMIT TRAN  INSERT INTO Tab1(ID) VALUES(0) END -- Connection 2 SET NOCOUNT ON WHILE 1=1 BEGIN  IF OBJECT_ID('tempdb.dbo.#Tab1') IS NOT NULL    DROP TABLE #Tab1  SELECT *    INTO #Tab1    FROM Tab1 WITH(NOLOCK)   WHERE EXISTS (SELECT *                   FROM Tab2 WITH(NOLOCK)                  WHERE Tab1.ID = Tab2.ID) END

  • Anonymous
    February 01, 2011
    I saw that you don't need the Update on Tab2, just the delete is enought to run the error 601. -- Preparando o ambiente IF OBJECT_ID('Tab1') IS NOT NULL  DROP TABLE Tab1 IF OBJECT_ID('Tab2') IS NOT NULL  DROP TABLE Tab2 GO CREATE TABLE Tab1 (ID INT, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab1(ID) VALUES(0), (1) GO CREATE TABLE Tab2 (ID INT PRIMARY KEY, Col1 Char(500) DEFAULT NEWID()) GO INSERT Tab2(ID) VALUES(0), (1) GO -- Conexão 1 SET NOCOUNT ON WHILE 1 = 1 BEGIN  -- Conexão 1  BEGIN TRAN  DELETE Tab1 WHERE ID = 0  COMMIT TRAN  INSERT INTO Tab1(ID) VALUES(0) END -- Conexão 2 SET NOCOUNT ON WHILE 1=1 BEGIN  IF OBJECT_ID('tempdb.dbo.#Tab1') IS NOT NULL    DROP TABLE #Tab1  SELECT *    INTO #Tab1    FROM Tab1 WITH(NOLOCK)   WHERE EXISTS (SELECT *                   FROM Tab2 WITH(NOLOCK)                  WHERE Tab1.ID = Tab2.ID) END

  • Anonymous
    January 19, 2012
    The comment has been removed

  • Anonymous
    October 11, 2012
    To be able to reproduce the errors, you can use WAITFOR DELAY statement. This statement can open time window in which you can try to sync the concurrent sessions and make them collide in expected manner.