with (NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

Discussion in 'ASP General' started by Eddie, Aug 24, 2005.

  1. Eddie

    Eddie Guest

    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 ***
     
    Eddie, Aug 24, 2005
    #1
    1. Advertising

  2. 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 []
     
    Aaron Bertrand [SQL Server MVP], Aug 25, 2005
    #2
    1. Advertising

  3. Eddie

    Eddie Guest

    Eddie, Aug 25, 2005
    #3
  4. Eddie

    Eddie Guest

    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 ***
     
    Eddie, Aug 25, 2005
    #4
  5. 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:...
    >
    > 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 ***
     
    Aaron Bertrand [SQL Server MVP], Aug 25, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. pabbu
    Replies:
    8
    Views:
    768
    Marc Boyer
    Nov 7, 2005
  2. Replies:
    1
    Views:
    328
    santosh
    Feb 17, 2007
  3. Vencz Istv?n
    Replies:
    2
    Views:
    306
  4. Replies:
    2
    Views:
    432
    Jochen Lehmeier
    Jan 7, 2010
  5. markspace

    JDBC transaction isolation

    markspace, May 3, 2012, in forum: Java
    Replies:
    16
    Views:
    1,296
Loading...

Share This Page