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.
