Data Anomalies
Isolation levels provide protection against the following data anomalies or conflicts that can occur when two processes concurrently access the data:
- Lost updates occur when two processes try to update an object at the same time. For example, Process A reads a record. Process B reads the same record, adds 10, and rewrites it. Process A adds 20 to the record that it previously read, and rewrites the record. Thus, Process B's update is lost.
- Dirty reads occur when one process modifies a record and a second process reads the record before the first is committed. If the first process terminates and is rolled back, the second process has read data that does not exist.
- Nonrepeatable reads occur when a process is unable to ensure repeatable reads. For example, this can occur if a transaction reads a record, another transaction updates it, then the first transaction rereads it, and gets a different value the second time.
- Phantom writes occur when a transaction selects a set of records based on selection criteria and another process writes a record that meets those criteria. The first process repeats the same selection and gets a different set of records.
Table 1 lists the data anomalies and the isolation levels at which they can occur.
| Anomaly | Level 0 | Level 1 | Level 2 | Level 3 | Level 4 |
|---|---|---|---|---|---|
| Lost update | No1 | No | No | No | No |
| Dirty read | Yes | Yes | No | No | No |
| Nonrepeatable read | Yes | Yes | Yes | No | No |
| Phantom write | Yes | Yes | Yes | Yes | No |
1Lost updates cannot occur if ISOMODE is set to 2 or 1. If ISOMODE is 0, it is possible for a process running at isolation level 0 to cause a lost update in your process.