Lock avoidance

Concurrency is improved when applications are created so that Db2 can use lock avoidance techniques.

Begin general-use programming interface information. The CURRENTDATA(NO) option enables greater opportunity for avoiding locks. Db2 can check whether a row or page contains committed data. Db2 does not need to obtain any lock on the data when the row or page contains committed data. Unlocked data is returned to the application, and the data can be changed while the cursor is positioned on the row.

To take the best advantage of this method of avoiding locks, make sure all applications that access data concurrently issue COMMIT statements frequently.

The following figure shows how Db2 can avoid taking locks and the following table summarizes the factors that influence lock avoidance.

Figure 1. Best case of avoiding locks by using the ISOLATION(CS) and CURRENTDATA(NO) options. This figure shows access to the base table. If Db2 must take a lock, locks are released when Db2 moves to the next row or page, or when the application commits. This behavior matches the CURRENTDATA(YES) option.
Begin figure description. A timeline that diagrams the positions of interactions between and an application that uses CS isolation and the CURRENTDATA(NO) option. End figure description
Table 1. Lock avoidance factors. Returned data means data that satisfies the predicate. Rejected data is that which does not satisfy the predicate.
Isolation CURRENTDATA Cursor type Avoid locks on returned data? Avoid locks on rejected data?
UR N/A Read-only N/A N/A
CS YES Any No Yes1
NO Read-only Yes
Updatable No
Ambiguous Yes
RS N/A Any No Yes1, 2
RR N/A Any No No
Notes:
  1. Locks are avoided when the row is disqualified after stage 1 processing
  2. Under the ISOLATION(RS) option and multi-row fetch, Db2 releases locks on Stage 1 qualified rows that later fail to qualify for stage 2 predicates at the next fetch of the cursor. End general-use programming interface information.