Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    40

    Unanswered: load command causing lock escalation !

    hello friends ,

    I am getting the following warning message in my db2diag.log file
    The escalation of "91510" locks on table "GPSMAIN
    .PXBZONEQTR" to lock intent "X" was successful.

    i am trying to delete a huge chunk of data .Earlier a plain delete stmt ws there in the script , i have replaced it with load from /dev/null.
    Although after doing this the no of times the msg for lock escaltion in my diag file has reduced significantly but still i can see one instance of this msg ...
    Is it possible to remove it completely..

    One more thing I can see a few more lock escalation (intent S) for other tables is this related to this do i have to take care of each and every lock escalation ??

    Thanks
    Sandeep

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Lock escalations are not necessarily evil. When you are deleting mass amounts of rows, lock escalation is OK. It is only when they occur frequently that the added overhead of escalating the locks can cause system degradation. If you anticipate that there would probably be a lock escalation, like when you are deleting all those rows, it might be more prudent to go ahead and put a table lock before the deletion starts.

    If you are getting lots of lock escalations, you need to tune your database because your locklist is probably too small.

    What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Jun 2008
    Posts
    40
    hello Andy,

    db2 8.1.1 fix pack 16 and os is aix.
    yes imust say this after replacing delete with load /dev/null the no of lock escaltions is reduced .. and my lock list i currently 1800 and max lockx is 60

    Thanks and Regards
    Sandeep

Posting Permissions

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