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 Post Reply

14856

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,3604)
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,3604)
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@devdex> wrote in message
news:ul**************@TK2MSFTNGP09.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

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...

Microsoft SQL Server

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. ...

Microsoft SQL Server

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...

C# / C Sharp

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

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...

Python

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...

Visual Basic .NET

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...

C# / C Sharp

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...

Microsoft SQL Server

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...

Microsoft SQL Server

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

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,...

General

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,...

C / C++

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...

Online Marketing

with (NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

Similar topics