Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    102

    Unanswered: Deadlock problem

    Hi All,

    DB2 UDB v8.2 Fixpack 7, OS- AIX

    I am getting this error :

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

    Also I took a snaphot of the databases which does show 2 deadlocks.

    What is best way to get rid of the deadlock? Can I force those applications which are holding the locks from the database by using "force application (application handle)"

    Thanks

    Anks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Deadlocks are automatically resolved by DB2 when it cancels one of the participants in the deadlock in order to let the other one proceed. By default, DB2 checks for deadlocks every 10 seconds.

    A locktimeout error (-911 reason code 68) can occur when the locktimeout period has occurred for an application waiting on a lock (but there is no deadly embrace). One thing you should do is to change the default locktimeout value from -1 (infinite wait time) to something like 30 seconds.
    db2 connect to <db-name>
    db2 update db cfg using locktimeout 30

    To analyze future deadlocks, you should create a deadlock event monitior. See create event monitor in the Command Reference manual. If you first created the database under version 8.2, then a deadlock event monitor may already be created by default and you can retrieve reports on what happened.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2005
    Posts
    102
    Thanks Marcus,

    The locktimeout is already set to 300 seconds, which I feel is little on the higher side.

    The Default deadlock does exits and I do see the .evt and .ctl under the folder db2detaildeadlock. Another strange thing is that the db2diag.log does not show any traces of deadlock. How much time does DB2 takes in resolving the deadlock? Because I saw deadlocks bumping from 1 to 3 in just half an hour.

    In a deadlock situation, can trying to increasing the MAXLOCKS help in getting rid of the deadlock, if DB2 doesn't get rid of the deadlock on its own.


    db2 get db cfg | grep LOCK

    Max storage for lock list (4KB) (LOCKLIST) = 8196
    Percent. of lock lists per application (MAXLOCKS) = 30
    Lock timeout (sec) (LOCKTIMEOUT) = 300

    I know MAXLOCKS is on a low side and I do see lot of lock escalations just before the deadlock.

    Thanks
    Anks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 starts the deadlock detector every 10 seconds by default (configurable in the db cfg) and if a deadlock exists it immediately cancels one of the participants. A deadlock is different than a lock wait because DB2 can determine if a real deadlock exists (in other words, it will never be resolved by itself no matter how much time elapses).

    Increasing the LOCKLIST and the MAXLOCKS (the percent of the LOCKLIST that can be used by one application before escalation takes place) will help prevent lock escalation from page to table. When the locklist is full, lock escalation takes place. But your values seem reasonable to me.

    The problem can probably be traced back to applications that are not commiting frequently enough, or running certain utilities when others are trying to access the table.

    If you produce the report for the deadlocks that occured, you should be able get some information on what happened.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2005
    Posts
    102
    Thanks again.

    I do have couple of application, database and lock snapshots taken at the time I got the above mentioned eror and a db2event.000 file. I suppose I can format this file using:

    db2evmon -d <database_name> <the event monitor file along with the path>

    Hopefully this would give me some information on the deadlock. I have attached a snapshot of the application which was holding the lock.

    Thanks
    Anks
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2007
    Posts
    8

    Smile Deadlock problem

    Hi all

    I know this is a later reply but here is an obscure solution

    IBM Resolving SQL0911N Reason Code = 68 - United States

    I don't know why this work it just did

    If anyone can shed some light on this it would be appreciated

Posting Permissions

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