LOCK TABLE statement

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

Invocation for LOCK TABLE

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

Authorization for LOCK TABLE

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
DATAACCESS authority

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 for LOCK TABLE

Description for LOCK TABLE

table-name

Identifies the table to be locked. The name must identify a table that exists at the current server. Start of change

It 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.

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.

If you omit the PARTITION clause for a table in a partitioned table space, all partitions are locked.

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.

Exceptions: Start of change

Unless the lock is on a LOB table space, a partitioned (non-UTS) table space, or a universal table space (UTS), concurrent processes that are running with an isolation level of uncommitted read (UR) can execute read-only operations on the table. Concurrent processes that are running with an isolation level of cursor stability (CS) with CURRENTDATA(NO) can execute read-only operations on the table when lock avoidance is used to read data that is already committed. End of change

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

Notes for LOCK TABLE

Locks obtained for LOCK TABLE statements

The locks that are acquired when you issue a LOCK TABLE statement depend on the mode that is requested and the type of table space.

For most table space types, the LOCK TABLE statement obtains locks on the table space or a specific partition. For segmented (non-UTS) table spaces, the LOCK TABLE statement applies to individual tables.

The following table shows the modes of locks acquired in segmented and non-segmented table spaces for LOCK TABLE statements.

Table 1. Modes of locks acquired by LOCK TABLE. LOCK TABLE on partitions behave the same as nonsegmented table spaces.
LOCK TABLE In	UTS or Non-Segmented Table Space	Segmented Table Space	Table in Segmented Table Space
EXCLUSIVE MODE	X	X	IX
SHARE MODE	S or SIX¹	S or SIX¹	IS

Note:

The SIX lock is acquired if the process already holds an IX lock. SHARE MODE has no effect if the process already has a lock of mode SIX, U, or X.

Release of locks obtained for LOCK TABLE statements

For LOCK TABLE statements, the RELEASE bind option in effect at the end of a unit of work controls when Db2 releases the locks. The RELEASE option in effect when the LOCK TABLE statement executes might change if the application issues subsequent statements under a different RELEASE option. For more information, see RELEASE bind option.

With the RELEASE(DEALLOCATE) option in effect, Db2 releases the lock when the plan is deallocated when the application ends)

With the RELEASE(COMMIT) option in effect, Db2 releases the lock at the next end of the unit of work (COMMIT or ROLLBACK), unless the table or table space is referenced by held cursors. A held cursor allows Db2 to keep prepared statements in memory past the end of the unit of work. In this case, Db2 holds the lock until after the prepared statements are freed from memory.

If a lock is held past the end of a unit of work for point for a universal table space (UTS), a new user sign-on causes Db2 to release the locks that it inherits from LOCK TABLE statements that were issued by the previous threads.

If a lock is held past the end of unit of work for a table in a non-UTS table space, it is demoted to an intent lock at the end of the unit of work.

Syntax alternatives and synonyms

For compatibility with previous releases of Db2, PART can be specified as a synonym for PARTITION.

Example for LOCK TABLE

Obtain a lock on the sample table named DSN8C10.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. However, under one of the conditions that are noted above under Exceptions, Db2 might allow read-only operations on the table.

LOCK TABLE DSN8C10.EMP IN EXCLUSIVE MODE;