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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 scenarios for table lock

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.



Cheers
MIK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-25 : 16:28:56
A long running transaction, an orphaned transaction, or a transaction that is waiting on other resources that are locked.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 hints
3) Dividing long running transactions in to small chunks

Actually I am trying to write kind of document on this.

Thank you!

Cheers
MIK
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -