Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

LOCK TABLE

Description

Explicitly locks the specified tables for shared or exclusive access.

Syntax

LOCK TABLE table_name [ , ... ]

IN { SHARE | EXCLUSIVE } MODE

[ NOWAIT ] ;

Notes

  • Explicit locking can be used to improve the performance of a single transaction at the cost of decreasing the concurrency of the system and potentially blocking other transactions. It is more efficient to explicitly lock a table if you know ahead of time that the transaction would be updating a substantial part of a table. The efficiency is gained by decreased overhead of the implicit locking mechanism and any potential waits for acquiring page level locks for the table.
  • Explicit locking can be used to minimize potential deadlocks in situations where a substantial part of a table is being modified by a transaction. The benefits of table locking should always be compared with the disadvantages of losing concurrency before a choice is made between explicit and implicit locking.
  • The SHARE mode allows other transactions to read the table but does not allow modifications on the table.
  • The EXCLUSIVE mode does not allow any other transactions to read and/or modify the table.
  • If the lock request cannot be honored by the system (due to a conflict lock held by another transaction) then in the normal case the transaction is suspended until the specified lock can be acquired. The NOWAIT option provides an immediate return of control if the lock cannot be acquired.
  • Locks that are acquired explicitly and/or implicitly are released only when the transaction is ended using either the COMMIT or the ROLLBACK WORK statement.

Example

LOCK TABLE custmaster

IN EXCLUSIVE MODE ;

Authorization

The user executing this statement must have any of the following privileges:

  • DBA, or INSERT, UPDATE, DELETE, or ALTER privilege.

    SQL Compliance

    SQL-92

    Environment

    Embedded SQL, interactive SQL

    Related Statements

    SELECT, INSERT, DELETE

TOCIndex