LOCK TABLE statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

Syntax

>>-LOCK TABLE--+-table-name-+--IN--+-SHARE-----+--MODE---------><
               '-nickname---'      '-EXCLUSIVE-'         

Description

table-name or nickname

Identifies the table or nickname. The table-name must identify a table that exists at the application server, but it must not identify a catalog table, a created temporary table, or a declared temporary table (SQLSTATE 42995). If the table-name is a typed table, it must be the root table of the table hierarchy (SQLSTATE 428DR). When a nickname is specified, the database manager will lock the underlying object (that is, a table or view) of the data source to which the nickname refers.

IN SHARE MODE

Prevents concurrent application processes from executing any but read-only operations on the table.

IN EXCLUSIVE MODE

Prevents concurrent application processes from executing any operations on the table. Note that EXCLUSIVE MODE does not prevent concurrent application processes that are running at isolation level Uncommitted Read (UR) from executing read-only operations on the table.

Notes

• Locking is used to prevent concurrent operations. A lock is not necessarily acquired during execution of the LOCK TABLE statement if a suitable lock already exists. The lock that prevents concurrent operations is held at least until termination of the unit of work.
• In a partitioned database, a table lock is first acquired at the first database partition in the database partition group (the database partition with the lowest number) and then at other database partitions. If the LOCK TABLE statement is interrupted, the table may be locked on some database partitions but not on others. If this occurs, either issue another LOCK TABLE statement to complete the locking on all database partitions, or issue a COMMIT or ROLLBACK statement to release the current locks.
• This statement affects all database partitions in the database partition group.
• For partitioned tables, the only lock acquired for the LOCK TABLE statement is at the table level; no data partition locks are acquired.
• The LOCK TABLE statement behaves similarly to a DDL statement, acquiring catalog locks for the target table. Commands or statements that require catalog locks to update table attributes or metadata cannot execute concurrently with an explicit lock table operation. Examples of activities that cannot execute concurrently with an explicitly locked table include DDL statements and the RUNSTATS command.

Example

   LOCK TABLE EMP IN EXCLUSIVE MODE