DB2 includes lock avoidance to reduce the overhead of always locking everything. DB2 checks to confirm that a lock is most likely necessary for data integrity before acquiring a lock. Lock avoidance is critical for performance. Application commits control its effectiveness. Lock avoidance is also used with isolation levels of cursor stability (CS) and repeatable read (RR) for referential integrity constraint checks. For a plan or package that is bound with RR, a page lock is required for the dependent page if a dependent row is found. This lock will be held to guarantee repeatability of the error on checks for updating primary keys when deleting is restricted.
You may need to bind your programs with CURRENTDATA(NO) and ISOLATION(CS) in DB2 V7 to allow for lock avoidance. In DB2 V8 and DB2 9, CURRENTDATA(YES) can also avoid locks. Although DB2 considers ambiguous cursors as read-only, it is always best to code your read-only cursors with the FOR FETCH ONLY or FOR READ ONLY clause.
Lock avoidance also needs frequent commits so that other processes do not have to acquire locks on updated pages. This practice also allows for page reorganization to occur to clear the possibly uncommitted (PUNC) bit flags in a page. Frequent commits allow the commit log sequence number (CLSN) on a page to be updated more often because it depends on the begin unit of recovery in the log, which is the oldest begin unit of recovery being required.
The best way to avoid taking locks in your read-only cursors is to read uncommitted. Use the WITH UR clause in your statements to avoid taking or waiting on locks; however, using WITH UR can result in the reading of uncommitted, or dirty, data that may eventually be rolled back. If you are using WITH UR in an application that will update the data, an optimistic locking strategy is your best performing option.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|