Previously committed rows might be missed if NOLOCK hint is used

SqlServerForum.org » Blog Archive » Previously committed rows might be missed if NOLOCK hint is used

Thu, 08 Feb 2007 10:42:03 GMT

PingBack from http://sqlserverforum.org/archives/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used/

re: Previously committed rows might be missed if NOLOCK hint is used

DannyRa — Mon, 12 Feb 2007 16:14:48 GMT

Hi lubor,we think we have managed to answer your challange and repro a solution that demonstrates twice scanned rows :

-- first create tables,index

Create table DemoPageSplits (a int ,b varchar(6000))

Create unique clustered index ix_clust on DemoPageSplits(a)

Create table SaveInsertResults (Counter int,KeyValue int,InsTime datetime)

-- fill the table

TRUNCATE TABLE DemoPageSplits

set nocount on

declare @i int ;

set @i=1;

while (@I<=10000)

begin

INSERT INTO DemoPageSplits VALUES (@I,'X')

set @i=@i+1;

end;

-- RUN SCRIPT 2 and SCRIPT 3 in a sperate spid

-- make sure you feed inside SCRIPT 3 the coorect spid of SCRIPT 2

-- SCRIPT 2

-- update the table and create page splits

declare @imin int, @imax int;

set @imin=1;

set @imax=9999;

While (@imax>@imin)

begin

update DemoPageSplits set b= replicate('x',6000)

Where a=@imin

update DemoPageSplits set b= replicate('y',6000)

where a=@imAX

set @imin=@imin+1;

set @imax=@imax-1;

end;

--SCRIPT 3

--replace SPID= with the correct SPID of SCRIPT 2

-- we make sure each insert has a specific id (counter)

-- so we can determine if we get duplicate values from

-- the same INSERT ... SELECT statment

TRUNCATE TABLE SaveInsertResults

DECLARE @cnt INT

SET @cnt=1

WHILE EXISTS(SELECT STATUS FROM sys.sysprocesses

WHERE SPID=(spid of script2 )AND STATUS<>'SLEEPING')

BEGIN

INSERT INTO SaveInsertResults

SELECT @cnt,A,getdate() FROM DemoPageSplits (NOLOCK)

SET @cnt=@cnt+1

END

--SCRIPT 4

--- check the results

-- we just get the first occurance of having count more then 10000

-- create index just to make query run faster

create clustered index inx_clust on SaveInsertResults(Counter,keyvalue,InsTime)

-------------------------------------------------------

SELECT Counter,KeyValue,InsTime,COUNT(*) CountDuplicates FROM SaveInsertResults

WHERE Counter in (SELECT top 1 Counter FROM SaveInsertResults

GROUP BY Counter

HAVING COUNT(*)>10000

)

GROUP BY Counter,KeyValue,InsTime

HAVING COUNT(*)>1

partial results :

Counter KeyValue InsTime CountDuplicates

----------- ----------- ----------------------- ---------------

24 451 2007-02-12 14:01:23.140 2

24 562 2007-02-12 14:01:23.140 2

24 563 2007-02-12 14:01:23.140 2

24 564 2007-02-12 14:01:23.140 2

24 565 2007-02-12 14:01:23.140 2

24 566 2007-02-12 14:01:23.140 2

24 567 2007-02-12 14:01:23.140 2

24 568 2007-02-12 14:01:23.140 2

24 569 2007-02-12 14:01:23.140 2

24 570 2007-02-12 14:01:23.140 2

24 571 2007-02-12 14:01:23.140 2

24 572 2007-02-12 14:01:23.140 2

24 573 2007-02-12 14:01:23.140 2

24 574 2007-02-12 14:01:23.140 2

24 575 2007-02-12 14:01:23.140 2

24 576 2007-02-12 14:01:23.140 2

24 577 2007-02-12 14:01:23.140 2

24 578 2007-02-12 14:01:23.140 2

24 579 2007-02-12 14:01:23.140 2

24 580 2007-02-12 14:01:23.140 2

24 581 2007-02-12 14:01:23.140 2

24 582 2007-02-12 14:01:23.140 2

24 583 2007-02-12 14:01:23.140 2

24 584 2007-02-12 14:01:23.140 2

24 585 2007-02-12 14:01:23.140 2

24 586 2007-02-12 14:01:23.140 2

24 587 2007-02-12 14:01:23.140 2

......

this show duplicate keys created in the destination table becasue of twice scanned rows.

Uri Munitz &

Danny Ravid

Clalit Health Services

re: Previously committed rows might be missed if NOLOCK hint is used

LuborK — Mon, 05 Mar 2007 20:06:32 GMT

Uri and Danny - your solution is correct and you will receive a book of your choice (handled separately in private mail).

After posting my blog I was contactet by Itzik Ben-Gan who pointed me to his article in SQL Server magazine http://www.sqlmag.com/Articles/Index.cfm?ArticleID=92888&DisplayTab=Article where he provided solution to my problem even before I have posted my blog... I would like thank to Itzik for not spoiling this competition and let other try to come up with their own scripts to show the behavior!

Query Failure with Read Uncommitted

Craig Freedman's WebLog — Tue, 12 Jun 2007 22:58:01 GMT

Over the past month or so, I've looked at pretty much every isolation level except for read uncommitted

Isolation level read commited - linii duplicate

Sql Server — Wed, 22 Aug 2007 16:26:10 GMT

Isolation level read commited - linii duplicate

SQL Spatial - Tips on Storage

Dave does Data — Tue, 04 Nov 2008 07:06:44 GMT

This post talks about talks about ways to Store Spatial Reference data in SQL in order to improve performance

SQL Server Nolock Data Loss

SQL and Tech in Africa — Tue, 24 Feb 2009 01:38:58 GMT

I often get asked about the implications of using nolock query hints or read uncommitted and my response

SQL Server NOLOCK Hint & other poor ideas.

Dave does Data — Mon, 06 Apr 2009 02:40:27 GMT

Frequently I see production code, created by professional development teams, peppered with NOLOCK &