SELECT WITH NOLOCKS

  • All the locking hints are just that.. hints.

    Right? 🙂

  • GilaMonster (3/19/2008)


    Matt Miller (3/18/2008)


    I'd have to agree. Just about the only thing preventing NOLOCK would be a schema-lock, or something changing your DDL. Do you have something routinely modifying your table structure????

    It could be that the deadlocks are from data modifications (update insert, delete), since they will always take locks.

    I can't recall ever seeing a deadlock that didn't have al least one data modificaion (or an X locking hint) involved.

    Hmm - I was under the impression that NOLOCK would just bypass those things, and/or read the "uncommitted" version. BOL seems to imply that NO data lock (including those during inserts/updates/deletes) "stop" NOLOCK.

    Of course - that's the theory, and since I don't use NOLOCK, I don't know if practice follows suit. Is that not the case?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • You should lookat using the new row versioning or snapshot isolation levels in SQL Server 2005 to deal with these problems.

  • Right SSCCHASING. We can have very good options like Readcommited Snapshot Isolation level that will work on process level and Snapshot Isolation level work on transaction level with the concept of row versioning but it will create some burdon on SQL Server if ur tables have heave updates and inserts then its not good practice to use Snapshot Isolation Level .

  • Matt Miller (3/20/2008)


    GilaMonster (3/19/2008)


    Matt Miller (3/18/2008)


    I'd have to agree. Just about the only thing preventing NOLOCK would be a schema-lock, or something changing your DDL. Do you have something routinely modifying your table structure????

    It could be that the deadlocks are from data modifications (update insert, delete), since they will always take locks.

    I can't recall ever seeing a deadlock that didn't have al least one data modificaion (or an X locking hint) involved.

    Hmm - I was under the impression that NOLOCK would just bypass those things, and/or read the "uncommitted" version. BOL seems to imply that NO data lock (including those during inserts/updates/deletes) "stop" NOLOCK.

    Of course - that's the theory, and since I don't use NOLOCK, I don't know if practice follows suit. Is that not the case?

    No... like I said earlier... WITH (NOLOCK) only affects SELECT's. The following is from Books Online... (or are we talking something different?)

    NOLOCK

    Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/20/2008)


    No... like I said earlier... WITH (NOLOCK) only affects SELECT's. The following is from Books Online... (or are we talking something different?)

    NOLOCK

    Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

    I'm totally clear on NOLOCK only working on Select (the BOL reference I remember is even more specific that that - state that NOLOCK will be ignored when used in an UPDATE/INSERT/DELETE statement).

    I was just not clear if Gail's getting at something restricting even that (meaning some circumstance where it's ignored/doesn't work even on a SELECT).

    Just for reference purposes - SQL 2005's BOL Table hints topic states this:

    READUNCOMMITTED

    Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, or present users with data that was never committed.

    READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).

    READUNCOMMITTED, and NOLOCK, cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

    Note:

    Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of Microsoft SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

    I guess I'm asking if that's the whole story, or if they're not fessing up to something.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • In addition, I invite you to read this articles regarding the DeadLock, it's really important to understand what deadlock is.

    - Understand the Deadlocking

    - Troubleshooting Deadlocks

    - Minimizing Deadlocks

    -Bart Duncan's SQL weblog

    I hope that it will help you


    Kindest Regards,

    degrem_m
    Degremont

  • Per BOL:

    All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

    Thus, I'd look for procs that issue DDL commands causing the deadlocks.

    I'm pretty sure "Disable Trigger" counts as a DDL command in this context. So if there are procs that are enabling and disabling triggers (a practice I've seen), they will most likely end up with schema locks. Those can still end up with deadlocks, even if the Select has NoLock on it, as per BOL.

    (There's gotta be a way to test that hypothesis. Time for some research and testing.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Regardless of what the cause of the deadlock is, I guarantee it's going to be code that has a BEGIN TRAN/COMMIT in it... unless someone went nuts using that everywhere, it's a pretty good first step towards wittling down what the code is that's causing it. Having trace flag 1204 turned on is almost essential towards figuring out where to go from there.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Matt Miller (3/20/2008)


    Jeff Moden (3/20/2008)


    No... like I said earlier... WITH (NOLOCK) only affects SELECT's. The following is from Books Online... (or are we talking something different?)

    NOLOCK

    Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

    I'm totally clear on NOLOCK only working on Select (the BOL reference I remember is even more specific that that - state that NOLOCK will be ignored when used in an UPDATE/INSERT/DELETE statement).

    I was just not clear if Gail's getting at something restricting even that (meaning some circumstance where it's ignored/doesn't work even on a SELECT).

    Nope. Just that if both halves of the deadlock are data modifications (which isn't unusual, considering that by default select locks are released as soon as the read is complete, making it hard for a select alone to be half of the deadlock), no amount of nolock is going to help

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden (3/21/2008)


    Regardless of what the cause of the deadlock is, I guarantee it's going to be code that has a BEGIN TRAN/COMMIT in it...

    The other place to look is trigger code, since triggers run within the transaction that fired them, even if no transaction was explicitly created.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GSquared (3/21/2008)


    (There's gotta be a way to test that hypothesis. Time for some research and testing.)

    I can confirm your theory. I've seen (On this forum in fact) a deadlock graph with a Sch-S lock on one side and a Sch-M lock on the other. Was caused by a process that either dropped or disabled triggers before running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Michael Valentine Jones (3/20/2008)


    You should lookat using the new row versioning or snapshot isolation levels in SQL Server 2005 to deal with these problems.

    Great features but they come at a price: heavy load on tempdb. Extensive app testing is required to size the tempdb files appropriately before trying this in a prod system.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply