Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Talking Unanswered: LOCK Escalation --Dead Locks--URGENT

    OS : AIX,Solaris
    UDB 8.2
    Porloblem lock escations are causing the deadlocks.


    Guys , I am seeing so many lock escations in the database. The diag log
    shous thousands of lock esacalation statements in the log.

    I have pasted an example of the lock escalation statement below..
    Out of these 1000s of statements few turned out to be deadlocks.

    I don't have any clue how to solve this problem. Could you guys will help me in finxing this problem?

    btw, the Lock list and maxlocks usage is pretty much under control.
    I believe these deadlocks are happening because fo too many lock escalations. Is there a way to fix this prblem





    2005-11-29-10.34.30.626343-300 E10266661C479 LEVEL: Error
    PID : 8161 TID : 1 PROC : db2agent (K065PPN) 0
    INSTANCE: instance1 NODE : 000 DB : DBname
    APPHDL : 0-1059 APPID: GA214279.P5F3.05F039152831
    FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4
    MESSAGE : ADM5503E The escalation of "102407" locks on table "schema.Tabname"
    to lock intent "S" has failed. The SQLCODE is "-911".

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by dba_udb
    btw, the Lock list and maxlocks usage is pretty much under control.
    I beg to differ...

    If you're having escalations it's because an application is taking up more than MAXLOCKS of your LOCKLIST, and DB2 decided it was time to escalate to a table lock.

    You either need to track down the statement that's causing the problem and rewrite it / tune it / etc so it doesn't require as many locks, or increase the LOCKLIST/MAXLOCKS parameters to accomidate it, if possible.

    What's the exact error you're seeing BTW, sql0911 rc=2 (deadlock) or rc=68 (timeout)?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2005
    Posts
    73
    Its rc=68.

    1010 COM.ibm.db2.jdbc.DB2Exception:
    [IBM][CLI Driver][DB2/SUN] SQL0911N The current transaction has been
    rolled back because of a deadlock or timeout. Reason code "68".
    SQLSTATE=40001

    at


    The transactions are timing out.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    OK, that's slightly different.

    Track down the statement and make sure:
    - The app commits as often as possible
    - The app has the lowest isolation level possible given your business requirements
    - The statements are well written and the access plans efficient
    - The app can't be run off-hours (ie. if it's a bulk delete or report)

    If you want the other apps to wait for it to complete, you can always increase LOCKTIMEOUT... but be really confident the long-running statement will complete in some reasonable amount of time, or that could just make the situation worse as all the other users line up waiting for locks.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    enlarge LOCKLIST size
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  6. #6
    Join Date
    Mar 2005
    Posts
    73
    I believe lock list memory usage is pretty much under control..

    Max storage for lock list (4KB) (LOCKLIST) = 2500
    Percent. of lock lists per application (MAXLOCKS) = 40
    Lock timeout (sec) (LOCKTIMEOUT) = 120



    Time database waited on locks (ms) = 91873265
    Lock list memory in use (Bytes) = 16840
    Deadlocks detected = 1063
    Lock escalations = 6911
    Exclusive lock escalations = 295
    Agents currently waiting on locks = 0
    Lock Timeouts = 204
    Number of indoubt transactions = 0

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    If you have the memory, you could go higher.

    But first make sure those locks are neccessary by tracking down the problem statement/application... Sorry to harp on it, but I don't think increasing values without understanding the root of the problem is a good idea, and in the end it may not even solve the issue.
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Mar 2005
    Posts
    73
    Petruck..
    I did not still understand why we have to increase the lock list.
    The lock list usage under control and max locks is 40 percent.

    So the locklist is not even 10 percent used.

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by dba_udb
    Petruck..
    I did not still understand why we have to increase the lock list.
    The lock list usage under control and max locks is 40 percent.

    So the locklist is not even 10 percent used.
    At the moment. That's not the high water mark, though, and the escalation suggests that the 40% is indeed being hit.
    --
    Jonathan Petruk
    DB2 Database Consultant

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If the usage of locklist is not more than 10%, why was there a lock escalation in the first place ?

    DB2 would have started acquiring row locks and the locklist would have gone upto 40% (1000 in your case). This will have caused the lock escalation to occur, ie , all row locks are released and a table lock is taken, bringing down the locklist usage to 10%

    The very purpose of lock escalation is to release the locklist space for other applications to use

  11. #11
    Join Date
    Mar 2005
    Posts
    73
    Ok..you mean to say at the time i have taken the snapshot the lock list usage is not showing the max.?

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes

    Cheers
    Sathyaram

Posting Permissions

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