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

14824

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

Windows 11 and Access Combo Box Issue

by: DJRhino | last post by:

Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

Microsoft Access / VBA

Access Europe meeting on Wed 4 Oct - The future of Access

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to find best mobile app development company in usa

by: Aliciasmith | last post by:

In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...

Mobile Development

Can faster file sharing be achieved by connecting computers simultaneously via WiFi and LAN cables

by: tracyyun | last post by:

Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...

Networking - Hardware / Configuration

Energy Model code modification to account for year dependent interest rate

by: giovanniandrean | last post by:

The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...

Python

Report value list

by: Teri B | last post by:

Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...

Microsoft Access / VBA

Finding a record, and then saving a new record after the record has been amended.

by: nia12 | last post by:

Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...

Microsoft Access / VBA

VBA Class Basics (Report Class)

by: NeoPa | last post by:

Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

Microsoft Access / VBA

Access Europe Meeting - Wed 6 Dec - Streamline Your Import/Export Spec Workflow with VBA Classes

by: isladogs | last post by:

The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

Microsoft Access / VBA

with (NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

Similar topics