Unlocked conditional search does not lock items whenever retrieval processing is underway, but instead locks only those rows and key values that satisfy the retrieval condition. Unlocked conditional search can reduce the retrieval time compared to ordinary retrieval processing, because rows and key values that do not satisfy the retrieval condition are not locked.

Moreover, when updating and retrieval are executed simultaneously, it is unnecessary to wait for lock-release if another user is updating or adding rows that do not satisfy the condition. Consequently, the incidence of deadlocks and of lock-release timeouts is reduced.

The following figure shows the processing flows of an ordinary search and of a search using unlocked conditional search.

Figure 3-11 Processing flows of an ordinary retrieval and of a retrieval using an unlocked conditional search

An unlocked conditional search is used by specifying YES in the PDLOCKSKIP operand in the client environment definition.

An unlocked conditional search is effective under the following conditions:

• When the number of items that satisfy the condition is small compared to the number of items to which the conditional search is to be applied.
  When a condition is selected for retrieval after the search range has been narrowed to some extent by means of an index key, only those items that satisfy the condition are locked. Consequently, if the number of items that satisfy the condition is small compared to the number of items within the range of the search, the number of lock processes is reduced (by number-of-items-that-satisfy-condition/number-of-items-in-search-range) compared to an ordinary retrieval.
  
• When retrieval does not use an index
  In the case of an ordinary retrieval that does not use an index, all rows are locked temporarily.
  If unlocked conditional search is used for a retrieval that does not use an index, only those items that satisfy the condition are locked, and therefore the number of lock processes is reduced (by number-of-items-that-satisfy-condition/total-number-of-rows-in-target-table).
  
• When retrieval is executed simultaneously with an updating process that does not satisfy the condition.
  Even if updating has already been performed within the retrieval range by another updating transaction, no lock-release waiting occurs if the updated results do not satisfy the condition.
  

Unlocked conditional search is not applied in the following cases even if it is specified:

• Retrieval that does not apply locking, such as WITHOUT LOCK NOWAIT (0 is specified in PDISLLVL in the client environment definition and lock option is omitted)
• Retrieval that uses an index when non-locking of index keys is applied
• Retrieval under any of the following conditions and without using an index:
  
  • A system-defined scalar function is specified
  • A user-defined function is specified
  • A VARCHAR-type column with a defined length of 256 bytes or more is specified
  • A MVARCHAR-type column with a defined length of 256 bytes or more is specified
  • A NVARCHAR-type column with a defined length of 128 characters or more is specified
  • A BINARY-type column with a defined length of 256 bytes or more is included
  • A BLOB-type column is specified
  • A repetition column is specified
  • A component specification is used
  • A subquery containing an external reference is specified
  • A qualified predicate or IN predicate is specified
  • A search spanning two or more tables, except a condition that is evaluated as an inner table for nest-loop-join
  • An index for use of AND multiple indexes (AND PLURAL INDEXES SCAN) is not used for evaluation
  • An index for use of OR multiple indexes (OR PLURAL INDEXES SCAN) is not used for evaluation

Because unlocked conditional search does not involve locking, uncommitted data might be used for the conditional search. For example, if a conditional search is performed simultaneously with an updating transaction, the result of the conditional search might differ from the result of the update transaction processing.