In this blog post, we will explore the concept of SET LOCK_TIMEOUT in SQL Server and understand how it can be used to control waiting time for locked queries.
SET LOCK_TIMEOUT is a setting that can be used at the connection level in SQL Server. By default, the value of SET LOCK_TIMEOUT is -1, which means that a query will wait indefinitely for a lock to be released on another query.
However, if we want to simulate the behavior of the NOWAIT query hint, which returns an error immediately if a query is locked, we can set the value of SET LOCK_TIMEOUT to 0.
Let’s take a look at an example to see how SET LOCK_TIMEOUT works:
USE tempdb
GO
CREATE TABLE First (
ID INT,
Col1 VARCHAR(10)
)
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO
-- First Connection
BEGIN TRAN
DELETE FROM First WHERE ID = 1
-- Second Connection
SET LOCK_TIMEOUT 2000
BEGIN TRAN
SELECT ID, Col1 FROM First WHERE ID = 1
In this example, we have two different connections. In the first connection, we start a transaction and delete a row from the “First” table. In the second connection, we set the value of SET LOCK_TIMEOUT to 2000 milliseconds and start a transaction to select a row from the same table.
The query in the second connection will wait for 2 seconds for the lock to be released by the first connection. If the lock is not released within the specified time, an error message will be displayed:
Msg 1222, Level 16, State 45, Line 3
Lock request time out period exceeded.
It is important to note a couple of differences between SET LOCK_TIMEOUT and the NOWAIT query hint:
- The time to wait before throwing an error can be configured in SET LOCK_TIMEOUT, whereas in NOWAIT it is always zero.
- The scope of SET LOCK_TIMEOUT is the entire connection, while the scope of NOWAIT is limited to the table where it is applied.
In conclusion, SET LOCK_TIMEOUT provides more flexibility in controlling waiting time for locked queries and can achieve the same purpose as the NOWAIT query hint. In my daily routine, I often use SET LOCK_TIMEOUT as it allows me to customize the waiting time according to my requirements.