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

 

[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 []

 

[Eddie]

Aaron,

Thanks for your input!

Eddie

 

[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

 

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