The LOCK TABLE statement prevents concurrent application
processes from using or changing 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
- DATAACCESS authority
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, DB2® 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