By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

Isolation question for DB2 beginer

P: n/a
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!

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
Ian
ib****@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.


Nov 12 '05 #3

P: n/a
Thanks to all!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.