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 [authorization-identifier.]table-identifier
[,[authorization-identifier.]table-identifier]...
[IN {SHARE | EXCLUSIVE} MODE]
[UNTIL DISCONNECT]
[{WITH ROLLBACK | NO WAIT | NOWAIT} ]
Operands
- [authorization-identifier.]table-identifier
[,[authorization-identifier.]table-identifier]...
- 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 view table is specified in table-identifier, the base table underlying the view table is locked. 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.
- {with rollback | NO WAIT | NOWAIT}
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.
- NOWAIT
- The same as specifying NO WAIT.
Common rules
- If the base table that are subject to lock are duplicated, they are subject to duplicate elimination. The maximum number of base tables that can be locked by one LOCK statement is 64.
- Data dictionary tables are not eligible for lock control.
- 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.
The following figure illustrates increasing and decreasing the locking level and the length of the locking period.
Figure 5-1 Increasing and decreasing of locking period and level
![[Figure]](figure/zu5s0020.gif)
- 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.
- 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.
Notes
- Because lock control is usually performed by HiRDB, the LOCK statement should be issued only for purposes of changing the unit of lock control.
- When UNTIL DISCONNECT is specified, the duration of locking depends on the specification of PDXAMODE in the client environment definition. For details about PDXAMODE and locking control, see the HiRDB Version 9 UAP Development Guide.
- 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 9 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