I have a question concerning the "Uncommitted Read" Isolation Level under DB2 DBMS for z/OS.
In this article
in the IBM Knowledge center it is stated that when using Isolation Levels other then Repeatable Read, the following phemomena can occur:
- Phantom Rows
- Dirty Read
- Non-repeateatable read
also the ANSI-SQL 92 standard only defines this three phenomena as possible and says that no updates can be lost:
(see page 68, chapter 4.28 "SQL-transactions").
"The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost."
So far I have not found a definitive answer on how DB2 (for z/OS) prevents lost updates when SQL statement in programs are coded with the "WITH UR" clause.
Lets assume the classic example for the "Lost Update" phenomenon:
- Transaction T1 reads the Value "X" from Table "R" into memory and increments it by 1.
- Transaction T2 reads the value "X" from Table "R" into memory and increments it by 2.
- Transaction T1 commits its changes
- Transaction T2 commits its changes - the value X+2 is stored in the database altough X+3 would be the correct result when T1 and T2 would have been serialized.
Lets assume that the transactions do not read the data by a "for update" cursor which internally leads to "with cursor stability" isolation level in DB2.
As of my understanding, to prevent this anomaly, Transaction T1 would need to set an exclusive lock on the qualifying row to prevent T2 to read the same value before T1 has not committed.
Despite that, as of the resources cited above, the Lost Update anomaly is not possible despite that - but how is this done?