Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005

    Question Unanswered: Isolation level RR Question


    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.

  2. #2
    Join Date
    May 2003
    The locks that are taken are compatible, but DB2 is hanging waiting to take an exclusive lock that is not compatible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2005
    Thanks your reply,
    So where can I get this exclusive lock info? Which operation(sql B?) is waiting to take the exclusive lock?

  4. #4
    Join Date
    Jan 2007
    Jena, Germany
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts