with (NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

Eddie

I wondering which one of the following I should use to get the best
performance.
1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
OR
2. "WITH (NOLOCK)"

I notice that when I use the #1 "SET TRANSACTION..." it sets a lock Mode
type of "Sch-S" (Schema stability Lock) which described by SQL Books
Online as "Schema stability (Sch-S) locks do not block any transactional
locks, including exclusive (X) locks"

When I use #2 "WITH (NOLOCK)" it returns a lock type mode of "IS"
(Intent shared) which is explained as:
"[IS lock] Indicates the intention of a transaction to read some (but
not all) resources lower in the hierarchy by placing S locks on those
individual resources." it later explains an "S" lock as: "No other
transactions can modify the data while shared (S) locks exist on the
resource."

This is how I tested bot executed both:

strSQL = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"
strSQL = strSQL & "SELECT intNumVisitors FROM HitCounter;"
set objRS = objConn.execute (strSQL)

OR

strSQL = "SELECT intNumVisitors FROM HitCounter WITH (NOLOCK);"
set objRS = objConn.execute (strSQL)

I thought that "WITH (NOLOCK)" was supposed to do exactly what "SET
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" does but that doesn't
seem to be the case? What's up??? I am confused! Can someone clarify why
this appears to be the case.

Thanks,

Ed
*** Sent via Developersdex http://www.developersdex.com ***

Aug 24 '05 #1

Subscribe Reply

14884

Aaron Bertrand [SQL Server MVP]

Behind the scenes, the queries themselves behave the same.

After discussing with some colleagues, I can summarize that the differences
you see are due to:

(a) system tables being accessed during query compilation and plan caching
(b) perhaps a query plan cached for one version and not the other (the
nolock version likely isn't cached)
(c) the procedure cache being empty

In other words, the locks on intNumVisitors are the same in both cases, but
different situations can cause locks to occur on other objects and while
managing the procedure cache, and because the lock messages are cryptic and
don't reference tables directly, it can be misleading. Much of this will
not affect performance, for example a lot of the procedure cache work is
performed as a result of the query, not before the query.

I ran these queries multiple times in Query Analyzer, against 8.00.2040, and
there didn't seem to be much difference between the locks acquired. I
didn't cross-reference everything, but I would put money on the notion that
not all of the locks are against the Orders table.

use Northwind
go

dbcc freeproccache
dbcc traceon(1200,36 04)
go

select * from Orders with (nolock)
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (5200bb3a0129) (class bit0 ref1)
result: OK

Process 60 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 60 acquiring S lock on KEY: 6:1:2 (260178426497) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 60 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:3:1 (cc0073e4c1ea) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cc0073e4c1ea)
Process 60 acquiring S lock on KEY: 6:3:1 (cd009d4b74f8) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cd009d4b74f8)
Process 60 acquiring S lock on KEY: 6:3:1 (ce00f82cc840) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (ce00f82cc840)
Process 60 acquiring S lock on KEY: 6:3:1 (cf0041141fdd) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cf0041141fdd)
Process 60 acquiring S lock on KEY: 6:3:1 (d0002473a365) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d0002473a365)
Process 60 acquiring S lock on KEY: 6:3:1 (d100cadc1677) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d100cadc1677)
Process 60 acquiring S lock on KEY: 6:3:1 (d200afbbaacf) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d200afbbaacf)
Process 60 acquiring S lock on KEY: 6:3:1 (d300f9abc997) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d300f9abc997)
Process 60 acquiring S lock on KEY: 6:3:1 (d4009ccc752f) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d4009ccc752f)
Process 60 acquiring S lock on KEY: 6:3:1 (d5007263c03d) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d5007263c03d)
Process 60 acquiring S lock on KEY: 6:3:1 (d60017047c85) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d60017047c85)
Process 60 acquiring S lock on KEY: 6:3:1 (d700ae3cab18) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d700ae3cab18)
Process 60 acquiring S lock on KEY: 6:3:1 (d800cb5b17a0) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d800cb5b17a0)
Process 60 acquiring S lock on KEY: 6:3:1 (d90025f4a2b2) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:2:1 (cc001ccb16a2) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cc001ccb16a2)
Process 60 acquiring S lock on KEY: 6:2:1 (cd00df983b89) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cd00df983b89)
Process 60 acquiring S lock on KEY: 6:2:1 (ce009ea92090) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (ce009ea92090)
Process 60 acquiring S lock on KEY: 6:2:1 (cf00593f61df) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cf00593f61df)
Process 60 acquiring S lock on KEY: 6:2:1 (d000180e7ac6) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d000180e7ac6)
Process 60 acquiring S lock on KEY: 6:2:1 (d100db5d57ed) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d100db5d57ed)
Process 60 acquiring S lock on KEY: 6:2:1 (d2009a6c4cf4) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d2009a6c4cf4)
Process 60 acquiring S lock on KEY: 6:2:1 (d3005570d473) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d3005570d473)
Process 60 acquiring S lock on KEY: 6:2:1 (d4001441cf6a) (class bit0 ref1)
result: OK
Process 60 releasing lock on TAB: 6:21575115 []
Process 60 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
(830 row(s) affected)

Process 60 releasing lock on TAB: 6:21575115 []

use Northwind
go

dbcc freeproccache
dbcc traceon(1200,36 04)
go

set transaction isolation level read uncommitted
select * from Orders
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (5200bb3a0129) (class bit0 ref1)
result: OK

Process 61 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 61 acquiring S lock on KEY: 6:1:2 (260178426497) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 61 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:3:1 (cc0073e4c1ea) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cc0073e4c1ea)
Process 61 acquiring S lock on KEY: 6:3:1 (cd009d4b74f8) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cd009d4b74f8)
Process 61 acquiring S lock on KEY: 6:3:1 (ce00f82cc840) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (ce00f82cc840)
Process 61 acquiring S lock on KEY: 6:3:1 (cf0041141fdd) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cf0041141fdd)
Process 61 acquiring S lock on KEY: 6:3:1 (d0002473a365) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d0002473a365)
Process 61 acquiring S lock on KEY: 6:3:1 (d100cadc1677) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d100cadc1677)
Process 61 acquiring S lock on KEY: 6:3:1 (d200afbbaacf) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d200afbbaacf)
Process 61 acquiring S lock on KEY: 6:3:1 (d300f9abc997) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d300f9abc997)
Process 61 acquiring S lock on KEY: 6:3:1 (d4009ccc752f) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d4009ccc752f)
Process 61 acquiring S lock on KEY: 6:3:1 (d5007263c03d) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d5007263c03d)
Process 61 acquiring S lock on KEY: 6:3:1 (d60017047c85) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d60017047c85)
Process 61 acquiring S lock on KEY: 6:3:1 (d700ae3cab18) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d700ae3cab18)
Process 61 acquiring S lock on KEY: 6:3:1 (d800cb5b17a0) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d800cb5b17a0)
Process 61 acquiring S lock on KEY: 6:3:1 (d90025f4a2b2) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:2:1 (cc001ccb16a2) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cc001ccb16a2)
Process 61 acquiring S lock on KEY: 6:2:1 (cd00df983b89) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cd00df983b89)
Process 61 acquiring S lock on KEY: 6:2:1 (ce009ea92090) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (ce009ea92090)
Process 61 acquiring S lock on KEY: 6:2:1 (cf00593f61df) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cf00593f61df)
Process 61 acquiring S lock on KEY: 6:2:1 (d000180e7ac6) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d000180e7ac6)
Process 61 acquiring S lock on KEY: 6:2:1 (d100db5d57ed) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d100db5d57ed)
Process 61 acquiring S lock on KEY: 6:2:1 (d2009a6c4cf4) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d2009a6c4cf4)
Process 61 acquiring S lock on KEY: 6:2:1 (d3005570d473) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d3005570d473)
Process 61 acquiring S lock on KEY: 6:2:1 (d4001441cf6a) (class bit0 ref1)
result: OK
Process 61 releasing lock on TAB: 6:21575115 []

Process 61 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
(830 row(s) affected)

Process 61 releasing lock on TAB: 6:21575115 []

Aug 25 '05 #2

Eddie

Aaron,

Thanks for your input!

Eddie

*** Sent via Developersdex http://www.developersdex.com ***

Aug 25 '05 #3

Eddie

Aaron,

So after thinking about what you said abit I understand it to mean that
there is no difference between the two. So if I do not want to have to
re-write all my sql statements by adding the "WITH (NOLOCK)" to the
FROMs and INNER JOINS, some statements have many inner/left joins, then
I could just add "strSQL = 'SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED;'" to the begging of the SQL statement on my asp page! Is
that correct?

Thanks!

Eddie

*** Sent via Developersdex http://www.developersdex.com ***

Aug 25 '05 #4

Aaron Bertrand [SQL Server MVP]

Yes, however I strongly recommend you do this in a stored procedure as
opposed to having "SQL statement on my asp page"... then at least you have a
chance at fully utilizing procedure cache. It should also eliminate the
discrepancies you note in locks, since in SQL Server 2000, procedure cache
is utilized at the procedure level. (In SQL Server 2005, it will be more
granular than that.)
"Eddie" <nospam@devde x> wrote in message
news:ul******** ******@TK2MSFTN GP09.phx.gbl...

Aaron,

So after thinking about what you said abit I understand it to mean that
there is no difference between the two. So if I do not want to have to
re-write all my sql statements by adding the "WITH (NOLOCK)" to the
FROMs and INNER JOINS, some statements have many inner/left joins, then
I could just add "strSQL = 'SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED;'" to the begging of the SQL statement on my asp page! Is
that correct?

Thanks!

Eddie

*** Sent via Developersdex http://www.developersdex.com ***

Aug 25 '05 #5

Similar topics

4689

READ_UNCOMMITTED problem with SQL 2000 and i-net Opta 2000 JDBC

by: neo | last post by:

Hi, I have a problem to set a JDBC connection as READ UNCOMMITED. setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED) This is causing lots of blocking on tables and update or insert doesn't work properly. Does anyone have any idea what I'm missing here?

Microsoft SQL Server

3612

manage transaction to avoid locks

by: extmb | last post by:

Hi, I am quite puzzled how SQLServer manages transactions. Whatever the isolation level I set when performing an insertion, other connections do not have access to the table in select mode. Example in SQL Analyzer: create table foo ( id numeric(10), data varchar(100) )

Microsoft SQL Server

2368

Question about Transaction IsolationLevel

by: Mike P | last post by:

I am using transactions on my website and the Isolation Level is ReadCommitted. Since the website has a lot of traffic this may be causing it to lock up every now and again. Can somebody tell me what IsolationLevel I should use if I want the user to be able to read while a record is being inserted/updated, as I'm not sure that the Isolation Level I am using is the right one to go for. Any assistance would be really appreciated.

C# / C Sharp

3456

with nolock problem

by: Rycho | last post by:

Hi, Is there a way to catch every select comming to sql and change it to select with nolock? or how to make database READ UNCOMMITTED permanent? any ideas? Richard

Microsoft SQL Server

3791

SQLite (with APSW) and transaction separate

by: Christian Stooker | last post by:

Part one: ====== Hi ! I want to use SQLite database like the FireBird database: with big isolation level. What's that meaning ? I have an application that periodically check some input directory,

Python

2191

transaction isolation level

by: Mark | last post by:

Hello, I'm using the following code implementing transactions: Using trans1 As New Transactions.TransactionScope 'Data manipulations here! End using How do I change the transaction isolation level?

Visual Basic .NET

6966

Download the JAVA , .NET and SQL Server interview with answers

by: Jobs | last post by:

Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip

C# / C Sharp

7219

Question about Transaction isolation level="readCommitted"

by: D. | last post by:

I have a question about the "readCommitted" transaction isolation level. I have a client that is updating a record on a table. I suspend the execution after the UPDATE but before the commit statement. Than another client is trying to read the same record. As transaction isolation is set to "readCommited" I expected that the second client will read the old version of the record (before the update). Instead, the second client hangs and...

Microsoft SQL Server

1943

Transaction Isolation Level

by: qhjghz | last post by:

Hi All, The transaction isolation level for my stored procedure is READ COMMITTED. As far as I read, in this isolation level, "In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run." But, I observed a deviation from this. In my session, I am explicitly starting a transaction and inserting a row into the Property table (say). This property belongs to the City Maine(say). However, I am...

Microsoft SQL Server

9589

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10216

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10049

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9997

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8873

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5448

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3965

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3565

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2815

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General