Lock timeouts

You can specify the amount of time that a task waits for a lock:

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 timeouts 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.

Use sp_sysmon to monitor the number of tasks that exceed the time limit while waiting for a lock.

See “Lock time-out information” in Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.