Explicitly locks a table within a transaction.
lock table table_name in {share | exclusive } mode
[ wait [ numsecs ] | nowait ]
specifies the name of the table to be locked.
specifies the type of lock, shared or exclusive, to be applied to the table.
specifies the number of seconds to wait, if a lock cannot be acquired immediately. If numsecs is omitted, specifies that the lock table command should wait until lock is granted.
causes the command to fail if the lock cannot be acquired immediately.
Tries to acquire a shared table lock on the titles table. If a session-level wait has been set with set lock wait, the lock table command waits for that period of time; otherwise, the server-level wait period is used:
begin transaction lock table titles in share mode
Tries to acquire an exclusive table lock on the authors table. If the lock cannot be acquired within 5 seconds, the command returns an informational message. Subsequent commands within the transaction continue as they would have without lock table:
begin transaction lock table authors in exclusive mode wait 5
If a table lock is not acquired within 5 seconds, the procedure checks the user’s role. If the procedure is executed by a user with sa_role, the procedure prints an advisory message and proceeds without a table lock. If the user does not have sa_role, the transaction is rolled back:
create procedure bigbatch
as
begin transaction
lock table titles in share mode wait 5
if @@error = 12207
begin
/*
** Allow SA to run without the table lock
** Other users get an error message
*/
if (proc_role("sa_role") = 0)
begin
print "You cannot run this procedure at
this time, please try again later"
rollback transaction
return 100
end
else
begin
print "Couldn't obtain table lock,
proceeding with default locking."
end
end
/* more SQL here */
commit transaction
If you use lock table as the first statement after the set chained on command, this creates a new transaction.
You can use lock table only within a transaction. The table lock is held for the duration of the transaction.
The behavior of lock table depends on the wait-time options that are specified in the command or that are active at the session level or server level.
If the wait and nowait option are not specified, lock table uses either the session-level wait period or the server-level wait period. If a session-level wait has been set using set lock wait, it is used, otherwise, the server-level wait period is used.
If the table lock cannot be obtained with the time limit (if any), the lock table command returns message 12207. The transaction is not rolled back. Subsequent commands in the transaction proceed as they would have without the lock table command.
You cannot use lock table on system tables or temporary tables.
You can issue multiple lock table commands in the same transaction.
ANSI SQL – Compliance level: Transact-SQL extension.
You must have select access permission on the table to use lock table in share mode. You must have delete, insert, or update access permission on the table to use lock table in exclusive mode.
Commands set
