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 n 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