Evaluate uncommitted data through lock deferral

To improve concurrency, the database manager in some situations permits the deferral of row locks for CS or RS isolation scans until a row is known to satisfy the predicates of a query.

By default, when row-level locking is performed during a table or index scan, the database manager locks each scanned row whose commitment status is unknown before determining whether the row satisfies the predicates of the query.

To improve the concurrency of such scans, enable the DB2_EVALUNCOMMITTED registry variable so that predicate evaluation can occur on uncommitted data. A row that contains an uncommitted update might not satisfy the query, but if predicate evaluation is deferred until after the transaction completes, the row might indeed satisfy the query.

Uncommitted deleted rows are skipped during table scans, and the database manager skips deleted keys during index scans if the DB2_SKIPDELETED registry variable is enabled.

The DB2_EVALUNCOMMITTED registry variable setting applies at compile time for dynamic SQL or XQuery statements, and at bind time for static SQL or XQuery statements. This means that even if the registry variable is enabled at run time, the lock avoidance strategy is not deployed unless DB2_EVALUNCOMMITTED was enabled at bind time. If the registry variable is enabled at bind time but not enabled at run time, the lock avoidance strategy is still in effect. For static SQL or XQuery statements, if a package is rebound, the registry variable setting that is in effect at bind time is the setting that applies. An implicit rebind of static SQL or XQuery statements will use the current setting of the DB2_EVALUNCOMMITTED registry variable.

Applicability of evaluate uncommitted for different access plans

Table 1. RID Index Only Access
Predicates	Evaluate Uncommitted
None	No
SARGable	Yes

Table 2. Data Only Access (relational or deferred RID list)
Predicates	Evaluate Uncommitted
None	No
SARGable	Yes

Table 3. RID Index + Data Access
Predicates		Evaluate Uncommitted
Index	Data	Index access	Data access
None	None	No	No
None	SARGable	No	No
SARGable	None	Yes	No
SARGable	SARGable	Yes	No

Table 4. Block Index + Data Access
Predicates		Evaluate Uncommitted
Index	Data	Index access	Data access
None	None	No	No
None	SARGable	No	Yes
SARGable	None	Yes	No
SARGable	SARGable	Yes	Yes

Example

The following example provides a comparison between the default locking behavior and the evaluate uncommitted behavior. The table is the ORG table from the SAMPLE database.

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION     
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York     
      15 New England         50 Eastern    Boston       
      20 Mid Atlantic        10 Eastern    Washington   
      38 South Atlantic      30 Eastern    Atlanta      
      42 Great Lakes        100 Midwest    Chicago      
      51 Plains             140 Midwest    Dallas       
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver

The following transactions occur under the default cursor stability (CS) isolation level.

Table 5. Transactions against the ORG table under the CS isolation level
SESSION 1	SESSION 2
`connect to sample`	`connect to sample`
`+c update org set deptnumb=5 where manager=160`
	`select * from org where deptnumb >= 10`

The uncommitted UPDATE statement in Session 1 holds an exclusive lock on the first row in the table, preventing the query in Session 2 from returning a result set, even though the row being updated in Session 1 does not currently satisfy the query in Session 2. The CS isolation level specifies that any row that is accessed by a query must be locked while the cursor is positioned on that row. Session 2 cannot obtain a lock on the first row until Session 1 releases its lock.

Waiting for a lock in Session 2 can be avoided by using the evaluate uncommitted feature, which first evaluates the predicate and then locks the row. As such, the query in Session 2 would not attempt to lock the first row in the table, thereby increasing application concurrency. Note that this also means that predicate evaluation in Session 2 would occur with respect to the uncommitted value of deptnumb=5 in Session 1. The query in Session 2 would omit the first row in its result set, despite the fact that a rollback of the update in Session 1 would satisfy the query in Session 2.

If the order of operations were reversed, concurrency could still be improved with the evaluate uncommitted feature. Under default locking behavior, Session 2 would first acquire a row lock prohibiting the searched UPDATE in Session 1 from executing, even though the Session 1 UPDATE statement would not change the row that is locked by the Session 2 query. If the searched UPDATE in Session 1 first attempted to examine rows and then locked them only if they qualified, the Session 1 query would be non-blocking.

Restrictions

The DB2_EVALUNCOMMITTED registry variable must be enabled.
The isolation level must be CS or RS.
Row-level locking is in effect.
SARGable evaluation predicates exist.
Evaluate uncommitted is not applicable to scans on the system catalog tables.
For multidimensional clustering (MDC) or insert time clustering (ITC) tables, block-level locking can be deferred for an index scan; however, block-level locking cannot be deferred for table scans.
Lock deferral will not occur on a table that is executing an inplace table reorganization.
For Iscan-Fetch plans, row-level locking is not deferred to the data access; rather, the row is locked during index access before moving to the row in the table.
Deleted rows are unconditionally skipped during table scans, but deleted index keys are skipped only if the DB2_SKIPDELETED registry variable is enabled.