DB2 Version 9.7 for Linux, UNIX, and Windows

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