About DB2 Isolation Levels

DB2 provides support for three levels of multiple user concurrency control. These isolation levels select the extent to which database operations performed by one user are isolated from concurrent operations performed by other users.

"Repeatable Read" isolation level means that locks are held on all rows read or updated by a user for the duration of the current transaction (or logical unit of work). At this isolation level, if a row is read at some time and at a later time during the same transaction is re-read, the row still contains the same data values. That is, within a transaction, a read of a row is "repeatable" with identical results.

"Cursor Stability" isolation level means that within a transaction, locks are held only on the most recently read row or rows. Were a row that had been previously read within a transaction to be re-read, there is no guarantee that it returns the same column values as previously as another user could have updated the row in the meantime.

"Uncommited Read" isolation level means that within a transaction, rows that have been updated by one user can be read by another user and the reading user sees the updated values. The updating user may later decide to cancel the transaction, in which case the reading user would have retrieved (and possibly made decisions based on) data that is no longer stored in the database.

In general, the cursor stability isolation level is appropriate. However, in cases where updates are being performed on tables that are being related to themselves (reflexive relationships), the repeatable read isolation level should be selected, as it is highly probable that previously read rows will be re-read.