LOCK TABLE

The LOCK TABLE statement requests a lock on a table or table space at the current server. The lock is not acquired if the process already holds an appropriate lock.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privilege set that is defined below must include at least one of the following:

  • The SELECT privilege on the identified table (the SELECT privilege does not apply to the auxiliary table)
  • Ownership of the table
  • DBADM authority for the database
  • SYSADM or SYSCTRL authority
  • Start of changeDATAACCESS authorityEnd of change

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Table 1. (For more details on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.)

Syntax

Read syntax diagram
>>-LOCK TABLE--table-name--+--------------------+--------------->
                           '-PARTITION--integer-'   

>--IN--+-SHARE-----+--MODE-------------------------------------><
       '-EXCLUSIVE-'         

Description

table-name
Start of changeIdentifies the table to be locked. The name must identify a table that exists at the current server. Start of changeIt must not identify a view, a temporary table (created or declared), an accelerator-only table, a catalog table, or a directory table.End of change The lock might or might not apply exclusively to the table. The effect of locking an auxiliary table is to lock the LOB table space that contains the auxiliary table.End of change
PARTITION integer
Identifies the partition of a partitioned table space to lock. The table identified by table-name must belong to a partitioned table space. The value specified for integer must be an integer that is no greater than the number of partitions in the table space.
IN SHARE MODE
For a lock on a table that is not an auxiliary table, requests the acquisition of a lock that prevents other processes from executing anything but read-only operations on the table. For a lock on a LOB table space, IN SHARE mode requests a lock that prevents storage from being reallocated. When a LOB table space is locked, other processes can delete LOBs or update them to a null value, but they cannot insert LOBs with a nonnull value. The type of lock that the process holds after execution of the statement depends on what lock, if any, the process already holds.
IN EXCLUSIVE MODE
Requests the acquisition of an exclusive lock for the application process. Until the lock is released, it prevents concurrent processes from executing any operations on the table.
Exception: Unless the lock is on a LOB table space, concurrent processes that are running at an isolation level of uncommitted read (UR) can execute read-only operations on the table.

Start of changeBe aware that an exclusive lock on a table space, table, or partition does not prevent other transactions from holding claims on the locked object.End of change

Notes

Releasing locks:
If LOCK TABLE is a static SQL statement, the RELEASE option of bind determines when DB2® releases a lock. For RELEASE(COMMIT), DB2 releases the lock at the next commit point. For RELEASE(DEALLOCATE), DB2 releases the lock when the plan is deallocated (the application ends).

If LOCK TABLE is a dynamic SQL statement, DB2 uses RELEASE(COMMIT) and releases the lock at the next commit point, unless the table or table space is referenced by cached dynamic statements. Caching allows DB2 to keep prepared statements in memory past commit points. In this case, DB2 holds the lock until deallocation or until the commit after the prepared statements are freed from memory. Under some conditions, if a lock is held past a commit point, DB2 demotes the lock state of a segmented table or a nonsegmented table space to an intent lock at the commit point.

Start of changeWhen you issue the MODIFY DDF PKGREL(BNDOPT) or MODIFY DDF PKGREL command, the use of processor resources for package allocation and deallocation are minimized for packages that use database access threads and are bound with RELEASE(DEALLOCATE). When the MODIFY DDF PKGREL(BNDOPT) command has been issued, DB2 honors the RELEASE bind option even for dynamic SQL statements.End of change

Syntax alternatives and synonyms:
For compatibility with previous releases of DB2, PART can be specified as a synonym for PARTITION.

Example

Obtain a lock on the sample table named DSN8A10.EMP, which resides in a partitioned table space. The lock obtained applies to every partition and prevents other application programs from either reading or updating the table.
   LOCK TABLE DSN8A10.EMP IN EXCLUSIVE MODE;