Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: Help on a lock-wait issue

    This table in our DB2 (DB2/AIX64 8.2.3) has a large amount of rows (>10 million). During a back-end program developing, I found a lock-wait situation that will potentially affect the performance of our application. Could somebody here help me find a solution to solve this issue? Thanks in advance!

    The issue is: after process A has locked one row by an update statement, process B isn't able to read some other rows by a select statement until process A commits.

    The simulation from 2 command line connections is like the following:

    connection A:
    >db2 +c "UPDATE TABLE_E SET COL_E = CURRENT TIMESTAMP WHERE COL_D = 'AN04' AND COL_C = 2"
    DB20000I The SQL command completed successfully.

    connection B:
    >db2 "SELECT * FROM TABLE_E WHERE COL_A='22222' AND COL_C = 4"

    hangs here!!!

    connection A:
    >db2 commit
    DB20000I The SQL command completed successfully.

    connection B:
    query result gets displayed immediately!!!

    Note:
    1) Uncommitted Read (UR) for process B is not an option in our application.
    2) The row that process A trying to update does have COL_A value '22222'. But apparently it wont be queried out by process B because of different values of COL_C;
    3) If I change process B the where clause COL_A a different value say '33333', the lock-wait situation is gone;
    4) The TABLE_E has 2 indexes: one is a clustering UNIQUE INDEX ON (COL_A,COL_B DESC), the other is a UNIQUE INDEX ON (COL_C,COL_D);
    5) The TABLE_E has PRIMARY KEY (COL_A,COL_B), there are other 2 tables REFERENCES TABLE_E.
    6) Snapshot on connection B shows:

    Application handle = 565
    Application status = Lock-wait
    ......
    ID of agent holding lock = 566
    ......
    Lock object type = Row
    Lock mode = Exclusive Lock (X)
    Lock mode requested = Next Key Share (NS)
    Name of tablespace holding lock = XXXXXXX
    Schema of table holding lock = XXXX
    Name of table holding lock = TABLE_E


  2. #2
    Join Date
    Sep 2003
    Posts
    237
    What about ReadStability for process B? Can you run
    db2set DB2_RR_TO_RS=YES
    and repeat your selects??
    mota

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    After I ran
    db2set DB2_RR_TO_RS=YES

    and tested the scenario again, the outcome was the same.

    I read somewhere that I needed to restart the db2 instance after I reset DB2_RR_TO_RS. If this is true, I'll discuss this with our DBA. But first, what's the downside of setting DB2_RR_TO_RS to be YES?

    Thanks.

  4. #4
    Join Date
    Sep 2003
    Posts
    237
    You are changing the isolation level from RepeatableRead to ReadStability at the server level. Most of the applications require the less restrictive RS.
    mota

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try also db2set DB2_EVALUNCOMMITTED=ON

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i
    Try also db2set DB2_EVALUNCOMMITTED=ON
    DB2_RR_TO_RS=YES has no effect in version 8 if you have type 2 indexes (the only indexes in V8 that are type 1 are those converted from version 7 and never reorged).

    n_i has the correct solution for V8.2+. DB2_EVALUNCOMMITTED=ON will allow the second query to evaluate the uncommited update and bypass the row if it does not match the predicate of the second query. If the predicate does match, then it will wait for the lock to be released as it did before. There are some unusual applications where using this registery value could be a problem, but not many.

    The DB2_SKIPINSERTED registery value should also be used and should be the default for virtually every application (unless the application does deletes and inserts instead of an update).

    DB2_SKIPDELETED can also be used to improve concurrency.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2005
    Posts
    7
    Thank you all!
    I'll discuss with our DBA about the upside/downside of resetting some of the registery values mentioned here. I'll run the tests again and keep you posted.

  8. #8
    Join Date
    Nov 2005
    Posts
    7
    Our DBA says that DB2_EVALUNCOMMITTED is originally ON in all our db2 instances. He turned on the other 3 mentioned here, restarted the instance, and I did the test again. The lock-wait situation remains.

    This is the result of command 'db2set' on our test database where we run all the tests.

    > db2set
    DB2_SKIPINSERTED=ON
    DB2_EVALUNCOMMITTED=ON
    DB2ROUTINE_DEBUG=ON
    DB2_SKIPDELETED=ON
    DB2BIDI=no
    DB2_RR_TO_RS=YES
    DB2COMM=tcpip


    I am thinking of adding a new index on (COL_A, COL_C). This would avoid process B doing a table scan and process B would not run into row lock!???

    What do you think? Any other suggestions? Thanks!

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have a small table, then DB2 may not use any of the indexes anyway because it is faster to do a table scan. Table scans can definitely lead to lock contention (as you suggested).

    The solution is to alter the tables to volatile, which will tell DB2 to use indexes even on small tables, which will usually minimize lock contention. If volatile does not help, then you might try to add the new indexes.
    Last edited by Marcus_A; 11-18-05 at 10:02.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Apr 2004
    Posts
    54
    Try change isolation level for session B with such way:
    SELECT * FROM TABLE_E WHERE COL_A='22222' AND COL_C = 4 WITH UR

  11. #11
    Join Date
    Nov 2005
    Posts
    7
    Thanks gardenman,
    Although our application doesn't allow Uncommitted Read (UR) originally, I'll review the design and think of the possibility again for some queries.

    Quote Originally Posted by Marcus_A
    If you have a small table, then DB2 may not use any of the indexes anyway
    I did created an identical table with all identical indexes and inserted less than 10 rows (the table where my original tests based on has more than 10 million rows.) Identical test on the small table doesn't give me the lock-wait issue. Can somebody explain this to me please? Thanks!

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by wwxu
    Can somebody explain this to me please?
    I think EXPLAIN will explain it better than anybody else because it really depends on your system configuration.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by wwxu
    I did created an identical table with all identical indexes and inserted less than 10 rows (the table where my original tests based on has more than 10 million rows.) Identical test on the small table doesn't give me the lock-wait issue. Can somebody explain this to me please? Thanks!
    If you never ran runstats on the table, then DB2 would use the default stats which would likely use an index. Alter the table to volatile to ensure that it always uses default stats even when you do a runstats.

    Please report back your results of the alter table set volatile.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Nov 2005
    Posts
    7
    I am asking our DBA to alter the table set volatile for a test. Mean while, below is the EXPLAIN on locks. "db2pd -locks" is the command that I can come up with.
    There are existing applications using these two existing indexes: the clustering UNIQUE INDEX ON (COL_A,COL_B DESC), and the UNIQUE INDEX ON (COL_C,COL_D). These two indexes will be there forever. This question may be silly. Did process B (where clause on COL_A and COL_C) use these two indexes? If the answer is YES, may I say "table scan (i.e. the test on the small size table) didn't cause the lock-wait, index scan (i.e. the original test) did cause the lock-wait"? Hope I am wrong.

    After process A did the update
    > db2pd -db XXXXXXX -locks
    Locks:
    Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
    0x07800000202F9340 18 0039000403DB311B0000000052 Row ..X G 18 1 0 0x0000 0x40000000
    0x07800000202F2080 18 53514C4445464C540763DD2841 Internal P ..S G 18 1 0 0x0000 0x40000000
    0x07800000202FB200 18 53514C4332453036C8324ABC41 Internal P ..S G 18 1 0 0x0000 0x40000000
    0x07800000202F4DC0 18 00390004000000000000000054 Table .IX G 18 1 0 0x0000 0x40000000

    After process B hung on the select
    > db2pd -db XXXXXXX -locks
    Locks:
    Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
    0x07800000202F81C0 20 00000003000000010001550056 Internal V ..S G 20 1 0 0x0000 0x40000000
    0x07800000202F9340 18 0039000403DB311B0000000052 Row ..X G 18 1 0 0x0000 0x40000000
    0x07800000202FAA40 20 0039000403DB311B0000000052 Row .NS W 18 1 0 0x0000 0x00000008
    0x07800000202F2080 18 53514C4445464C540763DD2841 Internal P ..S G 18 1 0 0x0000 0x40000000
    0x07800000202F7480 20 53514C4445464C540763DD2841 Internal P ..S G 20 1 0 0x0000 0x40000000
    0x07800000202FB200 18 53514C4332453036C8324ABC41 Internal P ..S G 18 1 0 0x0000 0x40000000
    0x07800000202F3080 20 53514C4332453036C8324ABC41 Internal P ..S G 20 1 0 0x0000 0x40000000
    0x07800000202F4DC0 18 00390004000000000000000054 Table .IX G 18 1 0 0x0000 0x40000000
    0x07800000202FA4C0 20 00390004000000000000000054 Table .IS G 20 1 0 0x0000 0x00000008

    After process A did the commit, all locks were gone.
    Last edited by wwxu; 11-21-05 at 12:04.

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This part shows that process B attempts to read the row locked by process A:
    Code:
    Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
    0x07800000202F9340 18 0039000403DB311B0000000052 Row ..X G 18 1 0 0x0000 0x40000000
    0x07800000202FAA40 20 0039000403DB311B0000000052 Row .NS W 18 1 0 0x0000 0x00000008
    As to why is this happening you'll need to come up with some other command to explain the execution plans, something like
    Code:
    db2expln -t -q "<your query here>"

Posting Permissions

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