DB2 isolation level-Repeatable read

The DB2 isolation level described below is the Repeatable read level. As one of the important DB2 isolation levels, you can use the Repeatable read, it is impossible to lose updates and access unimplemented data and phantom rows.

Repeatable read

Repeatable read RR) locks all rows referenced by the application in the work unit. With "repeatable", an application in the same unit of work that opens the cursor issues a SELECT statement twice, and the same result is returned each time. With "repeatable reading", it is impossible to lose updates and access unimplemented data and phantom rows.

The "repeatable" application can retrieve and operate these rows as many times as possible before the work unit is completed. However, other applications cannot update, delete, or insert rows that may affect the result table before the work unit is completed. The "repeatable" application cannot view the unimplemented changes of other applications.

With "repeatable", each row referenced will be locked, not just those rows retrieved. An appropriate lock is executed, so other applications cannot insert or update the row. The row may be added to the list of rows referenced by the query, if the query is re-executed ). This prevents phantom rows. For example, if you scan 10000 rows and apply predicates to them, although only 10 rows meet the conditions, all 10000 rows will be locked.
Note: The "Repeatable read" isolation level ensures that all returned data remains unchanged until the application sees the data, even if a temporary table or row block is used.

Because "Repeatable read" may obtain and suspend a large number of locks, these locks may exceed the number of locks that can be used as the valid result of the locklist and maxlocks configuration parameters. To avoid lock upgrade, the Optimizer may choose to immediately obtain a single table-Level Lock for index scanning when it deems that a lock upgrade is likely to occur. This is just as if the database manager issued a lock table statement. If you do not want to get a table-Level Lock, make sure there are enough locks available for the transaction or use the read stability isolation level.

When referencing constraints are evaluated, DB2 upgrades the DB2 isolation level used for internal scanning of External tables to "Repeatable read" RR ), regardless of the DB2 isolation level set by the user. This will cause other locks to be suspended until they are implemented, increasing the possibility of deadlock or lock timeout. To avoid this, we recommend that you create an index that contains only one or more foreign keys, so that RI can scan this index.
 

Use of DB2 dynamic SQL

Implementation of DB2 circular Query

DB2 query Management Utility

Introduction to DB2 information directory Center

Learn about the DB2 data warehouse center