Use the LOCK statement to manually lock a table and limit access to that object. You then use the UNLOCK statement to manually unlock the table, see UNLOCK TABLE.
Manual locking is not needed very often. The server automatic locking is usually sufficient. For more information about locking in general, and the server automatic locking in particular, see Concurrency control and locking.
Explicit locking of tables is primarily intended to help database administrators execute maintenance operations in a database without being disturbed by other users. For example, manual locking is typically used in Advanced Replication setups when making schema changes. For more details, see Upgrading schemas in a distributed system by using Maintenance Mode features.
The LOCK statement takes effect at the time it is executed and is released if you roll back the transaction in which the lock was placed.
The LOCK/UNLOCK TABLE statements apply only to tables. There is no statement to manually lock or unlock individual records within a table.
You can lock more than one table and specify different modes within one LOCK statement. If the LOCK statement fails, then none of the tables are locked. If the LOCK statement is successful, then all requested locks are granted.
Parameters, clauses, keywords, and variables
▪ table‑name: The name of the table to lock. You can also specify the catalog and schema of the table by qualifying the table name. You can only lock tables, not views.
▪ lock-mode:
– SHARED: Allows others to perform read and write operations on the table but not DDL operations (ALTER TABLE, DROP TABLE). More than one user at a time can have shared locks on a table. However, no user can get an exclusive lock on a table when another user has a shared lock (or exclusive lock) on that table.
– EXCLUSIVE: If a D-table uses pessimistic locking, then an exclusive lock prevents other users from accessing the table in any way (for example, inserting or deleting data, DDL operations, acquiring a lock), except for SELECT statements.
In the case of M-tables (always pessimistic) and optimistic D-tables, an exclusive lock allows other users to perform SELECT and SELECT FOR UPDATE statements on the locked table but prohibits any other activity (inserting or deleting data, DDL operations, acquiring a lock) on that table.
– LONG: By default, locks are released at the end of a transaction. If the LONG option is specified, then the lock is not released when the locking transaction commits and you can keep the table exclusively locked while executing a set of transactions. If the locking transaction aborts or is rolled back, then all locks, including LONG locks, are released. You must unlock LONG locks explicitly by using the UNLOCK statement. LONG duration locks are allowed only in EXCLUSIVE mode.
▪ wait‑option:
– NOWAIT: Specifies that control is returned to you immediately even if any specified table is locked by another user. If the requested lock is not granted, an error is returned.
– WAIT: Specifies a timeout (in seconds) for how long system should wait to get requested locks. If requested lock is not granted within that time, an error is returned.
If the user does not specify a wait option then the default wait time is used, which is the same as the deadlock detection timeout. The WAIT option is effective on disk-based tables only.
Return values
Error code
Description
10014
Resource is locked.
13047
No privilege for operation.
13011
Table <tablename> is not found.
Examples
LOCK TABLE emp IN SHARED MODE;
LOCK TABLE emp IN SHARED MODE TABLE dept IN EXCLUSIVE MODE;