Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Unhappy Unanswered: next key share lock problem

    Hi,

    I am facing a serious locking problem and need to solve it urgently.

    In session 1 I run the following sql:
    INSERT INTO table_a (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) VALUES (4538947, 4476205, 20557, 4476224, 226, 1001498, 2650, 0, 2650, 0);

    execution plan shows tablescan on table_a
    shapshot for locks shows 1 row lock(w) and 1 table lock(ix) on table_a

    In session 1 I run the following sql:
    update table_a set col10=4539505 where col1=4538947 AND col10=0;

    execution plan shows indexscan (unique idex on col1) on table_a
    shapshot for locks shows 1 row lock(x) and 1 table lock(ix) on table_a

    Then in session 2 I run the following sql:
    INSERT INTO table_a (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) VALUES (4538971, 4476205, 20557, 4476224, 1210729, 1001498,
    33320, 0, 33320, 0);

    Session 2 hangs.
    shapshot for locks shows 1 row lock(x) and 1 table lock(ix) on table_a
    for session 1, and another row lock(x) and 1 table lock(ix) on table_a for session 2.

    After I commit or rollback session 1, session 2 resumes.
    Execution plan shows tablescan.
    Shapshot for locks shows 1 row lock(w) and 1 table lock(ix) on table_a for session 2.

    I think after session 1 issued an update statement, the updated row is locked exclusively. When session 2 inserts a new row, the optimizer choose to use tablescan. However, it cannot read the row locked by session 1.

    If the assumption is right, I have several questions:
    1. I have specified 'volatile' in creating table_a, and indexes exist for table_a, why the optimizer still use tablescan in insert statement?
    2. How can I solve this locking problem?

    Many thanks for any suggestion.

    ps: no effect by using 'with ur/cs' in the sqls.
    Last edited by jmychung; 09-15-04 at 05:55. Reason: for a better title

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Give information on the db2 version/OS ...
    Also the table and index definiton, including any related tables/indexes..
    If possible post the execution plan ..

    This will help the forum to give a better answer ..

    Cheers
    Sathyaram
    Last edited by sathyaram_s; 09-08-04 at 04:33.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Post

    OS: Win2K
    DB2: V7.2 FP7

    attached:
    1. actual sql, table and index definition,
    2. execution plan and
    3. snapshot report(captured when application 26 hangs, application 18 run first then application 26)

    Thanks a lot!
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The statement is not doing tablescan on any 'physical' table .. (The table scan is on the GENROW) .. All locks seem to be OK ...

    I think it is likely to be because of Next-Key Share locks (V7 Type 1 indexes) ... take an application snapshot to see why the session B is waiting (I thought lock snapshot shows this info, but doesn't seem to be there) at the tail end of the B's snapshot

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Post

    Thanks for your reply.

    I generate the application snapshot but the content are quite complicated to me. attached 28.txt(session A) and 32.txt(session B).

    When will the next-key share lock hold? after insert or update? I tried different sequence of those sqls and found that if session A issue inserts statements only, session B can also issue insert statements, but once session A issues update statement, session B cannot issue insert or update statement anymore. It seems that the problem is caused by update by session A, but not insert.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The lock wait is because of the Next Key Weak Exclusive lock confilicts ....

    Next Key locks is a property of Type 1 indexes where a lock is obtained on an adjecent row when inserting into an index ...

    The lock-wait is caused by the index

    CREATE INDEX "SM_SET "."EV_STK_ENT_IDX_2" ON "SM_SET "."EV_STOCKS_ENTITLE"
    ("TRADE_LEG_OID" ASC)
    PCTFREE 10 ALLOW REVERSE SCANS;

    Google for 'Next Key Weak Exclusive" for more information

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Exclamation

    Thanks for your info.

    Since other sqls need that index. Is it possible to avoid this problem but not dropping that index?

    also, when I test the same case but using AIX, the optimizer will use EV_STK_ENT_IDX_2 instead of pk_ev_stk_ent in update sql. That cause another performance problem. are there any method to force or direct the optimizer to use a particular index?

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You need not drop the index ... Find a balance between having the overhead of frequent commits and concurrency ... Have the update as nearer to the commit point as possible ...
    In almost all cases, I would allow the optimizer to do what it thinks best .... DB2 UDB optimizer is quiet sophisticated to pick up the best access path if you provide it with proper information on statistics, bufferpool size, num of concurrent applications etc. For complex SQLs or joins, if you see a problem with the access path, get in touch with IBM ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    Hi Sathyaram,

    Your info are really useful to me. Thanks a lot!

    However, I have 1 more question. There are several indexes on the table, but why only the existence of EV_STK_ENT_IDX_2 causes this problem?

    EV_STK_ENT_IDX_2 is a non-unique index while other indexes are all unique. Is it true that NW will not be acquired if there are only unique indexes associated with a table?

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Because that is the index with the updated column TRADE_LEG_OID

    Version 8 has introducted Type-2 indexes ... Tnerefore you will not have this problem

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    Thank you very much for your help.

    As frequent commit has big impact on performance, I really hope there are other ways to solve this problem.

    Again, thanks.

Posting Permissions

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