Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    14

    Unanswered: Deadlocks on DB2 9.5

    Hi,
    Have a Db2 9.5 Fp2 on Win2003x64. I have a .net App that connects using the IBM .Net Provider for DB2 (part of Data Server Client).

    I get deadlock errors quite often. I want to know how I can debug the deadlock. Is there a way to find from logs the exact query that is causing a deadlock? Am a DB2 newbie, and request details on how to find the error. I couldnt find it on Control Center, and I couldnt find it with db2evmon.

    To improve performance, I use db2bulkcopy.WritetoServer. This works fine from a business point-of-view as each row has only one user.

    However, I am not sure how WriteToServer locks the table.
    TIA
    Kar

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Db2BulkCopy seems to do a COMPLETE table Level LOCK. If you are having multiple threads trying to work on the same table, then there is a great deal of chance of having DEADLOCKs.

    The easiest way to monitor, is to create an EVENT monitor for DEADLOCKs, run the applications and then Stop the event monitor. Check the DeadLock report generated.

    Thanks,
    Jayanta

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to make sure that you are actually getting deadlocks before you check the deadlock event monitor (which is created by default on a new database since 8.2).

    If the reason code (RC) = 2, it is a deadlock, if reason code = 68 it is a lock time out.
    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
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    As pointed out by Marcus, you need to check whether it is a deadlock or a lock timeout....

    Secondly you need to identify why there is a deadlock.... is it because of too many concurrent users or is it because of locks being held for too long, in which case you have to commit frequently
    IBM Certified Database Associate, DB2 9 for LUW

  5. #5
    Join Date
    Jun 2008
    Posts
    14
    Hi,
    Am a db2 newbie and request more help. My users get a message:
    "Server was unable to process request. Error 40001 IBM DB2 NT64 SQl0911N. The current transaction has been rolled back because of a Deadloct or Timeout. Reason Code 2. SQLState 40001".
    So it looks like a Deadlock to me.
    I also get alerts in Control Center that the number of deadlock per hour has breached its limit.
    I suppose I have a DB2DetailDeadlock event monitor, because it is creating evt files in the required folder. I cant see this monitor in Control Center however. How do I know what query is causing the deadlock? Is it available somewhere in control center? Or in the evt file? Or in a table?
    TIA
    Kar

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can print out the information that the deadlock event monitor is capturing by using DB2 supplied program which is described in the Command Reference manual (I don't recall the exact name, but you can look it up just as easily as me).
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That tool is called db2evmon.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2008
    Posts
    14
    Hi,
    I used db2evmon, but I still did not get the error details. Moreover, this command line tool just throws all the data into the Command Console, and that makes the data unavailable unless I pipe output to a text file (havent tried it yet).
    What I see in evmon output is that the SQL Statement is not available.
    I have just created my own Event Monitor, and have changed the Debugging Level to 4, and have also changed the Locktimeout to 30 s.
    Lets see if that helps.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to get the detail report to see the SQL (and you need the event montior set up to allow details).

    To help prevent deadlocks your LOCKLIST should be "automatic" (if you are using self-tunning memory manager) or try something like 4096.

    Another thing you can try is alter all your tables to "volatile".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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