|Under the "Locking and Deadlocks" section, under the "Applications |Slow or Appear to Hang" subsection, change the description under "Lock |waits or deadlocks are not caused by next key locking" to :
|Next key locking guarantees Repeatable Read (RR) isolation level |by automatically locking the next key for all INSERT and DELETE statements and |the next higher key value above the result set for SELECT statements. |For UPDATE statements that alter key parts of an index, the original index key |is deleted and the new key value is inserted. Next key locking is done |on both the key insertion and key deletion. It is required to guarantee |ANSI and SQL92 standard RR, and is the DB2 default.
|Examine snapshot information for the application. If the problem |appears to be with next key locking, you can set the DB2_RR_TO_RS option on if |none of your applications rely on Repeatable Read (RR) behavior and it is |acceptable for scans to skip over uncommitted deletes.
|When DB2_RR_TO_RS is on, RR behavior cannot be guaranteed for scans on user |tables because next key locking is not done during index key insertion and |deletion. Catalog tables are not affected by this option.
|The other change in behavior is that with DB2_RR_TO_RS on, scans will skip |over rows that have been deleted but not committed, even though the row may |have qualified for the scan.
|For example, consider the scenario where transaction A deletes the row with |column1=10 and transaction B does a scan where column1>8 and |column1<12.
|With DB2_RR_TO_RS off, transaction B will wait for transaction A to commit |or rollback. If it rolls back, the row with column1=10 will be included |in the result set of transaction B's query.
|With DB2_RR_TO_RS on, transaction B will not wait for transaction A to |commit or rollback. It will immediately receive query results that do |not include the deleted row.
|Do not use this option if you require ANSI and SQL92 standard RR or if you |do not want scans to skip uncommitted deletes.