Lock avoidance
Concurrency is improved when applications are created so that Db2 can use lock avoidance techniques.
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.
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:
- Locks are avoided when the row is disqualified after stage 1 processing
- 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.