|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.