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