Results 1 to 11 of 11

Thread: deadlock

  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: deadlock

    DB2 v9.5 ESE on AIX v6.1
    we are getting deadlocks. here is output of deadlock monitor i set up:

    111) Deadlock statement history ...
    Deadlock ID : 0
    Participant No : 0
    Application id : 172.16.1.122.44765.100902195013
    Stmt history ID : 108
    Type : Dynamic
    Section No : 3
    Package cache id : 4308926464
    Package creator : NULLID
    Package name : SQLUFG13
    Package version :
    Lock timeout value : 120
    Nesting level of stmt : 0
    Invocation ID : 0
    Query ID : 0
    Source ID : 0
    UOW Sequence number : 0001
    Isolation level : Read Stability
    Stmt first use time : 09/02/2010 15:50:14.528693
    Stmt last use time : 09/02/2010 15:50:14.528693
    Statement text : INSERT INTO bb_table ("KEY_","LANGUAGE_","TEXT_") VALUES (CAST (? AS INT)
    ,CAST (? AS CHAR(1)) ,CAST (? AS VARCHAR(254))

    it is not one time thing.
    what do we do to fix the problem?
    will changing isolation level of the package/statement fix the problem?

    thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deadlocks are programming errors. You get a deadlock with this scenario.

    Application user A locks resource X
    Application user B locks resource Y
    Application user A locks resource Y
    Application user B tries to lock resource X <-- this causes deadlock detection.

    Tell the developers to access the resources in the same order--all of the time.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    A deadlock has two participants; you will need to determine what is on the other side of this deadlock and take an appropriate action. Like Andy said, deadlocks almost always indicate logical errors in the application(s) and it is not possible to resolve such errors on the database side.

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by ARWinner View Post
    Deadlocks are programming errors. You get a deadlock with this scenario.

    Application user A locks resource X
    Application user B locks resource Y
    Application user A locks resource Y
    Application user B tries to lock resource X <-- this causes deadlock detection.

    Tell the developers to access the resources in the same order--all of the time.

    Andy
    yes, i understand how deadlocks happen. this is gotta be at least 2 statements, right? but we can see only one in the report. how do I find those 2 statement which cause deadlocks? our applications are huge and I need to point out what particular statements/packages causing the problem.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by n_i View Post
    A deadlock has two participants; you will need to determine what is on the other side of this deadlock and take an appropriate action. Like Andy said, deadlocks almost always indicate logical errors in the application(s) and it is not possible to resolve such errors on the database side.
    does it mean that db2 monitoring tools will not show me 2nd statement in the deadlock and we need to search in the code for it?

    will snapshot for locks catch it?

    thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The deadlock event monitor shows both. Don't forget to flush the event monitor before formatting its data.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by n_i View Post
    The deadlock event monitor shows both. Don't forget to flush the event monitor before formatting its data.
    do you mean to set its state to 0?

    set event monitor my_deadlocks_mon state 0
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

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

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    db2 v9.5 does not have this statement.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by MarkhamDBA View Post
    db2 v9.5 does not have this statement.
    Yes it does:

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    In addition to what Andy and Nick mentioned, check for lock escalations in the db2diag.log - they can contribute to deadlocks. Deadlocks don't occur without lock waits, reducing locktimeout may reduce deadlocks but increase lock timouts. You can review access plans to make sure no table scans where indexes should be used. Reducing isolation level may help, if appropriate.

Posting Permissions

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