LOCK statement (Lock control on tables)

Function

The LOCK statement performs lock control on specified tables. The LOCK statement can reduce the overhead that results from performing lock control in units of rows or key values when there are many rows on which HiRDB performs lock control automatically, when there are many rows from which the table is accessed for performing lock controls in units larger than the key-value unit, or when there are many key values.

Privileges

A user who has the SELECT privilege to a table can lock the table in the shared mode.

A user who has the INSERT, UPDATE, or DELETE privilege to a table can lock the table in the lock mode.

Format

LOCK TABLE [[RD-node-name.]authorization-identifier.]table-identifier
           [,[[RD-node-name.]authorization-identifier.]table-identifier]...
           [IN {SHARE | EXCLUSIVE} MODE]
           [UNTIL DISCONNECT]
           [{WITH ROLLBACK | NO WAIT} ]

Operands

RD-node-name
Specifies the name of the RD node to be accessed.
authorization-identifier
Specifies the authorization identifier of the owner of a table on which lock control is to be performed.
table-identifier
Specifies the name of the table that is to be subject to lock control.
If a foreign table is specified in table-identifier, a lock is performed on the table on the foreign DB associated with the foreign table.
If a view table is specified in table-identifier, a lock is performed on the base table that is a base for the view table, and the table on the foreign DB corresponding to the foreign table that is the source of the view table. In this case, a lock is not performed on the view table.
A maximum of 64 table names can be specified; the same table name can be specified more than once.

Specifies that the data in the specified tables to be subject to lock control can be referenced but not updated by other users (shared mode). During access to a table after a LOCK statement in which this operand is specified has been issued, the number of rows in the shared mode and the number of locked resources for key values are reduced.

Specifies that the data in the specified tables to be subject to lock control cannot be referenced or updated by other users (exclusive mode). The tables can still be retrieved by another user if the user specifies the WITHOUT LOCK NOWAIT lock option. During access to a table after a LOCK statement in which this operand is specified has been issued, the number of rows in the shared mode, the number of key values, the number of rows in the locked mode, and the number of locked resources for key values are reduced.

This option must be specified when table data is to be locked until it is processed by the DISCONNECT statement. The default is to lock the table data until the transaction terminates.

Specifies that when locking competes with another user, an error is to be received without waiting for resolution of the contention. If this operand is omitted and locking competes, HiRDB waits either until the lock is released or until amount of time specified in the system-defined pd_lck_wait_timeout operand has elapsed.

WITH ROLLBACK
If the table subject to lock is being used by another user, this operand is specified when canceling and nullifying the transaction.
NO WAIT
Specifies that when a table subject to this lock control is being used by another user, the current SQL statement is to be nullified but the transaction is not to be cancelled.

Common rules

  1. If the base table and the foreign table that are subject to lock are duplicated, they are subject to duplicate elimination. The maximum number of base tables and foreign tables that can be locked by one LOCK statement is 64.
  2. Data dictionary tables are not eligible for lock control.
  3. The action of a lock on foreign tables varies from one foreign DB to another. For details about locking, see D. Restrictions on Using a Foreign Table.
  4. Locking data or executing the LOCK statement causes the locking period to increase and the locking level to become higher. Therefore, even if the LOCK statement is executed with a specification to reduce the locking period or lower the locking level, the locking period or level will remain the same.
    Figure 5-1 illustrates the increasing and decreasing of the locking period and level.

    Figure 5-1 Increasing and decreasing of locking period and level

    [Figure]

  5. If a PURGE TABLE statement is executed after a LOCK statement has been issued, the lock level changes to EXCLUSIVE. However, if the transaction has terminated before the PURGE TABLE statement executes, the lock level does not change to EXCLUSIVE even though the PURGE TABLE statement executes.
  6. If a table locked by an UNTIL DISCONNECT specification is deleted by the DROP TABLE statement, the locking of the table is automatically reset by the HiRDB system.
  7. Tables on which the UNTIL DISCONNECT option is specified cannot be used in remote database access.
  8. A lock directed at a foreign table is performed on the table on the foreign server associated with the foreign table. If a lock fails on the table on the foreign server, the transaction that executed the LOCK statement is rolled back.

Notes

  1. Because lock control is usually performed by HiRDB, the LOCK statement should be issued only for purposes of changing the unit of lock control.
  2. When UNTIL DISCONNECT is specified, the duration of locking depends on the specification of PDXAMODE in the client environment definition. For details on PDXAMODE and locking control, see the HiRDB Version 8 UAP Development Guide.
  3. If the LOCK statement is executed on a shared table in the lock mode (in EXCLUSIVE MODE), the following RDAREAs are also subject to locking in addition to the shared table:
    • The RDAREA that stores the shared table
    • If an index is defined for the shared table, the RDAREA that stores the index
    For details about locking shared tables, see the HiRDB Version 8 Installation and Design Guide.

Example

Impose shared-mode lock control on a table named STOCK in order to retrieve all information from the table in a single transaction:

LOCK TABLE STOCK IN SHARE MODE