Results 1 to 12 of 12

Thread: DB2 deadlock

  1. #1
    Join Date
    Oct 2013
    Posts
    39

    Unanswered: DB2 deadlock

    1 quick question, I have found the SQL statements which is creating the deadlock. But I can't kill those statements as both of them is running from a long time and i can't afford to kill them. So, What could be the possible solution to solve this problem.

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    check the other thread on deadlocks/timeouts
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if an application gets a deadlock, the agent gets sqlerror and the application should handle this
    do you continue or abort on this type of error
    how do you handle this error-condition ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If you don't mess a lock timeout with a deadlock and your applications are in a deadlock situation much more than DLCHKTIME ms (this is the database configuration parameter), then you should open PMR with IBM.
    DB2 has to resolve deadlocks automatically.
    Regards,
    Mark.

  5. #5
    Join Date
    Oct 2013
    Posts
    39
    Hi Mark,

    Thanks for the reply, So As per your answer I think we can't do anything in this kind of situations. Only best parameter(locktimeout, DLCHKTIME) settings is required to control deadlock. Please correct me I am wrong.

  6. #6
    Join Date
    Oct 2013
    Posts
    39
    Hi przytula_guy,

    I don't have any idea how to handle this kind of situation. It was just a thought came into my mind. So thought of asking with experts in this forum.
    What is your opinion on this. Do u think that DB2 should take care of this kind of scenarios and this kind of situations cannot come in DB2.

  7. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    How did you exactly find that it's a deadlock and not a lockwait?
    Did I get it correctly, that the following query:

    Select REQ_APPLICATION_HANDLE, HLD_APPLICATION_HANDLE
    From sysibmadm.mon_lockwaits

    Returns something like this for a long time:
    X, Y
    ...
    Y, X

    And you can't afford to kill any of these 2 important applications?
    Regards,
    Mark.

  8. #8
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Have you read this and followed the diagnosis instructions?

    http://www-01.ibm.com/support/knowle.../c0055071.html
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have to be more specific when asking question, as we can not see what is going on
    did you do some problem determination
    list applications - status of these problem agents
    if deadlocks occur - the application will be rejected and probably do rollback
    if lock wait : at least 1 appl will be in Lock wait
    try to find the lockholder - the statement/appl - try to see if commits can be included - if program logic can be adapted - if isol level or locking level can be adapted...
    many questions - not much input
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Oct 2013
    Posts
    39
    Hi All,

    Thanks for your inputs.

    Here the scenarios is not a real time scenarios, so i cant provide any inputs to give like list application or to give any agent id. or to check whether its lockwait or a deadlock. It was just a thought came into my mind and i asked here.
    Sorry for the confusion and thanks again for the input.. But my question is still open.. is this situation is possible or not which i have quoted? if yes(I think everything is possible so did the situation) then what can we do to resolve it?

  11. #11
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    deadlocks will be resolved by DB2. timeouts depend on whether you have the timeout parm set in the config ... as mentioned earlier. you can display locking issues using db2top and determine which one is holding the lock and inhibiting the other. either you kill one of the applications, you wait for db2 to kill one due to the lock timeout or if locktimeout is -1 you wait until the offender has completed. also, you can use db2top to see if lock escalation has occurred. you may see a table lock. read the stuff in the link i provided
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, deadlocks are not an issue that a DBA can resolve, they are an application programming error and must be resolved by the application teams.
    Dave

Posting Permissions

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