Waiting for uncommited rows

[Mehmet Baserdem]

My select process with isolation level=CS on a table with locksize =
row waits until the other insert process commits.I don't lock the
table level and if I change the select process' isolation level to UR,
it doesn't wait.

I would like to have my processes with CS isolation level to be able to
read all "clean" rows without any wait. How can I make them skip dirty
rows?

Regards,

Mehmet Baserdem

[Shashi Mannepalli]

You can do this using DB2 REGISTRY VARIABLES like

DB2_EVALUNCOMMI TTED

Check the DB2 ADMIN GUIDE

or using Proper indexes

cheers....
Shashi Mannepalli


Mehmet Baserdem wrote:

My select process with isolation level=CS on a table with locksize =
row waits until the other insert process commits.I don't lock the
table level and if I change the select process' isolation level to UR,
it doesn't wait.

I would like to have my processes with CS isolation level to be able to
read all "clean" rows without any wait. How can I make them skip dirty
rows?

Regards,

Mehmet Baserdem

[Serge Rielau]

Mehmet Baserdem wrote:

My select process with isolation level=CS on a table with locksize =
row waits until the other insert process commits.I don't lock the
table level and if I change the select process' isolation level to UR,
it doesn't wait.

I would like to have my processes with CS isolation level to be able to
read all "clean" rows without any wait. How can I make them skip dirty
rows?

Regards,

Mehmet Baserdem

DB2_EVALUNCOMMI TTED
http://publib.boulder.ibm.com/infoce...e/c0011223.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Artur]

Probably you need DB2_SKIPINSERTE D.

Let's say, that there are 10 rows in table1.

UserA inserts 1 row without committing data.
UserB issues "select * from table1 with cs" and receives 10 rows. He is
not aware of the newly inserted row, until it's committed.
http://publib.boulder.ibm.com/infoce...n/c0012389.htm

-- Artur Wronski

[Mehmet Baserdem]

Thanks Artur, DB2_SKIPINSERTE D register resolved my problem.

DB2_EVALUNCOMMI TTED didn't work for me with the following
configuration:
DB2 Client ver 8.1.4 and DB2 server ver 8.2.2

I guess we better to use DB2_SKIPINSERTE D register for the db2 servers
of version v8.2.2.

regards

Mehmet Baserdem

[Artur]

Mehmet,

DB2_EVALUNCOMMI TTED just checks if the evaluated row fits the query
criteria.

Let's say there are 10 rows in table table1:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
....
UserA updates row #3 without committing data:
update table1 set col2='X' where col1 = 3

UserB issues:
select * from table1 where col2='A' with cs
and receives one row 1,'A'

Without the registry variable UserB have to wait (as there are no
indexes in table1 and DB2 is forced to do sequential scan).

But this variable can cause some inconsistencies .

If UserB issues:
select * from table1 where col2='C' with cs
will receive no rows, as current evaluated value is 'X'. But if userA
rollback the work the same query will return one row.

So, bottom line is: the variable can increase concurrency when users
are working on excluding data sets (in the same table).

-- Artur Wronski