SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Consensus on NOLOCK
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  05:43:18  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by eyechart
1000s of page hits per second is not the norm. [understatement]That is a busy system[/understatement], and is really a type of environment that SQL Server has had difficulty handling in the past. Using NOLOCK in that environment is no doubt a must to achieve acceptable performance.

On a system that busy and if the data was a little more critical, it might be worthwhile looking at other RDBMS solutions like Oracle or even DB2.


I agree with everything you said. I just wanted to mention the scenario where using nolock hints makes sense.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  05:44:44  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by eyechart
We could always ask mmarovic to disable NOLOCK on his system to see what happens. I suggest barracading your office door before you run this test. That should hold off your management long enough for you to run a few tests and post the results on sqlteam for us


Unfortunatelly, I don't work for that company anymore.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  05:47:35  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by nosepicker

Using NOLOCK can be dangerous. I have a nightly job that does a simple INSERT ... SELECT. I used to use NOLOCK on the SELECT, but about once a month it would actually dupe some of the records. It was selecting from a relatively high transaction table. Bottom line: NOLOCK can produce unpredictable results at times, so be careful when you use it.


It can. During a year and half I worked for the company having nolocks as a rule it was registred twice that an error claiming something like "because of using nolocks I miss the pointer to the next row, so I don't know where to go now".
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  05:56:35  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by Michael Valentine Jones
The question of whether the RDBMS is locking or not locking is moot; it is doing locking and that can’t be turned off in SQL Server. The question is when will NOLOCK in queries make any noticeable improvement in performance.

What I am saying is that most of what I have heard about this falls in the "everyone knows that" or "old wives tale" category.

If you have a system that is experiencing heavy blocking due to long running transactions that update a lot of rows, that is one situation. If you have an application this is mostly short inserts of a few rows, that is another situation. It could be that there is very little benefit to using NOLOCK when there are few long running transactions.

mmarovic only said that using NOLOCK was the rule there; he didn't make any statements about what happened before and after this was implemented, what the nature of the transactions were, or if he was even there before it was implemented. That’s hardly the kind of information you should make an informed decision on.

I would like to see a lot more in the way of solid information based on testing under controlled conditions before implementing something like always using NOLOCK. I’m willing to believe NOLOCK can make performance improvements in some situations. I’m just unwilling to believe it’s some dramatic cure-all without seeing hard evidence.


MVJ, I agree with your "test everything, trust nobody" approach. Too many miths are spreading around, so it is better to test any claim.
You are also right that I haven't seen tests when I came there. However, the company had a few very knowledgable sql server specialists and I believe they tested it. Also their data model was well designed. They also looked for every small performance improvment and resource usage saving they (we) could find.
So the moral of the story is that nolock hints save the resources and speed-up the query somewhat (how much remains to be tested). The price are occasional errors and inconsistent results. It depends on your business scenario if it is the price worth paying.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  05:58:51  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
quote:
Originally posted by TG

I tried to set up a little test to "prove" my anecdotal intuition:

Here is a very simple test which you guys can correct but this is the kind of scenario that I believe most people resort to using nolock (or read uncommitted transaction isolation level). The situation is where a lot of small transactions are written to the same tables that are included in some fairly intensive searches. The search performance isn't really affected but the writes are the things that benefit.

This may just prove that a bad design can benefit from using nolock but I think this is a common situation


/*
Set up the test

use pubs
if object_id('tempdb..##junk') > 0 
	drop table ##junk

create table ##junk (rowid int identity(1,1) primary key clustered, id int, name sysname)
insert ##junk
select a.id, a.name
from	sysobjects a
cross join sysobjects b
cross join sysobjects c
*/


--in 1 window do the selects 
--run this and at the same time:
--run the other statements in another window

--then uncomment the "with (nolock)"
--switch the other window's "values" to "noLock"
--and restart both windows

declare @i int
set @i = 1
while @i < 27
begin
	select * 
	from ##junk --with (nolock)
	where name like char(96 + @i) + '%'
	set @i = @i + 1
end


/*
--in another window do the writes
set nocount on
-- if object_id('tempdb..#log') > 0 
-- 	drop table #log
-- create table #log (seltype varchar(15), duration int)


declare @i int
	,@st datetime
select @i = 1
	,@st = getdate()
while @i < 501
begin
	set @st = getdate()

	insert ##junk (id, name)
	values (1, 'tg')

	insert #log (seltype, duration)
	--values ( 'nolock', datediff(millisecond, @st, getdate()) )
	values ( 'lock', datediff(millisecond, @st, getdate()) )
	
	set @i = @i + 1
end

--select seltype, min(duration) [min], max(duration) [max], avg(duration) [Avg], count(*) [count] from #log group by seltype
*/


output:
Write stats while selects are conducted

seltype         min         max         Avg         count       
--------------- ----------- ----------- ----------- ----------- 
nolock          0           16          0           500
lock            0           10750       21          500


Be One with the Optimizer
TG


I think locks are not applied to temporary tables, so we would need to use permanent tables for testing.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/07/2006 :  06:43:31  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
Uh, it looks like I was wrong based on results posted. I would still use permanent tables, because I am not sure if locking and logging mechanism is the same for tempdb as for user databases.
Go to Top of Page

sunilagarwal
SQL Server Product Team

USA
3 Posts

Posted - 06/07/2006 :  11:58:19  Show Profile  Reply with Quote
Like others have indicated on this thread,here are some general points

(1) NOLOCK is same as ANSI defined isolation level 'read uncommitted'
(2) It means that a transaction running under this isolation level can read data modified by transactions that have not yet committed.
(3) A query that runs under NOLOCK or read uncommitted isolation level, it will not get blocked by concurrent updates.

You can benefit by this isolation level ONLY when both of the following conditions are true
(1) you are experiencing significant blocking between readers/writers
(2) your application or transaction is fine reading uncommitted data. One example can be say say you want to find out 'Average price of wine bottle in a wine shop?' you are probably ok running this query with NOLOCK even though customers may be in the process of buying a subset of wine bottles.

thanks
Sunil Agarwal
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 06/07/2006 :  12:09:06  Show Profile  Reply with Quote
quote:
Originally posted by sunilagarwal
(1) NOLOCK is same as ANSI defined isolation level 'read uncommitted'



It is almost the same. Since NOLOCK is a hint, the server can chose to ignore it. That is not true when you change the isolation level.



-ec

Edited by - eyechart on 06/07/2006 12:09:20
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 06/07/2006 :  12:14:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sunil is a MS employee responding due to Paul Randall raising the question! Thanks Sunil and Paul.

I am actually going to be recommending the NOLOCK hint in a few minutes to some developers here. The recommendation will only be on a couple of stored procedures and only on the large table that we are selecting from.

Tara Kizer
aka tduggan
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/07/2006 :  12:56:58  Show Profile  Reply with Quote
"I am actually going to be recommending the NOLOCK hint ..."

Yup, I reckon that we have a couple of busy tables which would be safe too.

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 06/07/2006 :  13:00:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well we have a current problem, so that's why the recommendation was made. I wouldn't go down this route unless you knew of a problem or had an impending problem.

Tara Kizer
aka tduggan
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/07/2006 :  13:08:06  Show Profile  Reply with Quote
OK, ta. The table I have in mind is used for a code lookup, if the value is not found it is inserted. It gets lots of INSERTs a day, heaps more Selects, but the rows are never modified thereafter.

We have a couple of other tables like that.

I'm figuring it would be worth trying on those tables that should have no side effects but MIGHT be performance bottlenecks (if not now then in the future) to stave off potential future problems.

Clients are always thrilled when a new release goes faster. We tell them its because of months of careful optimisation and tinkering with the caching algorithms - so any "low fruit" is therefore always welcome!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/07/2006 :  13:56:23  Show Profile  Reply with Quote
I wonder how SQL Server handles databases that are in read only mode?

Does it still create page read locks, or does it not even bother because there is no possibility that the page is being updated? Would the NOLOCK hint help or have any effect at all?

I guess the same could be asked about a filegroup that was set to read only.

CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/08/2006 :  02:53:42  Show Profile  Reply with Quote
So never-changing tables put in a filegroup that was readonly might get all the benefits without having to explicitly use NOLOCK etc.?

Kristen
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 06/08/2006 :  10:33:54  Show Profile  Reply with Quote
I don't think the NOLOCK hint would do anything in a read-only filegroup since there wouldn't be any lock contention between shared and exclusive locks.
Go to Top of Page

sunilagarwal
SQL Server Product Team

USA
3 Posts

Posted - 06/08/2006 :  11:59:43  Show Profile  Reply with Quote
I did a simple experiment by running a transaction under serializable isolation level and seeing what locks are taken when database is read-write or database is read-only. As you can see that locks on data rows/keys are not taken under read-only database. So NOLOCK hint will not provide you any benefit.


My example:
set transaction isolation level serializable

begin tran
go
select * from t1
sp_lock @@spid



spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
52 7 0 0 DB S GRANT
52 7 645577338 2 PAG 1:181 IS GRANT
52 1 1115151018 0 TAB IS GRANT
52 7 645577338 2 KEY (ffffffffffff) RangeS-S GRANT
52 7 645577338 0 TAB IS GRANT
52 7 645577338 2 KEY (03000d8f0ecc) RangeS-S GRANT
52 7 645577338 2 KEY (010086470766) RangeS-S GRANT


alter database bulktest set read_only
set transaction isolation level serializable

begin tran
go
select * from t1
sp_lock @@spid

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
52 7 0 0 DB S GRANT
52 1 1115151018 0 TAB IS GRANT
52 7 645577338 0 TAB IS GRANT



Sunil Agarwal
Program Manager,
SQL Server Storage Engine
Go to Top of Page

BillSheldon
Starting Member

USA
2 Posts

Posted - 06/21/2006 :  16:30:07  Show Profile  Visit BillSheldon's Homepage  Reply with Quote
So given that there are inherent issues with using NOLOCK/READUNCOMMITED in terms of data consitency, and given that not having reads blocked by writes I have a couple questions:
1. In terms of overall database performance, how does using the NOLOCK hint on query after query in your application compare against the new SQL Server 2005 feature for "Read Committed with Snapshots" isolation level which does protect data consistency but does not cause reads to be blocked by writes?

2. Given that the primary need for this hint is that some database process has a long running update against one or more tables, is there any prescriptive guidance on how to create atomic transactions which avoid long running write statements?

Thanks

Bill Sheldon
MVP for VB.NET
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 06/21/2006 :  16:51:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
Bill Sheldon...Now there's a name that I recognize from the comment header block in some legacy stored procedures here.

Tara Kizer
aka tduggan
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/21/2006 :  16:55:48  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

I wonder how SQL Server handles databases that are in read only mode?...

I guess the same could be asked about a filegroup that was set to read only.



I was on a project that once a year imported data from an external agencies. The 4 tables it spawned averaged a couple of millions rows and we shifted those to a read-only filegroup. On our tests SQL didn't issue locks and hence we thought we where legends because we saved a few cycles...

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.
Go to Top of Page

sunilagarwal
SQL Server Product Team

USA
3 Posts

Posted - 06/21/2006 :  22:21:50  Show Profile  Reply with Quote
quote:
Originally posted by BillSheldon

So given that there are inherent issues with using NOLOCK/READUNCOMMITED in terms of data consitency, and given that not having reads blocked by writes I have a couple questions:
1. In terms of overall database performance, how does using the NOLOCK hint on query after query in your application compare against the new SQL Server 2005 feature for "Read Committed with Snapshots" isolation level which does protect data consistency but does not cause reads to be blocked by writes?

sunilagarwal> There are two primary overheads when snapshot option (SI or RCSI) is enabled on the database. First, SQL server will generate versions for updates and deletes. Second, a select may need to traverse version chain to access the older version. So may require additinal IO. For common applications, the version chain is small. You will need to test with your workload. There is a white paper on snapshot isoation @ http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbengine.mspx


2. Given that the primary need for this hint is that some database process has a long running update against one or more tables, is there any prescriptive guidance on how to create atomic transactions which avoid long running write statements?
sunilagarwal> you can only update one table per statement. So you should break your update into its own transaction if possible. If you are updating a large number of rows of a given table, it may run for a long time.

Thanks

Bill Sheldon
MVP for VB.NET



Sunil Agarwal
Program Manager,
SQL Server Storage Engine
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000