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

Examples

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.

Related reference
set