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)
9 Comments. Leave new
Thanks Pinal sir.
Thanks for sharing.
if we put query timeout to 600 then how much time it take sir..
@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.
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
Great information, does this do the same for blocked tables (as compared to locked tables)
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
Thank you for information.
I’m using Microsoft SQL Server 2014 and interested in assigning LOCK_TIMEOUT temporarily in a program and then setting it back – because some of our data processing takes a very long time when it’s working correctly (this may be bad design but out of my control), but I also have a program that tries to drop then create a table and it got hung on the drop for unknown reason. I want to prevent that from happening again.
From experiment and from elsewhere, this can be done within dynamic SQL e.g. EXEC (‘SET LOCK_TIMEOUT 5000; DROP TABLE DoesNotWantToBeDropped’) The LOCK_TIMEOUT returns to the previous setting after this statement. Also (I’m told) if a stored procedure includes SET LOCK_TIMEOUT, then its effect only applies while the stored procedure is executing. This is usual for SET statements.
Luckily, that is where I wanted to use it. Because a catch seems to be is that while I can store the value of @@LOCKWAIT in a variable i.e. SET @timeout = @@LOCK_TIMEOUT, the statement SET LOCK_TIMEOUT won’t take a variable as its parameter…?
Okay, I thought, I will do, SET @ctimeout = STR(@@LOCK_TIMEOUT); ….; EXEC (‘SET LOCK_TIMEOUT ‘ + @ctimeout). But as I said, it didn’t work.