January 12, 2012 at 1:38 pm
Hi everybody,
I need to disable lock escalation for a SELECT ... FROM table_name that is part of a stored procedures that runs every night (and inserts data from table_name into other tables).
That query has to use a table scan, because its WHERE clause is something like :
WHERE field_name IS NOT NULL AND field_name!=''.
I know there is the ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE), is there such a thing for SELECTs ?
I don't want to disable lock escalation at the server level (so I can't use flag 1211 (?)), and as far as I know that table hints don't actually prevent lock escalation...
Any ideas on this one???
Virgil Rucsandescu
January 12, 2012 at 1:52 pm
I think you want something in this article http://msdn.microsoft.com/en-us/library/ms181714.aspx. Since it is going to do a table scan you might want to specify the TABLOCK hint since it may escalate to that anyway. I'm not a n expert on table and query hints though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 12, 2012 at 1:57 pm
I have to prevent escalating because this causes deadlocks in queries like :
INSERT INTO table_name ...blablabla... and this is not acceptable 🙁
January 12, 2012 at 2:08 pm
Lock escalation in and of itself wouldn't cause deadlocking, blocking and perhaps timeouts, but not deadlocks. Now it could be part of the problem when getting deadlocks, but it isn't the sole problem. Also specifying TABLOCK might solve the deadlocking because no other resource would be able to acquire an incompatible lock on any resources on the table while that select query is running, so there wouldn't be a circular locking/blocking chain that causes a deadlock.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 12, 2012 at 2:28 pm
You cannot completely stop selects from shared locking unless you use a read uncommitted transaction level or the NOLOCK hint. You also can't stop lock escalation.
However, this brings us to a different issue. When locks reach 5000+ locks, they try to escalate to table level. If they cannot however, they continue to use the locking mechanism they were using (row/page). Thus attempting to escalate and failing does no specific damage other than using more memory.
If you have an update process that is locking other records and these run into each other, the escalation is not the problem. They are attempting to access the same records. Any lock at this point gets jammed. It's not the escalation that's the problem. It's the crossover of the record volume.
You have three ways out of this. Make sure your select (or update) does a mass shared lock when it goes in, as Jack recommended, and hint it with a TABLOCK. That may mean that it waits awhile. The second option is to use the NOLOCK mechanism, which is fraught with hazards and you need to understand the true implications of using uncommitted records before you proceed on that path. The third is to implement Snapshot Isolation, which moves versions of records into the tempdb while updates happen under the hood to avoid this exact scenario. It's more resource intensive but was built as a solution to this exact problem (amongst others).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 2:37 pm
If it has to select the whole table, or most of it, it's going to have to lock the table anyway.
A Select statement by itself can't really cause a deadlock, because all it takes is a shared lock. But if you're using the Select as part of Insert/Update/Delete transactions, it can definitely cause deadlocks as part of that. It sounds like that's what you're using the Select for, which is probably where the deadlocks are coming in.
You have a few possible solutions:
Snapshot isolation, either at the database level (Read-Committed Snapshot Isolation enabled) or in the query itself (Set Transaction Isolation Level Snapshot). Either one should eliminate the locking you're running into from that query, but can have unintended consequences if you have large volumes of updates going on concurrently (large numbers of rows being versioned into tempdb), or if the Select is part of an actual data modification, in which case it can cause other issues in tempdb.
TablockX. Use TablockX on the Select and other queries won't deadlock with it, but it will be delayed while other queries finish up, and will delay anything else while it's still got the table locked. Eliminate the deadlocks but possibly slow things down.
Actual snapshots. Only available if you're in Enterprise Edition, if I'm not mistaken. Create a snapshot of the database, and query that instead of the primary copy of the table. No blocking/deadlocking.
Replication/Log Shipping/et al, to create a separate copy of the table and use that as your data source. Works with Standard Edition, unlike Snapshots, but it's a lot more complex to set up and administer, and requires (usually) a lot more disk space.
Bulk copy of the data via SSIS. SSIS can do some pretty neat tricks with bulk copying data from one server/database to another, with minimal locking in most cases.
Do any of those sound viable to you?
And, to clarify, is this a stand-alone Select, or is it part of an Insert Select, Update From, Select Into, or other data-modifying query?
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply