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.

Figure 3-11 shows the processing flows of an ordinary retrieval and of a retrieval 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 a lock (such as WITHOUT LOCK NOWAIT)
• Retrieval that uses the direct product of two tables or a quantified predicate as the retrieval condition
• Retrieval that uses an index when non-locking of index keys is applied

Because unlocked conditional search performs a conditional search without locking items, if it is executed simultaneously with an updating transaction, the result may not be the same as would have been the case with an ordinary retrieval.