Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Deadlock Monitor

    We are on AIX 4.33 UDB 7.2 Fixpack 10.

    We are having deadlocks issues in our applications. I created a Deadlock Event Monitor in the enviroment and turned it on.

    However the Deadlock Monitor report shows no Deadlocks. Its not capturing them.

    The error I get on the app screen is
    "SQL0911N The current transaction has been rolled back or timed out. Reason Code =68."

    Reason Code 68 is for timeouts as per the manual. Is that the reason why the Event monitor is not capturing it ??

    thanks

    Manish

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Deadlock Monitor

    Originally posted by mgupta
    We are on AIX 4.33 UDB 7.2 Fixpack 10.

    We are having deadlocks issues in our applications. I created a Deadlock Event Monitor in the enviroment and turned it on.

    However the Deadlock Monitor report shows no Deadlocks. Its not capturing them.

    The error I get on the app screen is
    "SQL0911N The current transaction has been rolled back or timed out. Reason Code =68."

    Reason Code 68 is for timeouts as per the manual. Is that the reason why the Event monitor is not capturing it ??
    It might surprise you but lock timeouts and deadlocks are different animals. You are absolutely correct noticing that the "reason code 68 is for timeouts as per the manual". This is exactly the reason why the deadlock event monitor doesn't capture these events.

    Hope this helps.

    Nick
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As noted, a deadlock (or deadly embrace) is not the same as a timeout, even though they both abend due to lock contention.

    It seems that you have a timeout that is caused by a wait on a locked resource that has exceeded the time allowed per your lockwait time specified for the DB2 system. This could be caused by lock escalation, where DB2 has escalated an application from Row to Table level locks based on the amount of lock activity. Lock escalation can often be curtailed with the maxlocks and locksize parameters.

    Lock contention could also be caused by applications using RR isolation level where they keep a lot of row locks until commit time. You might see if CS isolation level is acceptable for such applications, and use it to improve concurrency.
    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
    Posts
    93
    I surely have a time out issue.

    The problem is that I am not able to determine that the time out problem is being caused what other thread. The lock list utilization is less than 10 percent.

    If my thread is timing out, some other thrad must have the lock. How to capture that thread info ???

    thanks

  5. #5
    Join Date
    Dec 2002
    Posts
    134
    What is the current value of the lock timeout?
    If it is large enough, you should be able to capture locked applications, tables, statements that are part of the "lock timeout" using regular snapshot mechanism (get snapshot for applications, get snapshot for lock for application).
    Do not forget to turn statement and lock monitors on.

    -dmitri

  6. #6
    Join Date
    Oct 2003
    Posts
    93
    Ok this is the simplest solution and it worked.
    I set the Locktimeout to -1. (so basically disabled it).
    Set the DLCHKTIME = 20000 ms (=20sec which was my Locktimeout before).

    So now the application will have the rollback in case of lock problem with Reason code 2 instead of 68.

    AND the event monitor catches it nicely showing the contention threads easily...

    By the way, this whole thing use to be easily visible in the db2 log in the mainframe without the need to set any thing.. it would be nice to see the deadlocks in the log file on UDB on AIX too..

    thanks every one..

  7. #7
    Join Date
    Dec 2002
    Posts
    134
    Originally posted by mgupta
    By the way, this whole thing use to be easily visible in the db2 log in the mainframe without the need to set any thing.. it would be nice to see the deadlocks in the log file on UDB on AIX too..
    I think you can see them if you rise diaglevel to 4

  8. #8
    Join Date
    Oct 2003
    Posts
    93
    Your are right about diaglevel 4. I tried that and it does show the deadlock. However the inofrmation provided is very limited and very cryptic. Thanks for the suggestion...

Posting Permissions

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