March 20, 2008 at 9:07 am
All the locking hints are just that.. hints.
Right? 🙂
March 20, 2008 at 9:18 am
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?
March 20, 2008 at 10:00 am
You should lookat using the new row versioning or snapshot isolation levels in SQL Server 2005 to deal with these problems.
March 20, 2008 at 10:14 am
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 .
March 20, 2008 at 10:40 am
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
Change is inevitable... Change for the better is not.
March 20, 2008 at 10:51 am
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?
March 20, 2008 at 11:27 am
In addition, I invite you to read this articles regarding the DeadLock, it's really important to understand what deadlock is.
I hope that it will help you
degrem_m
Degremont
March 21, 2008 at 9:16 am
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
March 21, 2008 at 9:41 am
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
Change is inevitable... Change for the better is not.
March 21, 2008 at 2:26 pm
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
March 21, 2008 at 2:32 pm
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
March 21, 2008 at 2:38 pm
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
March 21, 2008 at 7:10 pm
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