Server-Wide Lock-Wait Limit

A system administrator can configure a server-wide lock-wait limit using the lock wait period configuration parameter.

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:

sp_configure "lock wait period", 0, "default"