Explicitly locks one or more specified tables for shared or exclusive access.

Syntax

LOCK TABLE table_name[ , table_name] , ... 
  IN { SHARE | EXCLUSIVE } MODE ;

Parameters

table_name

The table in the database that you want to lock explicitly. You can specify one table or a comma-separated list of tables.

SHARE MODE

Allows all transactions to read the tables. Prohibits all other transactions from modifying the tables. After you acquire an explicit lock on a table in SHARE MODE, any SELECT statements in your transaction can read rows and do not implicitly acquire individual record locks. Any INSERT, UPDATE, and DELETE statements do acquire record locks.

EXCLUSIVE MODE

Allows the current transaction to read and modify the tables, and prohibits any other transactions from reading or modifying the tables. After you acquire an explicit lock on a table in EXCLUSIVE MODE, you can SELECT, INSERT, UPDATE, and DELETE rows, and your transaction does not implicitly acquire individual record locks for these operations.

Notes

  • The LOCK TABLE statement might encounter a locking conflict with another transaction.
  • The SHARE MODE option detects a locking conflict if another transaction:
    • Locked the table in EXCLUSIVE MODE and has not issued a COMMIT or ROLLBACK
    • Inserted, updated, or deleted rows in the table and has not issued a COMMIT or ROLLBACK
  • The EXCLUSIVE MODE option detects a locking conflict if another transaction:
    • Locked the table in SHARE MODE or EXCLUSIVE MODE and has not issued a COMMIT or ROLLBACK
    • Read from, inserted, updated, or deleted rows and has not issued a COMMIT or ROLLBACK
  • When there is a locking conflict, the transaction is suspended and the database returns an error. You might configure the time at which the transaction is suspended. The default is five seconds.
  • You can use explicit table locking 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 lock a table explicitly if you know that the transaction will be updating a substantial part of a table. You gain efficiency by decreasing the overhead of the implicit locking mechanism, and by decreasing any potential wait time for acquiring individual record locks on the table.
  • You can use explicit table locking to minimize potential deadlocks in situations where a transaction is modifying a substantial part of a table. Before making a choice between explicit or implicit locking, compare the benefits of table locking with the disadvantages of losing concurrency.
  • The database releases explicit and implicit locks only when the transaction ends with a COMMIT or ROLLBACK operation.

Example

Unless another transaction holds an EXCLUSIVE lock on the teratab and megatab tables, the SHARE MODE example explicitly locks the tables. The shared lock allows all transactions to read the tables. Only the current transaction can modify the tables, as shown in the following example:

LOCK TABLE teratab, megatab IN SHARE MODE ;

Unless another transaction holds a lock on the teratab table, the EXCLUSIVE MODE example locks the teratab table for exclusive use by the current transaction. No other transactions can read or modify the teratab table, as shown in the following example:

LOCK TABLE teratab IN EXCLUSIVE MODE ;

Without a table lock, the first SELECT statement in the following example could exceed the limits of the record lock table, while the LOCK TABLE statement prevents the subsequent SELECT statement from consuming the record lock table:

-- Without a table lock, this SELECT statement creates an
-- entry in the record lock table for every row in teratab. 
 SELECT COUNT (*) FROM teratab ;
 -- The LOCK TABLE IN SHARE MODE operation preserves the
-- record lock table resource.
 LOCK TABLE teratab IN SHARE MODE ;
SELECT COUNT (*) FROM teratab ;