SQL Reference

LOCK TABLE

The LOCK TABLE statement either prevents concurrent application processes from changing a table or prevents concurrent application processes from using a table.

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

The privileges held by the authorization ID of the statement must include at least one of the following:

• SELECT privilege on the table
• CONTROL privilege on the table
• SYSADM or DBADM authority.

Syntax

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

Description

table-name

Identifies the table. The table-name must identify a table that exists at the application server, but it must not identify a catalog table. It cannot be a nickname (SQLSTATE 42809) 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).

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 the execution of the LOCK TABLE statement if a suitable lock already exists. The lock that prevents concurrent operations is held at least until the termination of the unit of work.
• In a partitioned database, a table lock is first acquired at the first partition in the nodegroup (the partition with the lowest number) and then at other partitions. If the LOCK TABLE statement is interrupted, the table may be locked on some partitions but not on others. If this occurs, either issue another LOCK TABLE statement to complete the locking on all partitions, or issue a COMMIT or ROLLBACK statement to release the current locks.
• This statement affects all partitions in the nodegroup.

Example

Obtain a lock on the table EMP. Do not allow other programs either to read or update the table.

   LOCK TABLE EMP IN EXCLUSIVE MODE