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.
//blog.sqlauthority.com/2013/01/28/sql-server-basic-explanation-of-set-lock_timeout-how-to-not-wait-on-locked-query/
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.
Comments are closed