Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Unanswered: Lock Escalation Problem

    Hi,

    We have a setup where we are testing 3 concurrent users who are trying to delete some rows from the same table based on some different predicates, however after a few row level locks the same gets escalated to a table level lock in the 'X' exclusive mode and eventually one of the transactions rollsback.

    We have set the DB2_RR_TO_RS=Yes. However the same still persists.

    We have also set locktimeout to 300 seconds and maxlocks to 75 and lockist to approx 100 MB.

    Does any one have any clues to avoid the same.

    Regards

    nitin.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What version and fixpak are you running?

  3. #3
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278
    Hi Marcus,

    We are running Fix Pack 3.

    I have increased the locklist to 60000 however it still escates the rows

    Any pointers??

    nitin.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If that is version 8 then you are up to date. Version 7 has some issues with locking that are improved in version 8.

    Any chance that you have "allow reverse scans" on indexes? This can cause contention.

    I am not sure how these statements are executed, but you might try CS isolation level.

  5. #5
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Lock Escalation Problem.

    Hi ,

    How do we permanently alter the ISOLATION LEVEL for the database.

    how does the change isolation level command work, is it at the DBLEVEL or only for that cli connection??

    Regards

    Nitin

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I discussed that in your other thread:

    http://www.dbforums.com/t961387.html

    You can change the default isolation level for CLI, but it can be overriden by the other factors mentioned in my response.

  7. #7
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Problem Still Persists

    Hello Everyone

    I have now changed the Isolation Level to RS and increased the maxlocks to 77 and locklist to 1250 however I am still facing a lock escalation and table level locks when I have 3 concurrent users. Any other pointers to the parameters to be checked.

    Regards

    Nitin.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you sure it is lock escalation and not a timeout or deadlock (deadly embrace)? Increasing the locktimeout will not help a deadlock.

    Can you post table and index DDL and the 3 delete statements that are causing the problem?

  9. #9
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Problem Overcome and New Arisen

    Hi Marcus,

    I've managed to overcome the lock escalation problem by setting the Isolation to RS and increasing the Maxlocks to 77 and Locklist to 1252. However now it is giving an error which says that there is a deadlock or a timeout, however given the nature of our delete statements we know for sure that that will never encounter a deadlock.

    The current value for Locktimeout is -1 and Dlchcktimeout is 30000 ms.

    Any clues to what can be further changed.

    All your comments have been really helpful. Thanks a ton!!!!

    Regards

    Nitin.

Posting Permissions

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