THE SQL Server Blog Spot on the Web

Welcome to - 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,


-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



Srinivas said:

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

May 31, 2012 1:42 AM

obat batuk kronis said: Obat herbal kolesterol terbaik Obat Batuk Kronis Alami Terbaik Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 27, 2018 8:30 PM

Leave a Comment


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,

This Blog



Privacy Statement