THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Flexibility When Waiting on Locks

Speaking at a recent SQL Saturday, an attendee in one of my sessions wanted to know how they could more flexibly react to locks on their application than to wait for blocks to occur and then kill the SPID at the head of the blocking chain.  They were also interested in some alternatives to using the  SQL Server syntax like the WITH (NOLOCK) hint, since that might have unintended consequences due to allowing reads on uncommitted data.

One alternative I suggested is the SET LOCK_TIMEOUT statement.  Since most of the attendees hadn't heard of this statement, I figured it'd make a good blog post.  

When using the statement, you can set this context for the connection, for a batch of code (such as a function or stored procedure), or for a single SQL statement (excluding a few DDL statements such as CREATE/ALTER DATABASE).  By passing a numeric value with the set statement, you specify the number of milliseconds that the statement will wait for a lock to be released before returning a locking error.  0 means don't wait at all and -1, the default, means wait forever.  Once changed, the new setting stays in effect for the remainder of the connection.  So you might want to set it back to the default if you want it to apply to only one statement, say a SELECT, in a big batch of statements.

You can also get the same behavior by using the READPAST locking hint.

Hope this helps with those troublesome locking situations!  Enjoy,

-Kev

-Follow me on Twitter

Published Thursday, May 17, 2012 9:29 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Srinivas said:

Warning : SET LOCK_TIMEOUT will rollback only current statement in a transaction without TRY CATCH handling

May 31, 2012 1:42 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement