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:

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

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 (http://blog.sqlauthority.com)

About these ads

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

    • @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.

      Like

  1. Pingback: SQL SERVER – Difference Between NOLOCK and NOWAIT Hints « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s