A System Administrator can configure a server-wide lock-wait limit with the configuration parameter lock wait period. The syntax is:
sp_configure "lock wait period" [, no_of_seconds]
If the lock-wait period expires before a command acquires a lock, unless there is an overriding set lock wait or lock table wait period, the command fails, the transaction containing it is rolled back, and the following error message is generated:
Msg 12205, Level 17, State 2: Server ’wiz’, Line 1: Could not acquire a lock within the specified wait period. SERVER level wait period=300 seconds, spid=12, lock type=shared page, dbid=9, objid=2080010441, pageno=92300, rowno=0. Aborting the transaction.
A time limit entered through set lock wait or lock table wait overrides a server-level lock-wait period. Thus, for example, if the server-level wait period is 5 seconds and the session-level wait period is 10 seconds, an update command waits 10 seconds to acquire a lock before failing and aborting its transaction.
The default server-level lock-wait period is effectively “wait forever.” To restore the default after setting a time-limited wait, use sp_configure to set the value of lock wait period as follows:
sp_configure "lock wait period", 0, "default"
