Specifies the number of milliseconds a statement waits for a lock to be released.
SET LOCK_TIMEOUT timeout_period
Is the number of milliseconds that will pass before Microsoft® SQL Server™ returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever).
When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means not to wait at all and return a message as soon as a lock is encountered.
At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.
The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.
The READPAST locking hint provides an alternative to this SET option.
SET LOCK_TIMEOUT permissions default to all users.
This example sets the lock time-out period to 1,800 milliseconds.
SET LOCK_TIMEOUT 1800 GO