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!

ibm_97@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.

ibm_97@yahoo.com wrote:

>
> 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?

UR means Uncommitted Read. Meaning that you read changes that have not
yet been committed (in your example, 'XYZ').

DB2 does not utilize multi-version read consistency like Oracle.


FYI, UR has no meaning when performing an update. When a row is
updated, DB2 will always lock the row exclusively regardless of the
client's isolation level.

Thanks to all!