Read stability at the DB2 isolation level

The read stability in DB2 is the knowledge that every DBA must master. It ensures that "non-repeated reading" is not possible and deserves further study.

Read stability RS) only lock the rows retrieved by the application in the work unit. It ensures that any qualified row reads during the execution of a work unit are not modified by other application processes before a work unit is completed, make sure that no rows changed by another application process are read until the process implements these changes. That is to say, there is no possibility of "non-repeated reading.

Different from repeatable reading, if your application sends the same query multiple times when "read stability" is used, you may see additional phantom row phantom reading ). When you rereference the example of scanning 10000 rows, "Read stability" only locks the specified row. In this way, when "read stability" is used, only 10 rows are retrieved, and only the 10 rows are suspended for locking. Compare it with "repeatable". In this example, the Repeatable read suspends the lock on all 10000 rows. The pending locks can be shared, the next share, update, or mutex lock.
Note: The "read stability" DB2 isolation level ensures that all returned data remains unchanged until the application sees the data, even if temporary tables or row blocks are used.

One of the goals of the "read stability" DB2 isolation level is to provide a high degree of parallelism and a stable view of data. To help achieve this goal, the optimizer ensures that the table-Level Lock is not obtained before the lock upgrade occurs.
"Read stability" DB2 isolation level is most suitable for applications that include all of the following features:

* Run in a concurrent Environment
* It is necessary to limit the stability of some rows during the unit of work.
* The same query is not sent multiple times in the work unit, or the same query is not required to obtain the same answer when multiple queries are sent in the same work unit.
 

Use of DB2 dynamic SQL

Implementation of DB2 circular Query

DB2 query Management Utility

Learn about the DB2 data warehouse center

How to query and create a table using DB2