Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2011-05-25 : 16:23:08
|
| Hi guys, Hope you are doing well. Can you please help me in identifying scenarios/reasons for tables that would lock a table potentially for a long period of time. Or please let know any URL where I can find this information. CheersMIK |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-06-15 : 13:24:30
|
| Hey Tara, I apologize for getting late to say thank you. :)can you please let me know the strategies through which long running transaction or, transactions that is waiting on other resources that are blocked, to be avoided or minimize? If i am correct we can use the following?1) By changing the isolation level 2) Using nolock, tablelock hints3) Dividing long running transactions in to small chunks Actually I am trying to write kind of document on this. Thank you!CheersMIK |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-15 : 13:32:42
|
| Changing the isolation level will only help if the normally blocked query is a select AND no locking hint is on the DML command. For instance, if a transaction puts a UPD lock on a SELECT and then does the UPDATE later, all SELECTs against that locked data will have to wait.Using the nolock hint should be avoided except in adhoc scenarios (like a production DBA doing a COUNT(*)) and in rare circumstances, so I would not add this to your documentation as it is highly not recommended. Doing the large transaction in smaller chunks will help avoid long blocks.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
scenarios for table lock