LOCK TABLE statement

The LOCK TABLE statement prevents concurrent application processes from using or changing a table. The lock is released when the unit of work issuing the LOCK TABLE statement either commits or terminates.

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 authorities:
  • SELECT privilege on the table
  • CONTROL privilege on the table
  • DATAACCESS authority

Syntax

Read syntax diagramSkip visual syntax diagramLOCK TABLE table-namenickname IN SHAREEXCLUSIVE MODE

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.

Example

Obtain a lock on the table EMP. Do not allow other programs to read or update the table. 
   LOCK TABLE EMP IN EXCLUSIVE MODE