Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: Identifying a deadlock ORA-00060

    Hi all.

    I'm facing a deadlock which I'm having trouble to figure out. At the end of this message is part of my oracle deadlock file. Right below are my general comments.

    My application consists of many identical processes performing the same operation, but on different sets of data. The particular deadlock file at the end has three processes in deadlock. All three processes on the same exact sql statement, which is an update.
    In theory, it should not happen that more than one process perform the same update with the same primary key (ID_FK1 and ID_FK2 form the PK). However, as you can see in the file, this seems to be happening with sessions 423 and 63. I say that because the file shows the same rowid and dictionary obj for these sessions. An interesting thing is that when I search for any of the rowids in the file, I can't find them in TABLE_A (the one which is being updated). Why?

    I also wonder the reason the blocker 'holds' are described by an 'X' and the waiter 'holds' are described by an 'S'. Shouldn't the blocker 'holds' be a 'RX' (row exclusive table lock)? In addition, what important information can I take from the transaction id (the TX-....)?

    A last question is if there's a way to identify the values of the parameters being passed to the update (b1, b2, b3 and b4). I did a few queries on some oracle views at the moment the deadlock was happening but I couldn't find one with this information (although I must say that I didn't have access to many dba views at the moment, so my attemps were not the best as they could have been). Attaching a debugger at runtime is not also a good idea because the application is in production. (I haven't been able to reproduce it in my testing environment).

    Well, I'd appreciate any suggestion that helps me precisely identify this deadlock.

    Thank you for your time,

    Leandro Melo.


    *** SESSION ID:(67.33860) 2007-09-06 06:00:06.460
    DEADLOCK DETECTED
    Current SQL statement for this session:
    update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)
    The following deadlock is not an ORACLE error. It is a
    deadlock due to user error in the design of an application
    or from issuing incorrect ad-hoc SQL. The following
    information may aid in determining the deadlock:
    Deadlock graph:
    ---------Blocker(s)-------- ---------Waiter(s)---------
    Resource Name process session holds waits process session holds waits
    TX-00280018-000df8dc 841 76 X 48 423 S
    TX-005e001b-00005ecf 48 423 X 27 63 S
    TX-00180002-0017bb69 72 63 X 841 76 S
    session 76: DID 0001-0094-000006A4 session 423: DID 0001-0054-00001A6F
    session 423: DID 0001-0054-00001A6F session 63: DID 0001-0048-000024F2
    session 63: DID 0001-0048-000024F2 session 76: DID 0001-0094-000006A4
    Rows waited on:
    Session 423: obj - rowid = 0000E0E9 - AAAODpAMvAAAAAAAAA
    (dictionary objn - 98658, file - 815, block - 0, slot - 0)
    Session 63: obj - rowid = 0000E0E9 - AAAODpAMvAAAAAAAAA
    (dictionary objn - 98658, file - 815, block - 0, slot - 0)
    Session 76: obj - rowid = 0000E261 - AAAOJhAORAAAAAAAAA
    (dictionary objn - 45455, file - 913, block - 0, slot - 0)
    Information on the OTHER waiting sessions:
    Session 423:
    pid=48 serial=46832 audsid=112200496 user: 80/MYUSER
    O/S info: user: myuser, term: , ospid: 12222, machine:
    program: PEXP@stak (TNS V1-V3)
    application name: PEXP@stak (TNS V1-V3), hash value=0
    Current SQL Statement:
    update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)
    Session 63:
    pid=72 serial=35788 audsid=112198034 user: 80/MYUSER
    O/S info: user: myuser, term: , ospid: 25836, machine: stak
    program: PEXP@stak (TNS V1-V3)
    application name: PEXP@stak (TNS V1-V3), hash value=0
    Current SQL Statement:
    update TABLE_A set ID_PARAM=:b1, DT_OPERATION=TO_DATE(:b2,'YYYYMMDD') where (ID_FK1=:b3 and ID_FK2=:b4)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >A last question is if there's a way to identify the values of the parameters being passed to the update (b1, b2, b3 and b4).
    Yes, there is a way, but it might be easier said than done.

    If you can enable event 10146 at level 12, then the trace file will contain the values of the bind variable.

    http://www.petefinnigan.com/index.htm contains multiple ways for enabling SQL_TRACE & at least 1 for level=12

    Also I suspect that your conclusion that "I can't find them in TABLE_A (the one which is being updated)." is pilot problem & that Oracle accurately identified the details involving the deadlock.

    Is there any way that the row could have been deleted between the deadlock event & when you went to look for it?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2007
    Posts
    2
    Quote Originally Posted by anacedent
    >Also I suspect that your conclusion that "I can't find them in TABLE_A (the one which is being updated)." is pilot problem & that Oracle accurately identified the details involving the deadlock.

    Is there any way that the row could have been deleted between the deadlock event & when you went to look for it?
    Not really. There's not a process that deletes from this table. It only receives inserts, updates and selects. Any other idea?

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Also I suspect that your conclusion that "I can't find them in TABLE_A (the one which is being updated)." is pilot problem.
    Oracle accurately identified the details involving the deadlock.
    Use CUT & PASTE to capture whole SQL*Plus session showing your steps to identify the suspect row.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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