Microsoft® SQL Server 2012 Unleashed

Setting the Lock Timeout Interval

If you do not want a process to wait indefinitely for a lock to become available, SQL Server allows you to set a lock timeout interval by using the SET LOCK_TIMEOUT command. You specify the timeout interval in milliseconds. For example, if you want your processes to wait only 5 seconds (that is, 5,000 milliseconds) for a lock to become available, you execute the following command in the session:

SET LOCK_TIMEOUT 5000

If your process requests a lock resource that cannot be granted within 5 seconds, the statement is aborted, and you get the following error message:

Click here to view code image

Server: Msg 1222, Level 16, State 52, Line 1Lock request time out period exceeded.

To examine the current LOCK_TIMEOUT ...