Avoiding Locks

Page and row locking can be avoided at execution time by letting the system use lock-avoidance mechanisms. DB2 can test whether a row or a page has committed data on it. If it does, no lock is required on the data. Lock avoidance is valid only for read-only or ambiguous cursors and requires a combination of events and settings to occur. First, the statement must be a read-only or an ambiguous cursor, along with the proper isolation level and the appropriate CURRENTDATA setting. The best setting for maximum lock avoidance is using an isolation level of CS, either as a bind parameter or on the individual SQL statement, and CURRENTDATA set to NO as a bind option.

Lock avoidance is normally the default for most use, as it removes a lot of locking overhead. DB2 executes a small instruction set to determine whether an IRLM lock is truly needed for read-only cursors or ambiguous cursors.

Page and row locking may also be overridden at bind time or execution time. Locking can be overridden for read-only, not ambiguous, cursors by using the UR isolation level at bind time or on the individual statement level, as follows:

 EXEC SQL SELECT ... WITH UR 

An SQL SELECT statement is considered to be read-only when it uses any of the following:

• JOIN

• SELECT DISTINCT

• GROUP BY

• ORDER BY

• UNION

• UNION ALL

• SELECT of a column function

• FOR FETCH ONLY (FOR READ ONLY)

• SELECT FROM nonupdatable catalog table

If the SELECT statement does not incorporate any of the preceding, it is said to be ambiguous, meaning that DB2 does not know for sure whether modifying DML will be issued against the cursor. The CURRENTDATA setting of NO states to DB2 that the ambiguous cursor is read-only. If the statement is targeted by updating SQL, you will get an error. Table 16-5 shows when locking is avoided or overridden.

Table 16-5. Lock-Avoidance Determination

Isolation Level	CURRENTDATA	Cursor Type	Avoid Locks on Returned Data	Avoid Locks on Rejected Data
UR		Read-only
CS	Yes	Read-only	No	Yes
		Updatable
		Ambiguous
	No	Read-only	Yes
		Updatable	No
		Ambiguous	Yes
RS		Read-only	No	Yes
		Updatable
		Ambiguous
R		Read-only	No	No
		Updatable
		Ambiguous

NOTE

If your program is bound with ISOLATION(UR), mass deletes cannot run concurrently with your program.

In order to determine whether your application is getting lock avoidance, you can run a performance trace class 6, IFCID 214, to obtain information about whether lock avoidance was used for a particular page set in a unit of work. Field QW0218PC will have a Y or N indicating its use. IFCID 223 in trace class 7 includes more detailed information about the use of lock avoidance on a particular resource.

 DSNI031I - csect - LOCK ESCALATION HAS OCCURRED FOR RESOURCE NAME = name LOCK STATE = state PLAN NAME : PACKAGE NAME = id1 : id2 STATEMENT NUMBER = id3 CORRELATION-ID  = id4 CONNECTION-ID  = id5 LUW-ID  = id6 THREAD-INFO = id7 : id8 : id9 : id1