Comparison of isolation levels

The following table summarizes information about isolation levels.

  NC UR CS RS RR
Can the application see uncommitted changes made by other application processes? Yes Yes No2 No2 No
Can the application update uncommitted changes made by other application processes? No No No No No
Can the re-execution of a statement be affected by other application processes? See phenomenon P3 (phantom) below. Yes Yes Yes Yes No
Can “updated” rows be updated by other application processes? Yes No No No No
Can “updated” rows be read by other application processes that are running at an isolation level other than UR and NC? Yes No No No No
Can “updated” rows be read by other application processes that are running at the UR or NC isolation level? Yes Yes Yes Yes Yes
Can “accessed” rows be updated by other application processes?

For RS, “accessed rows” typically means rows selected. For RR, see the product-specific documentation. See phenomenon P2 (nonrepeatable read) below.

 Yes Yes Yes No No
Can “accessed” rows be read by other application processes? Yes Yes Yes Yes Yes
Can “current” row be updated or deleted by other application processes? See phenomenon P1 (dirty-read) below. See Note 1 See Note 1 See Note 1 No No
Note 1: This depends on whether the cursor that is positioned on the “current” row is updatable:
  • If the cursor is updatable, the current row cannot be updated or deleted by other application processes
  • If the cursor is not updatable,
    • For UR or NC, the current row can be updated or deleted by other application processes.
    • For CS, the current row may be updatable in some circumstances.

Note 2: The USE CURRENTLY COMMITTED clause should be used. In addition, when a query is implemented using a table scan, use the QAQQINI option CONCURRENT_ACCESS_BEHAVIOR with a value of *STRICTSCAN.
Examples of Phenomena:
P1
Dirty Read. Unit of work UW1 modifies a row. Unit of work UW2 reads that row before UW1 performs a COMMIT. UW1 then performs a ROLLBACK. UW2 has read a nonexistent row.
P2
Nonrepeatable Read. Unit of work UW1 reads a row. Unit of work UW2 modifies that row and performs a COMMIT. UW1 then re-reads the row and obtains the modified data value.
P3
Phantom. Unit of work UW1 reads the set of n rows that satisfies some search condition. Unit of work UW2 then INSERTs one or more rows that satisfies the search condition. UW1 then repeats the initial read with the same search condition and obtains the original rows plus the inserted rows.