How to set lock timeout

SET LOCK_TIMEOUT

The SET LOCK_TIMEOUT specifies how many milliseconds a statement should wait during a lock before returning an error.

Use the statement in your stored procedures right before the BEGIN TRANSACTION clause.

This example will wait for 5 seconds (5000 ms) before returning error in the event of a lock.

SET LOCK_TIMEOUT 5000
BEGIN TRAN
   -- Do your stuff
COMMIT TRAN