reading DB2 with isolation CS - why is the lock blocking?

[unkwb]

Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a
locking situation in DB2 I do not understand. It is actually a pretty
simple scenario to which I can drill it down.

Let say I have a table with one column. This table contains 2 rows:
select * from my_tab
test1
test2

Now I insert a value (with a 1st CLP): insert into my_tab values('x1')

When I try to read the table from a 2nd CLP with isolation level CS
(read committed) it hangs. It waits because of the lock of the row
insert:
select * from my_tab with CS -- hangs

I would expect - like in Oracle - that with CS isolation it would
simply give me the 2 rows (test1/2) above.
select * from my_tab with UR -- does not hang and already displays x1,
AS EXPECTED
select * from my my_tab where col < 'x' with CS -- hangs if I do not
have an index on col, does not hang if I have an index on col

I find this pretty strange for isolation CS: As a conclusion an
insert/update of a table could lock all other select * until it is
committed.

Do I miss something here?
Regards HW

[Matt Emmerton]

<un***@web.de> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a
locking situation in DB2 I do not understand. It is actually a pretty
simple scenario to which I can drill it down.

Let say I have a table with one column. This table contains 2 rows:
select * from my_tab
test1
test2

Now I insert a value (with a 1st CLP): insert into my_tab values('x1')

When I try to read the table from a 2nd CLP with isolation level CS
(read committed) it hangs. It waits because of the lock of the row
insert:
select * from my_tab with CS -- hangs

I would expect - like in Oracle - that with CS isolation it would
simply give me the 2 rows (test1/2) above.
select * from my_tab with UR -- does not hang and already displays x1,
AS EXPECTED
select * from my my_tab where col < 'x' with CS -- hangs if I do not
have an index on col, does not hang if I have an index on col

I find this pretty strange for isolation CS: As a conclusion an
insert/update of a table could lock all other select * until it is
committed.

Do I miss something here?

No, aside from the fact that DB2 is not Oracle, and things do not
neccessarily work exactly the same.

DB2 uses next-key locking to ensure that the result set remains constant.
(CS = cursor stability in DB2 parlance.) In the presence of indexes, this
can block other queries from completing until the original query commits.

--
Matt Emmerton

[Mark A]

<un***@web.de> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a
locking situation in DB2 I do not understand. It is actually a pretty
simple scenario to which I can drill it down.

Let say I have a table with one column. This table contains 2 rows:
select * from my_tab
test1
test2

Now I insert a value (with a 1st CLP): insert into my_tab values('x1')

When I try to read the table from a 2nd CLP with isolation level CS
(read committed) it hangs. It waits because of the lock of the row
insert:
select * from my_tab with CS -- hangs

I would expect - like in Oracle - that with CS isolation it would
simply give me the 2 rows (test1/2) above.
select * from my_tab with UR -- does not hang and already displays x1,
AS EXPECTED
select * from my my_tab where col < 'x' with CS -- hangs if I do not
have an index on col, does not hang if I have an index on col

I find this pretty strange for isolation CS: As a conclusion an
insert/update of a table could lock all other select * until it is
committed.

Do I miss something here?
Regards HW

In Oracle, each user is guaranteed read constancy of the data (albeit the
old data), if someone has else has updated it and not committed. Oracle must
temporarily create and store a separate copy of the data to do this, so it
is not "free" from a performance perspective.

DB2 has a significantly different locking architecture than Oracle, and does
not provide read consistency of updated data (ability to see the data before
it was changed for uncommitted updates).

However, there are some new DB2 environment (registry) variables that allow
DB2 to have some of the benefits of Oracle optimistic locking, without the
extra overhead:

You can use the DB2_SKIPINSERTED registry variable to skip uncommitted
inserted rows for Cursor Stability (CS) and Read Stability (RS) isolation
levels. Having this registry variable set ON produces greater concurrency
and would therefore be the preferred choice for most applications (and
emulates how Oracle would function).

The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used to
skip (not wait on the lock) uncommitted deletions and uncommitted updates.
In the case of DB2_EVALUNCOMMITTED, DB2 will skip updated rows that no
longer qualify the predicate criteria in the query. Otherwise, CS and RS
isolation levels require the processing of committed data only.

Registry variables are changed with the DB2SET command and are in effect at
the instance level.

The DB2_SKIPINSERTED was introduced in FP9 (8.2.2) and the others I believe
were included in FP4 (but I am not going to look it up and I provide no
guarantees about the fixpack level needed).

[unkwb]

Thanks a lot for these answers.
They contain some very helpful hints for me!

Best regards
HW

[Serge Rielau]

un***@web.de wrote:

Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a
locking situation in DB2 I do not understand. It is actually a pretty
simple scenario to which I can drill it down.

Let say I have a table with one column. This table contains 2 rows:
select * from my_tab
test1
test2

Now I insert a value (with a 1st CLP): insert into my_tab values('x1')

When I try to read the table from a 2nd CLP with isolation level CS
(read committed) it hangs.
It waits because of the lock of the row
insert:
select * from my_tab with CS -- hangs

I would expect - like in Oracle - that with CS isolation it would
simply give me the 2 rows (test1/2) above.

No, by default DB@ tries to catch that third row. So it has to wait.
For insert however there is no difference for correctness, so DB2
supplies the DB2_SKIP_INSERTED registry variables.
http://publib.boulder.ibm.com/infoce...n/c0012389.htm

There is another registry variable you can set to skip uncommited rows
which do not qualify base predicates. That one is semantically a bit
risky though.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab