lock table

Description

Explicitly locks a table within a transaction.

Syntax

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

Parameters

table_name

specifies the name of the table to be locked.

share | exclusive

specifies the type of lock, shared or exclusive, to be applied to the table.

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

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

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.

See also

Commands set