You can specify the time that a task waits for a lock:
At the server level, with the lock wait period configuration parameter
For a session or in a stored procedure, with the set lock wait command
For a lock table command
See the Transact-SQL Users’ Guide for more information on these commands.
Except for lock table, a task that attempts to acquire a lock and fails to acquire it within the time period returns an error message and the transaction is rolled back.
Using lock time-outs can be useful for removing tasks that acquire some locks, and then wait for long periods of time blocking other users. However, since transactions are rolled back, and users may simply resubmit their queries, timing out a transaction means that the work needs to be repeated.
You can use sp_sysmon to monitor the number of tasks that exceed the time limit while waiting for a lock.
See “Lock time-out information” on page 277 in the Performance and Tuning: Monitoring and Analyzing.
