Read stability

Like level RR, level Read Stability (RS) ensures that:

Unlike RR, RS does not completely isolate the activation group from the effects of concurrent activation groups that use a different commitment definition. At level RS, activation groups that issue the same query more than once might see additional rows. These additional rows are called phantom rows.

For example, a phantom row can occur in the following situation:

  1. Activation group P1 reads the set of rows n that satisfy some search condition.
  2. Activation group P2 then INSERTs one or more rows that satisfy the search condition and COMMITs those INSERTs.
  3. P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows inserted by P2.

In addition to any exclusive locks, an activation group running at level RS acquires at least share locks on all the rows it reads.

In the SQL 2003 Core standard, Read Stability is called Repeatable Read.

DB2 UDB for iSeries supports read stability through COMMIT(*ALL) or COMMIT(*RS).