July 6, 2009 at 4:59 pm
I keep reading about why using a nolock hint can be bad, so I decided to do some experiments to see if it was true.
First of all I created a couple of tables
create table NoLockTest1
(
IDintnot null,
Data varchar(8000) not null,
Seq int identity(1,1) not null
constraint pk_NoLockTest1 primary key (Seq)
)
go
create unique index ind_NoLockTest1 on NoLockTest1(ID)
go
create table NoLockTest2
(
IDintnot null,
Data varchar(8000) not null,
constraint pk_NoLockTest2 primary key (ID)
)
go
Note that the ID column in both tables has to contain unique values.
Into the first table I then inserted 1000 'small' rows with IDs 0 to 999
declare @i as int = 0
while @i < 1000
begin
insert into NoLockTest1(ID,Data) select @i,'a'
set @i = @i + 1
end
go
[/code]
I then open a second query window and set the following two queries running at the same time.
[code]
--Connection one
while 1=1
begin
update NoLockTest1
set Data = Data + 'a', ID = ID + 1
end
go
--Connection Two
while 1=1
begin
delete from NoLockTest2
insert into NoLockTest2 select ID,Data from NoLockTest1 (noLock)
if @@ERROR != 0 break
end
[/code]
After a few seconds I got the following error from the second connection
Violation of PRIMARY KEY constraint 'pk_NoLockTest2'. Cannot insert duplicate key in object 'dbo.NoLockTest2'.
Normally this wouldn't happen as a unique constraint on NoLockTest1 ensures that table can't contain duplicate values for ID.
In this case however, the nolock hint allows us to read the data whilst the table is being updated. As we keep increasing the length of the data column then page splits are occuring which is causing our query to return inconsistent data.
Is my understanding correct? and is there an easier way of showing it?
July 6, 2009 at 5:36 pm
The issue you outline here is not the only issue, nor in my opinion is it the worst case. The worst case is when you have data movement occuring during your read that causes you to read pages of data over.
Something like that could cause your query to return invalid totals for your reports. Overstating income, dividends, credits, debits, or any other type of information could be disastrous to your company.
Review the following blog: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
He shows just some of the reasons why using nolock can be problematic.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2009 at 6:24 pm
Read Uncommitted is not unique in being vulnerable to reading the same data twice:
This is one of many articles on the subject
It is unique, however, in being vulnerable to the 'cannot continue scan with NOLOCK due to data movement' error.
The answer to the original question is: it depends. Every transaction isolation represents a different compromise. It depends what you need. For a super-high-volume mainly-OLTP system, READ UNCOMMITTED may be the best or only choice. Systems which require 'accurate' aggregations may benefit from a row-versioning isolation level. Still others will require REPEATABLE READ or SERIALIZABLE, at least in places.
The question of 'correct' data is a fascinating one. Where the compromise lies for you depends entirely on what you need and why.
Paul
July 6, 2009 at 9:36 pm
As a policy I recommend NEVER using NOLOCK when the data you are reading is reasonably possible to change and if a change would potentially cause an issue..
What this really means is that I ALMOST never use it for anything other than reports and then only on reports that cover history where the data is unlikely to change. For current reports I almost never allow it, since I can't guarantee data quality. There have been a very few exceptions to this..
In this insert/select statement I would NEVER do it.. There is just too much risk..
CEWII
July 6, 2009 at 10:11 pm
So Elliott,
Which isolation level would you use to avoid that problem 😉
Paul
July 6, 2009 at 10:27 pm
First I would not use the NOLOCK hint, second of all I would probably use the Read committed isolation level, the upside is that is the SQL default..
I rarely change the isolation level, I have had a few cases where I a different one but not very often. The read committed is good most of the time.
CEWII
July 6, 2009 at 10:32 pm
Elliott (7/6/2009)
First I would not use the NOLOCK hint, second of all I would probably use the Read committed isolation level, the upside is that is the SQL default.
😀 That's what I figured 😀
If you get a minute, check out the article I linked to a couple of posts back...
Paul
July 7, 2009 at 1:46 am
thank you for your replies and article links - I will take a look.
Regards
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply