ib****@yahoo.com wrote:
Session 1:
$db2 +c
db2 => set current isolation = UR
db2 => select * from t
T1
------
ABC
db2 => update t set t1 = 'XYZ'
(no commit or rollback)
Session 2:
$db2
db2 => set current isolation = UR
db2 => select * from t
T1
------
XYZ
Question:
Since I set both isolation levels to 'UR' and autocommit is OFF in
session 1, I expect to see the UNCHANGED data which is 'ABC' is session
2, why I get 'XYZ' instead?
Thanks!
First of all, isolation level has no effect on updates. It only affects
how long read (share) locks are held, except for UR which allows one to
see updated but not committed data.
Second, DB2 (unlike Oracle) only has one copy of the data. So if an
update changes the data there is no before image (even if not
committed). If a subsequent application does a select (with isolation
levels RR, RS, CS), then the select be locked out until the update is
committed. If the select is issued with UR, then the update lock is
ignored, and the dirty read sees the updated data.
However, there is a new registery variable available that will allow a
select to see through an uncommitted update for the sole purpose of
determining if the row qualifies for the select WHERE clause. If the
updated row does not qualify, then the read will not be blocked because
of the uncommitted update. But if the row does qualify in the select
WHERE clause, it will be blocked until the update committ happens.
