Using SQL statements to override isolation levels
You can override the isolation level with which a plan or package is bound.
Procedure
Results
By using one of these options, you tell DB2® to acquire
and hold a specific mode of lock on all the qualified pages or rows.
The following table shows which mode of lock is held on rows or pages
when you specify the SELECT using the WITH RS or WITH RR isolation
clause.
Option Value | Lock Mode |
---|---|
USE AND KEEP EXCLUSIVE LOCKS | X |
USE AND KEEP UPDATE LOCKS | U |
USE AND KEEP SHARE LOCKS | S |
With read stability (RS) isolation, a row or page that is rejected during stage 2 processing might still have a lock held on it, even though it is not returned to the application.
With repeatable read (RR) isolation, DB2 acquires locks on all pages or rows that fall within the range of the selection expression.
All locks are held until the application commits. Although this option can reduce concurrency, it can prevent some types of deadlocks and can better serialize access to data.