Dears,

I made a test about isolation rr today. The result is correct for the RR concept. But after I checked the lock level in the testing, something seems wrong. Here is my test steps:

1) create table tmp(a int, b int)
2) execute sql A: "Select * from tmp where a=1 with rr" in one console with auto-commit off setting (that is db2 => update command options using c off).
3) execute sql B: "insert into tmp(a,b) values(1,2)" in another console, this transaction will be hang.

I checked the lock info from access plan as following:
The lock of sql A: Table Intent Share
Row: Share
Block: None
The lock of sql B: Table Intent Share
Row: Next Key Share
Block: None

The lock of A is compatible with B. For this meaning, sql B should not be blocked by transaction A. Does anybody know why? Is there any other lock I missed? My db2 version is 9.1.

The locks that are taken are compatible, but DB2 is hanging waiting to take an exclusive lock that is not compatible.

Thanks your reply,
So where can I get this exclusive lock info? Which operation(sql B?) is waiting to take the exclusive lock?

A little bit of background: step 3) (session B) must not succeed because the next query in session A would potentially turn up a "phantom" row, which was not present in the query run in step 2).

The INSERT itself is waiting for an exclusive lock.

Also, you should check the snapshot monitors to see which locks are currently taken.