Question & Answer
Question
Is there a DB2 registry variable that can be used to revert back to the previouslocking behaviour if you haev an application or query that has a dependancy on locking as it existed prior to the DB2 version 9.5 product when moving to the DB2 version 9.5 or more recent product?
Cause
In the DB2 version 9.5 product and more recent versions, the DB2 product tries to reduce locking while maintaining isolation levels as defined. The aim is to pursue the best possible throughput in concurrency situations while adhering to the requested levels of isolation. Behavioral changes resulting from the refinement and evolution of the locking mechanism might not affect applications since locking and locks are an internal mechanism upon which applications and queries should not be made to rely upon. However, some behavioral changes might be noted as a result of these changes.
Answer
When a scan is blocking, there is no current row in the cursor. The scan might be correctly returned by a Cursor Stability (CS) scan without locking the row as long as the scan can determine that a qualifying row is committed. Prior to the DB2 Version 9.5 product, the lock was needed to determine if the row was committed. However, as of the DB2 version 9.5 product, other techniques can be employed to determine this. For some blocking CS scans in the DB2 Version 9.5 and later products, a row might be returned without locking if it can be determined that it is committed without getting the lock.
To revert back to the previous locking behavior, set the internal DB2 registry variable DB2_LOCKAVOIDANCE to OFF. With this setting, the CS scans will revert back to the old behavior of getting and releasing a lock for qualifying rows, even if it can now be determined that they are committed. The DB2_LOCKAVOIDANCE registry variable is not normally set, and defaults to ON. This avoid locks where possible.
The DB2_LOCKAVOIDANCE=OFF registry variable should only be used for temporary relief. Longer term application solutions should be sought out and implemented to alleviate any application dependancies upon internal locks and locking behaviors.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21382486