The ISOLATION (RS) option

The ISOLATION (RS) or read stability option enables an application to read the same pages or rows more than once and prevents updates or deletes to qualifying rows by other processes. However, other applications can insert or update rows that did not satisfy the search condition of the original application.

Read stability isolation might result in greater concurrency than repeatable read. Other applications cannot change rows that are returned to the original application. However, they can insert new rows or update rows that did not satisfy the original search condition. Only rows or pages that satisfy the stage 1 predicate (and all rows or pages evaluated during stage 2 processing) are locked until the application commits. The following figure illustrates this process. In the example, the rows held by locks L2 and L4 satisfy the predicate.

Figure 1. How an application that uses RS isolation acquires locks when no lock avoidance techniques are used. Locks L2 and L4 are held until the application commits. The other locks aren't held.
Begin figure description. A diagram that shows interactions between an application and when no lock avoidance is used. This figure is described in the surrounding text. End figure description.

Applications that use read stability isolation can leave rows or pages locked for long periods, especially in a distributed environment.

If you do use read stability, plan for frequent commit points.

An installation option determines the mode of lock chosen for a cursor defined with the FOR UPDATE OF clause and bound with read stability.