Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Question from Deadlock Chain

    Env: DB2 8.2/AIX 5.3

    Lock snapshot shows the following information but I am not sure what would have caused this deadlock. Any thought on this?

    HOLD_APP HOLDER_ID HOLDER WAIT_APP WAITING_ID WAITER HOLD_MODE OBJ_TYPE TAB_NAME SCHEMA WAIT_TIME CUR_TIME
    ---------- ----------- ---------- ---------- ----------- ---------- --------- -------- --------------- ---------- ----------- --------
    db2jcc_app 9285 APPOWNER db2jcc_app 9283 APPOWNER X Row TAB1 ETL 0 11:23:02
    db2jcc_app 9283 APPOWNER db2jcc_app 9285 APPOWNER NKS Row TAB2 ETL 0 11:23:02

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to look EVERYWHERE in the app where Tab1 and Tab2 are both modified. They should be modified in the same order (e.g. Tab1, then Tab2). Somewhere in the application the order is backwards. This is what needs fixing.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You need to look at the deadlock event monitor output - the monitor should be running by default, unless you dropped or disabled it. The name is DB2DETAILDEADLOCK.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    DB2DETAILEDDEADLOCK has been disabled so that is not helping me here.

    By the way, how are you saying that it should be modified in same order to avoid this deadlock ? I would appreciate if you can provide more info on this.

    In my understanding of Regarding Next Key Share lock this is obtained for rows on the same index page if CS or RS isolation level is used. For example if a table has 500 rows (50 rows per page). If a rows in updated with X lock rest of 49 rows on the same page will have NKS lock. Is this a correct understanding ? Would you pls correct me if I'm wrong.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are using V8 which is long out of service, and I do not remember how the locking works. But it really does not matter. What is happening is this:

    Application A is locking a resource on Tab1
    Application B is locking a resource on Tab2
    Application A wants to lock the same resource on Tab2 but cannot so it goes to lock wait
    Application B want to lock the resource on Tab1 <<-- DB2 detects a deadlock with this.

    It does not matter what the lock types are specifically. You need to determine what statements are causing the deadlock. The previously mentioned event monitor will provide that. Then the application need to be changed so that the order is changed. In the example, change Application B to try to access Tab1 first then Tab2.

    Andy

  6. #6
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for sharing this. I am enabling deadlock event monitor to find the needle from the deadlock haystack.

  7. #7
    Join Date
    Dec 2008
    Posts
    76
    Once you have determined the deadlocking statements, sometimes you can prevent deadlocks with better indexes. If one or both statements are doing a tablespace scan, an index that will prevent the scan may avoid the deadly embrace. I emphasize: sometimes.
    RD

Posting Permissions

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