Within a transaction, the lock table command allows you to request a table lock on a table without waiting for the command to acquire enough row-level or page-level locks to escalate to a table lock.
The lock table command contains a wait/nowait option that allows you to specify the length of time the command waits until operations in other transactions relinquish any locks they have on the target table.
The syntax for lock table is:
lock table table_name in {share | exclusive} mode
[wait [no_of_seconds] | nowait]
The following command, inside a transaction, sets a wait period of 2 seconds for acquiring a table lock on the titles table:
lock table titles in share mode wait 2
If the wait time expires before a table lock is acquired, the transaction proceeds, and row or page locking is used exactly as it would have been without lock table, and the following informational message (error number 12207) is generated:
Could not acquire a lock within the specified wait period. COMMAND level wait...
For a code example of handling this error message during a transaction, see the Reference Manual: Commands.
If you use lock table...wait without
specifying no_of_seconds,
the command waits indefinitely for a lock.
You can set time limits on waiting for a lock at the session level and the system level, as described in the following sections. The wait period set with the lock table command overrides both of these
The nowait option is equivalent to the wait option with a 0-second wait: lock table either obtains a table lock immediately or generates the informational message given above. If the lock is not acquired, the transaction proceeds as it would have without the lock table command.
You can use the set lock command at either the session level or within a stored procedure to control the length of time a task waits to acquire locks.
A system administrator can use the sp_configure option, lock wait period, to set a server-wide time limit on acquiring locks.
