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

    Question Unanswered: how to check current database isolation level?

    Hi,

    I face some locking problem in production database. I delete all records in a table by issuing 4 different delete sqls in 4 sessions, so that each session delete a subset of the table. However, 2 of the 4 sessions are locktimeout.

    It is very likely caused by next key exclusive lock. However, I checked the registry parameter db2_rr_to_rs that it is already set to yes. I know this setting takes effect only after instant restart. I am not sure if we have restarted the instance after setting this parameter to yes, and it is impossible to restart the instance at the moment (as it is a 7 x 24 production database). How can I know if db2_rr_to_rs=yes is taking effect?

    Also, I tried getting snapshot for locks report but it doesn't show that an application is waiting for lock which is next key exclusive locked by other application(it doesn't show the hang session in lock-wait status), and the locking application shows only type X lock on deleted row as well as row next to the deleted row. How can I get more information on those locks?

    Are that any other case apart from next key exclusive lock which may have outcome similar to the problem I described here?

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    Can anyone help me? Thanks!

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Is this version 7 or 8? I believe that the next key locking problem (on indexes) was fixed with type 2 indexes in Version 8.

    Your title does not exactly match the text of your question. I don't believe that isolation level would come into play on delete statements. Isolation level determines how long Read locks are held, not Update locks.

    You might be experiencing lock escalation from row to table level. This is usually indicated in the db2dia.log file. To discourage lock escalation, increase the locklist parm (amount of memory for storing locks) by a factor of 10, and increase the maxlocks parameter to at least 50 (percent). Maxlocks determines the percent of locks that can be held by a single application before lock escalation takes place.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    It is V7 database. I have already set maxlocks to 100 and locklist to 16384. However, when I get snapshot for locks, I get the following error:

    Tue Sep 14 11:37:57 HKG 2004
    SQL0973N Not enough storage is available in the "QUERY_HEAP" heap to process
    the statement. SQLSTATE=57011

    I posted another thread to query on this.

    Do you have any idea? Thanks.

  5. #5
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    I suppose this is db2_rr_to_rs setting problem because when I use another database to simulate the case, I get fail result with db2_rr_to_rs=no, and I solve the problem when I set db2_rr_to_rs=yes.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If that is true, then there is probably some other SQL Select statement besides the deletes that is causing the lock contention.
    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
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    I checked db2diag.log and not escalation is found.

Posting Permissions

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