DB2 10.5 for Linux, UNIX, and Windows

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 diagram
>>-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

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