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
Predicates | Evaluate Uncommitted |
---|---|
None | No |
SARGable | Yes |
Predicates | Evaluate Uncommitted |
---|---|
None | No |
SARGable | Yes |
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 |
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
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
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.