SQL SERVER – Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query

In earlier blog post SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query, we learned how we can use NOWAIT query hint to not wait on any locked query and return error. The Query Hint works on query and table level. There is similar setting which can work at a connection level as well,  it is SET LOCK_TIMEOUT. When any connection starts the value of the SET LOCK_TIMEOUT is -1, which means that the query has to wait for infinite time for the lock to be released on another query. If you want to simulate the scenario of SET LOCK_TIMEOUT to match NOWAIT query hint, it should be set to value 0. Let us see a similar example where we demonstrate how SET LOCK_TIMEOUT works.

First Let us create a table:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now let us open two different connections.

Run following command in the First Connection:

Solarwinds

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Run following command in the Second Connection:

SET LOCK_TIMEOUT 2000
BEGIN TRAN
SELECT
ID, Col1
FROM First
WHERE ID = 1

SQL SERVER - Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query set-lock-timeout

In this case, I have set the value of the SET LOCK_TIMEOUT to 2000 milliseconds. This query will wait for 2 seconds to another query to release the lock. If another query does not release the lock in 2 seconds, it will display the following error:

Msg 1222, Level 16, State 45, Line 3
Lock request time out period exceeded.

This is the same error which we have discussed in an earlier blog post here.

Here are a couple of very interesting differences between SET LOCK_TIMEOUT and NOWAIT query hint.

  • User can configure the time to wait before throwing error in SET LOCK_TIMEOUT, whereas in case of NOWAIT it is always zero (o). 
  • The scope of the SET LOCK_TIMEOUT is wholly connected however the scope of NOWAIT is the table where it is applied.

I guess, now it is very clear when to use what. In my daily routine I usually use SET LOCK_TIMEOUT as it gives me more flexibility and achieves the same purpose as NOWAIT.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #013
Next Post
SQL SERVER – Contest to Win Amazon Card – Experience Cloud Data Management System (CDMS) and NuoDB

Related Posts

8 Comments. Leave new

  • Thanks Pinal sir.

    Reply
  • Thanks for sharing.

    Reply
  • if we put query timeout to 600 then how much time it take sir..

    Reply
    • Imran Mohammed
      January 29, 2013 7:53 pm

      @Rajesh,

      From what I understood from Pinal Example is, the time mentioned in the set statement is in milli seconds, if you set the value as 600 it actually means 0.6 seconds, the query will wait for .6 seconds. In the example Pinal has used 2000 milli seconds which means 2 seconds, same is shown with an arrow in the screenshot.

      ~ IM.

      Reply
  • hello pinal sir

    sir what is mean by log shipping in sql server 2005

    sir i want to download turo c

    plz sir send link of download site

    Reply
  • Dan Lampkin
    May 5, 2016 8:35 pm

    Great information, does this do the same for blocked tables (as compared to locked tables)

    Reply
  • Hi,

    Is it possible to use LOCK_TIMEOUT over a linked server?
    I want to query tables using a linked server but i want to know if those tables are under any lock.

    I tested this, by simulating a lock, but my query just keeps on running without quitting.

    Using SQL Server 2014.

    Thanks

    Reply

Leave a Reply

Menu