deadlock interpretation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: deadlock interpretation

  1. #1
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78

    deadlock interpretation

    Oracle version - 9i rel 2
    O/S version - Solaris 9

    Hi,

    I have found a deadlock which happened one day ago. I have pasted that trace file. Now I have to know two things.

    1) How can I detect whether the following deadlock is due to row-level locking or transaction layer locking (since I am already one day delay i.e I am trying to find out the reason on 1day after the day lock happened)?

    2) I am able to find out the row having rowid 'AAAHzHAAnAAAA7hAAP'. But I am not able to find
    out any row having rowid 'AAAH/gAAiAAAByOAA0'. Why? Someone has deleted that row ? A rowid
    can have '/' ?

    DEADLOCK DETECTED
    Current SQL statement for this session:
    UPDATE SSP_PROGRAMME_SCHEDULE
    SET modify_dt=SYSDATE
    WHERE SCHD_KEY=:b1
    ----- PL/SQL Call Stack -----
    object line object
    handle number name
    94f1b1d4 2 STOWNER.TR_SSP_PROGRAMME_SCHEDULE
    994a0858 268 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
    994a0858 2549 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
    994a0858 6202 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
    8f90b6d0 1 anonymous block
    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-00030007-0001e95b 60 60 X 45 136 X
    TX-00060025-0000ff93 45 136 X 60 60 X
    session 60: DID 0001-003C-00000002 session 136: DID 0001-002D-00000002
    session 136: DID 0001-002D-00000002 session 60: DID 0001-003C-00000002
    Rows waited on:
    Session 136: obj - rowid = 00007FE0 - AAAH/gAAiAAAByOAA0
    (dictionary objn - 32736, file - 34, block - 7310, slot - 52)
    Session 60: obj - rowid = 00007CC7 - AAAHzHAAnAAAA7hAAP
    (dictionary objn - 31943, file - 39, block - 3809, slot - 15)
    Information on the OTHER waiting sessions:
    Session 136:
    pid=45 serial=1983 audsid=47043 user: 44/
    O/S info: user: wsadmin, term: , ospid: 19810, machine: piranha
    program: java@piranha (TNS V1-V3)
    application name: java@piranha (TNS V1-V3), hash value=0
    Current SQL Statement:
    DELETE FROM SSP_PROGRAMME_PART
    WHERE schd_episode_key = :b1
    End of information on OTHER waiting sessions.

    Regards

    Deba

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    Thanks for the reply.

    Your answer is not clear to me. I have seen the metalink document. But still I am not getting the answers. Would you like to explain pl.

    In both the cases (metalink document and in my case) the locks are same . Then why I will not get the row against the above rowid ?

    And what about the firts question ?

    Regards

    Deba

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check the package STOWNER.SSP_PROCESS_SCHEDULE_PKG to what exactly is happening

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Answer to the 1st Question:
    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-00030007-0001e95b 60 60 X 45 136 X
    TX-00060025-0000ff93 45 136 X 60 60 X

    It is very clear that the sessions (60 and 130) held resources that were needed by other sessions. Check your application whether it is using order of locking the tables are same or not. If not, change it.

    Answer to 2nd question:
    A rowid cannot have /. The original character might have been overwritten by '/' due to accident.

    Tamil

  6. #6
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    Tamil,

    thanx for the reply. What you have told me that I know and its is very standard one. I think you have not gone through my questions properly.

    q1> deadlock is due to row-level locking or transaction layer locking?
    How to understand whether it is row-level locking or transaction
    layer locking?

    q2> What I have pasted , that is 100% correct. '/' is there in rowid.
    Since it is starnge to me , that's why I am asking.

    Regards

    Deba

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    It was due to transaction layer lock.
    You can see the TX locks on both sessions.

  8. #8
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    Hi Tamil,

    you are saying that "It was due to transaction layer lock.
    You can see the TX locks on both sessions.". But I have seen a deadlock trace file which contains TX lock and that deadlock was due to row level locking.

    If TX is meant for Transaction layer locking , then what indicates in the trace file that the deadlock is due to row level locking.

    Deba

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you see ora-60 error in the beginning of trace file?

Posting Permissions

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



Click Here to Expand Forum to Full Width