Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

Query Failure with Read Uncommitted

Query Failure with Read Uncommitted

Rate This
  • Comments 8

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.

  • PingBack from http://blogs.clarience.com/davide/?p=9

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

  • 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.

  • 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.

  • 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

  • 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

  • I think the subject (NOLOCK, TRANSACTION ISOLATION LEVEL READ UNCOMMITED) is running around situations where it is too obvious to not to use this locking mechanism. If someone does that in a circumstances like the  Fabiano Amorim and Craig examples demonstrated should return his/her Microsoft Certification. My environment is where we have heavy load of reads (Large Reports) and comparatively low amount of WRITE/UPDATE/DELETE types of transactions. Also the the SELECT queries executing on Date range limited filtered data set where the latest record is at least 8 hours old. Summaries for government officials, weekly,monthly and quarterly performance analysis etc... To speed up the Reports I use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED because it is very unlikely that any of the records SELECTed will be changed or deleted. Never had any complain that my extracts are inaccurate or data missing. In circumstances like this NOLOCK is a benefit.

  • 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.

Page 1 of 1 (8 items)
Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post