2.19 Lock option

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Notes
(5) Specification example (lock option is specified in the SELECT statement)

(1) Function

The lock option provides exclusive controls for retrieving data.

The lock option is specified in the cursor declaration, the cursor specification in a FOR statement, the single-row SELECT statement, or the dynamic SELECT statement.

(2) Format

lock-option::=[{WITH {SHARE|EXCLUSIVE} LOCK
              |WITHOUT LOCK [{WAIT|NOWAIT}]}]
             [{WITH ROLLBACK|NO WAIT}]

(3) Operands

[{WITH {SHARE|EXCLUSIVE} LOCK|WITHOUT LOCK [{WAIT|NOWAIT}]}]
However, the default for this operand is WITH EXCLUSIVE LOCK if a FOR UPDATE clause is specified in the cursor declaration or in the dynamic SELECT statement, or the row using the cursor is updated or deleted.
WITH SHARE LOCK
Specifies that the contents of retrieved data may be referenced but may not be updated by other users (shared mode) until the current transaction has terminated.
WITH EXCLUSIVE LOCK
Specifies that the contents of retrieved data are not to be referenced (except for referencing by WITHOUT LOCK NOWAIT) or updated (exclusive mode) until the current transaction has terminated.
The WITH EXCLUSIVE LOCK option is effective only on the table specified in the FROM clause of a query specification.
Even when the WITH EXCLUSIVE LOCK option is specified, the shared-mode exclusive use option is applied to the key values of an index only when the index is referenced, and the exclusive use option is reset upon completion of the referencing operation. This permits other users to access the same index. However, if the index that is used for retrieval purposes is updated by another user, the user who needs to use the index for retrieval purposes may have to wait. The LOCK statement ensures that any processing of the index by another user must wait until the current transaction has been completed.
WITHOUT LOCK [WAIT]
Specifies that the contents of retrieved data are not to be subject to exclusive control until the current transaction terminates. If a search is performed by an SQL statement that has WITHOUT LOCK [WAIT] specified, locking of the referenced locked resource (row or page) is canceled.
Specifying WITHOUT LOCK [WAIT] causes HiRDB to reset the lock upon completion of the retrieval of a row without waiting for termination of the current transaction, thus reducing lock resource requirements. This technique minimizes the resources required to perform exclusive control and improves HiRDB's ability to process transactions concurrently. It should be noted, however, that the WITHOUT LOCK [WAIT] option can create a situation in which the same row retrieved twice during the same transaction provides different results.
WITHOUT LOCK NOWAIT
Specifies that data being updated by another user (or data subject to the exclusive lock option) can be referenced before the updating operation is completed and that the contents of retrieved data are not to be subject to exclusive lock until the current transaction has been completed. However, if the table to be searched is a shared table and if the LOCK statement is being executed in the lock mode by another user, the table is lock-release pending, unavailable for referencing.
In a retrieval performed by an SQL statement with WITHOUT LOCK NOWAIT specified, resources can be referenced in the same manner as without a lock (except that a logical file cannot be referenced), even when other transactions are applying the lock option to the tables and rows in the EX mode. However, tables that are being accessed by the pdload or pdorg command cannot be referenced.
Note that when WITHOUT LOCK NOWAIT is specified, the same data cannot be received again when the same row is retrieved twice in the same transaction. Moreover, if a table updating transaction is cancelled by one user, another user may receive data that did not exist when the table was retrieved while it was being updated.
If the WITHOUT LOCK NOWAIT option is specified in a cursor declaration or in a dynamic SELECT statement, a FOR UPDATE clause cannot be specified in the cursor declaration or dynamic SELECT statement; similarly, the WITHOUT LOCK NOWAIT option specified in the cursor declaration conflicts with the UPDATE or DELETE operation that uses the cursor.
[{WITH ROLLBACK|NO WAIT}]
If this operand is omitted and the data to be searched is being used by another user, the data goes into the wait state (exclusive of WITHOUT LOCK NOWAIT) until the pending user transaction terminates.
WITH ROLLBACK
Specifies if the data to be searched is being used by another user and if the pending transaction is to be cancelled and nullified (any SQL statements that were executed before the error occurred will be rolled back).
NO WAIT
Specifies when the data to be searched is being used by another user and the search is to be flagged as an error (lock error) without canceling the transaction (SQL statements that were executed before the error occurred will not be rolled back).
In this case, any lock applied by the specified SQL statement is not unlocked. Because the cursor remains open, it should be closed by executing the CLOSE statement in the UAP.
However, if a lock error occurs under any of the following circumstances, the transaction will be canceled and invalidated even when the NO WAIT option is specified:
  • With respect to a table that is subject to a retrieval by a subquery
  • A derived table that is the object of search by the FROM clause
  • With respect to a table that is subject to a retrieval by a query in which a value expression is specified in the GROUP BY clause
  • See 2.21 Inner derived tables for details about in-table derived tables that are subject to a retrieval by a query that satisfies one of the conditions that result in an "inner derived table."

(4) Notes

Because WITHOUT LOCK NOWAIT has a different meaning from WITHOUT LOCK NO WAIT, these options must be specified with care.

(5) Specification example (lock option is specified in the SELECT statement)

SELECT PCODE FROM STOCK
 WHERE PNAME = N'blouse'
 WITH SHARE LOCK
 WITH ROLLBACK