Repeatable read

The Repeatable Read (RR) isolation level ensures:

  • Any row read during a unit of work is not changed by other activation groups that use different commitment definitions until the unit of work is complete.1
  • Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed.

In addition to any exclusive locks, an activation group running at level RR acquires at least share locks on all the rows it reads. Furthermore, the locking is performed so that the activation group is completely isolated from the effects of concurrent activation groups that use different commitment definitions.

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

Db2® for i supports repeatable read through COMMIT(*RR). Repeatable read isolation level is supported by locking the tables containing any rows that are read or updated.

1 For WITH HOLD cursors, these rules apply to when the rows were actually read. For read-only WITH HOLD cursors, the rows may have actually been read in a prior unit of work.