You can use set lock wait to control the length of time that a command in a session or in a stored procedure waits to acquire locks. The syntax is:
set lock {wait no_of_seconds | nowait}
no_of_seconds is an integer. Thus, the following example sets a session-level time limit of 5 seconds on waiting for locks:
set lock wait 5
With one exception, if the set lock wait period expires before a command acquires a lock, the command fails, the transaction containing it is rolled back, and the following error message is generated:
Msg 12205, Level 17, State 2: Server ’sagan’, Line 1: Could not acquire a lock within the specified wait period. SESSION level wait period=300 seconds, spid=12, lock type=shared page, dbid=9, objid=2080010441, pageno=92300, rowno=0. Aborting the transaction.
The exception to this occurs when lock table in a transaction sets a longer wait period than set lock wait. In this case, the transaction uses the lock table wait period before timing out, as described in the preceding section.
The set lock nowait option is equivalent to the set lock wait option with a 0-second wait. If a command other than lock table cannot obtain a requested lock immediately, the command fails, its transaction is rolled back, and the preceding error message is generated.
If both a server-wide lock-wait limit and a session-level lock-wait limit are set, the session-level limit takes precedence. If no session-level wait period is set with set lock wait, the server-level wait period is used.
