lock table

Explicitly locks a table within a transaction.

Syntax

lock table table_name in [partition data_partition_name] in 
	{share | exclusive} mode
	[wait [numsecs] | nowait]

Parameters

table_name – specifies the name of the table to be locked.
partition data_partition – indicates you are locking a data partition.
share | exclusive – specifies the type of lock, shared or exclusive, to be applied to the table or partition.
wait numsecs – 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.
nowait – causes the command to fail if the lock cannot be acquired immediately.

Examples

Example 1 – 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
```
Example 2 – 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
```

Example 3 – 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

Usage

You can use lock table with an archive database.
If you use lock table as the first statement after the set chained on command, this creates a new transaction.
You cannot lock a table on which you previously executed the dbcc tune(des_bind…) command because the SAP ASE server does not allow shared or exclusive table locks on hot objects. For example, the SAP ASE server issues warning number 8242 if you:
- Create a table
- Run dbcc tune (des_bin. . . ). For example:
```
dbcc tune(des_bin, 4, new_table)
```
- Attempt to lock the table:
```
begin tran
lock table new_table in exclusive mode
go
```
```
Msg 8242, Level 16, State 1:
Server 'server01', Line 2:
The table ‘new_table’ in database 'big_db' is bound to metadata cache memory. Unbind the table and retry the query later.
```
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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be the table owner. 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.