Could not continue scan with NOLOCK due to data movement.

Could not continue scan with NOLOCK due to data movement.

Post by kurt sun » Wed, 04 Oct 2006 18:27:51


Hi, I am having trouble with "Could not continue scan with NOLOCK due to
data movement."

I have a very large table in which i need to delete rows without having
neither the rowlocks to escalate nor the transactionlog to blow up.

So i have created a query for this. Unfortunately it doesnt work on SQL2000
which we have on the production machine. It works perfect on SQL2005.

The error in SQL2000 is due to the fact that if TOP x returns fewer rows
than there is in the table i get:
Server: Msg 601, Level 12, State 6, Line 15
Could not continue scan with NOLOCK due to data movement.

Anybody any ideas?




Code to reproduce the problem:

The DDL:
CREATE TABLE dbo.F03 (
VJEDUS char(10) NOT NULL ,
VJEDTN char(22) NOT NULL ,
VJEDLN float NOT NULL ,
VJEDBT char(15) NOT NULL ,
VJEDSP nchar(1) NULL ,
VJDGJ decimal(18,0) NULL
)
ALTER TABLE dbo.F03 ADD
CONSTRAINT F03PK PRIMARY KEY CLUSTERED
( VJEDUS,
VJEDBT,
VJEDTN,
VJEDLN
)
CREATE INDEX F03x001 ON dbo.F03(VJEDSP ASC)
CREATE INDEX F03x002 ON dbo.F03(VJDGJ DESC)
GO

Populate the table:
set @i = 1
while @i < 999
begin
insert into dbo.F03
(VJEDUS
,VJEDTN
,VJEDLN
,VJEDBT
,VJEDSP
,VJDGJ
)values
( 'A'
, 'A'
, @i
, 'A'
, N'1'
, @i)
insert into dbo.F03
(VJEDUS
,VJEDTN
,VJEDLN
,VJEDBT
,VJEDSP
,VJDGJ
)values
( 'B'
, 'B'
, @i
, 'B'
, N'1'
, @i)
set @i = @i + 2
end


The query:

SET NOCOUNT ON
DECLARE @i INTEGER, @j INTEGER
SET @i = 100
WHILE @i < 124
BEGIN
SET @j = 1
PRINT '>>>>>>>> @i = '+ convert(varchar(10), @i)
WHILE @j > 0
BEGIN
DELETE dbo.F03 FROM
(SELECT TOP 1 *
FROM dbo.F03B
WHERE VJEDSP=N'1' AND VJDGJ = @i ) AS I
WHERE dbo.F03.VJEDUS = I.VJEDUS
AND dbo.F03.VJEDBT = I.VJEDBT
AND dbo.F03.VJEDTN = I.VJEDTN
AND dbo.F03.VJEDSP = I.VJEDSP
AND dbo.F03.VJDGJ = I.VJDGJ

SET @j = @@rowcount
PRINT 'Deleted another ' + convert(varchar(10), @j)
PRINT '==========================================='
END
SET @i = @i + 1
END
 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Immy » Wed, 04 Oct 2006 18:39:02

have you seen this?
http://www.yqcomputer.com/

 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by kurt sun » Wed, 04 Oct 2006 19:19:11

o, I havent seen that Q.

But it says that this error occurs only when you have
"transaction isolation level set to READ UNCOMMITTED".

Which I haven't.

Am I thinking errounesly?


/k

"Immy" < XXXX@XXXXX.COM > wrote in message
news:ORhcE$ XXXX@XXXXX.COM ...


 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Roji P Tho » Wed, 04 Oct 2006 19:47:31

The reasons are very well explained by Itzik Ben Gan in the following
article series.

http://www.yqcomputer.com/
http://www.yqcomputer.com/
http://www.yqcomputer.com/

--
Regards
Roji. P. Thomas
http://www.yqcomputer.com/
 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Roji P Tho » Wed, 04 Oct 2006 19:48:12

he NOLOCK hint is essentially same as the READ UNCOMMITTED isolation level.

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"kurt sune" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Daniel Cri » Wed, 04 Oct 2006 19:49:58

ITH (NOLOCK) is the same as doing this.

Dan

kurt wrote on Tue, 3 Oct 2006 12:19:11 +0200:



 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Immy » Wed, 04 Oct 2006 19:53:59

t's practically the same .

"kurt sune" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Dan Guzma » Wed, 04 Oct 2006 20:01:47

was able to reproduce your problem in the READ COMMITTED isolation level
if I changed the F03B table reference to F03.


I thought it odd that you are not specifying the primary key columns in the
derived table join. I don't know the details of your actual situation
(there is no F03B table in your script) so I changed the DELETE to the
self-join and it worked as expected. I suspect the error actually occurs
when multiple rows are returned by the join and is misleading due to the
NOLOCK reference.

DELETE dbo.F03 FROM
(SELECT TOP 1 *
FROM dbo.F03
WHERE VJEDSP=N'1' AND VJDGJ = @i ) AS I
WHERE dbo.F03.VJEDUS = I.VJEDUS
AND dbo.F03.VJEDBT = I.VJEDBT
AND dbo.F03.VJEDTN = I.VJEDTN
AND dbo.F03.VJEDLN = I.VJEDLN

Also, I strongly suggest you avoid using floats as part of the primary key.
The float data type is approximate and can cause issues when used
inappropriately.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kurt sune" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by kurt sun » Wed, 04 Oct 2006 20:50:06

Thanks for answer.
F03B was a typo, it should be F03.

Yes I forgot the fourth primary key column.

However I find it strange that:

On a win2003 server with sql2000 sp4 I got
Server: Msg 601, Level 12, State 6, Line 11

Could not continue scan with NOLOCK due to data movement.

On a win2000 server with sql2000 sp3 it worked.
On a win2000 server with sql2005 it worked.

/k




the
key.
 
 
 

Could not continue scan with NOLOCK due to data movement.

Post by Dan Guzma » Wed, 04 Oct 2006 21:07:50

> However I find it strange that:

I'm not too surprised you might see different behavior depending on SQL
Server version, SP level and perhaps even hotfix level. The conditions
needed to cause the error (DELETE, derived table, mal-formed query) are
fairly obscure and is probably why this wasn't encountered during SP4
regression testing.

--
Hope this helps.

Dan Guzman
SQL Server MVP