The lock table command allows you to explicitly request that before a table is accessed, a table lock be put on it for the duration of a transaction. This is useful when an immediate table lock may reduce the overhead of acquiring a large number of row or page locks and save locking time. Examples of such cases are:
A table will be scanned more than once in the same transaction, and each scan may need to acquire many page or row locks.
A scan will exceed a table’s lock-promotion threshold and will therefore attempt to escalate to a table lock.
If a table lock is not explicitly requested, a scan acquires page or row locks until it reaches the table’s lock promotion threshold (see Reference Manual: Procedures), at which point it tries to acquire a table lock.
The syntax of lock table is:
lock table table_name in {share | exclusive} mode
[wait [no_of_seconds] | nowait]
The wait/nowait option allows you to specify how long the command waits to acquire a table lock if it is blocked (see “wait/nowait option of the lock table command”).
These considerations apply to the use of lock table:
You can issue lock table only within a transaction.
You cannot use lock table on system tables.
You can first use lock table to lock a table in share mode, then use it to upgrade the lock to exclusive mode.
You can use separate lock table commands to lock multiple tables within the same transaction.
Once a table lock is obtained, there is no difference between a table locked with lock table and a table locked through lock promotion without the lock table command.
