LOCK TABLE

The LOCK TABLE statement either prevents concurrent application processes from changing a table or prevents concurrent application processes from using a table.

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 privileges held by the authorization ID of the statement must include at least one of the following:

• For the table identified in the statement,
  • The system authority of *OBJOPR on the table, and
  • The system authority *EXECUTE on the library containing the table
• Administrative authority

Syntax

>>-LOCK TABLE--table-name--IN----+-SHARE MODE----------------+-><
                                 +-EXCLUSIVE MODE ALLOW READ-+
                                 '-EXCLUSIVE MODE------------'
 

Description

table-name

Identifies the table to be locked. The table-name must identify a base table that exists at the current server, but must not identify a catalog table.

IN SHARE MODE

Acquires a shared lock (*SHRNUP) for the application process in which the statement is executed. Until the lock is released, it prevents concurrent application processes from executing any but read-only operations on the table. Other application processes may also acquire a shared lock (*SHRNUP) and prevent this application process from executing any but read-only operations.

IN EXCLUSIVE MODE ALLOW READ

Acquires an exclusive allow read lock (*EXCLRD) for the application process in which the statement is executed. Until the lock is released, it prevents concurrent application processes from executing any but read-only operations on the table. Other application processes may not acquire a shared lock (*SHRNUP) and cannot prevent this application process from executing updates, deletes, and inserts on the table.

IN EXCLUSIVE MODE

Acquires an exclusive lock (*EXCL) for the application process in which the statement is executed. Until the lock is released, it prevents concurrent application processes from executing any operations at all on the table.

The lock is acquired when the LOCK TABLE statement is executed.

The lock is released:

• When the unit of work ends, unless the unit of work is ended by a COMMIT HOLD or ROLLBACK HOLD
• When the first SQL program in the program stack ends, unless CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) was specified on the CRTSQLxxx command
• When the activation group ends
• When the connection is changed using a CONNECT (Type 1) statement
• When the connection associated with the lock is disconnected using the DISCONNECT statement
• When the connection is in the release-pending state and a successful COMMIT occurs

You may also issue the Deallocate Object (DLCOBJ) command to unlock the table.

Because the statement is synchronous, conflicting locks already held by other application processes will cause your application to wait up to the default wait time.

Example

Obtain a lock on the DEPARTMENT table. Do not allow others to either update or read from DEPARTMENT while it is locked.

   LOCK TABLE DEPARTMENT IN EXCLUSIVE MODE