Results 1 to 4 of 4

Thread: Ora-4020

  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: Ora-4020

    Can anyone explain what is going on with this ORA-4020.

    Symptoms:
    Client application hangs trying to insert into an Oracle table (A). Table (A) has an associated (before insert) trigger that generates a unique id from a sequence.
    A .trc file was generated on the Oracle server. The .trc file indicates an ORA-4020 error, but the server did not return to the client. The problem continued for > 5 hours before the DBA reset the session.
    Running (unix) truss on the client process shows a thread waiting on a call to read().


    TRC file

    *** 2006-09-25 10:30:43.173
    *** SESSION ID53.51120) 2006-09-25 10:30:43.172
    A deadlock among DDL and parse locks is detected.
    This deadlock is usually due to user errors in
    the design of an application or from issuing a set
    of concurrent statements which can cause a deadlock.
    This should not be reported to Oracle Support.
    The following information may aid in finding
    the errors which cause the deadlock:
    ORA-04020: deadlock detected while trying to lock object IDD500_OWNER.IDD_TRANSACTION
    --------------------------------------------------------
    object waiting waiting blocking blocking
    handle session lock mode session lock mode
    -------- -------- -------- ---- -------- -------- ----
    34635bec 2e4926a0 2f018988 X 2e4926a0 2fbb848c S
    --------------------------------------------------------
    ---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
    --------------------------------------------------------
    ------------- WAITING LOCK -------------
    ----------------------------------------
    SO: 2f018988, type: 51, owner: 30ad7574, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=2f018988 handle=34635bec request=X
    call pin=0 session pin=0
    htl=2f0189c4[2fbb84c8,2fbd2808] htb=2fbd2808
    user=2e4926a0 session=2e487cc0 count=0 flags=[00] savepoint=193182
    LIBRARY OBJECT HANDLE: handle=34635bec
    name=IDD500_OWNER.IDD_TRANSACTION
    hash=b1d1782b timestamp=09-24-2006 19:16:49
    namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
    kkkk-dddd-llll=0000-074d-074d lock=S pin=S latch#=1
    lwt=34635c04[2f018998,2f018998] ltm=34635c0c[34635c0c,34635c0c]
    pwt=34635c1c[34635c1c,34635c1c] ptm=34635c74[34635c74,34635c74]
    ref=34635bf4[34635bf4, 34635bf4] lnd=34635c80[386f7c24,3450bbc4]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
    366efbe0 4 DEP[01]
    35397d44 3 DEP[01]
    36acb07c 3 DEP[01]
    37684a3c 3 DEP[01]
    3913d8f0 3 DEP/INV[05]
    32761b30 2 DEP[01]
    338db9c4 0 DEP[01]
    36d24d08 0 DEP[01]
    LOCK OWNERS:
    lock user session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    2fbb848c 2e4926a0 2e4926a0 1 S PNC/[04]
    2f018088 2e49d080 2e49d080 1 S PNC/[04]
    2fd6f888 2e4827d0 2e4827d0 1 S PNC/[04]
    2fe11e84 2e49b430 2e49b430 1 S PNC/[04]
    38b56004 2e4a70f0 2e4a70f0 1 S PNC/[04]
    2f017f68 2e483140 2e483140 1 S PNC/[04]
    LOCK WAITERS:
    lock user session count mode
    -------- -------- -------- ----- ----
    2f018988 2e4926a0 2e487cc0 0 X
    PIN OWNERS:
    pin user session lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    2f0198b8 2e49d080 2e49d080 2f018088 2 S 0749
    2fbb9644 2e4827d0 2e4827d0 2fd6f888 2 S 0749
    2fbb9494 2e49b430 2e49b430 2fe11e84 2 S 0749
    37f24ac4 2e4a70f0 2e4a70f0 38b56004 2 S 0749
    2fd6d7a0 2e483140 2e483140 2f017f68 2 S 0749
    2fbb968c 2e4926a0 2e4926a0 2fbb848c 2 S 0749
    LIBRARY OBJECT: object=331b1dcc
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:
    data# heap pointer status pins change
    ----- -------- -------- ------ ---- ------
    0 333f2bcc 331b1e54 I/P/A 0 NONE
    2 32abf4ac 0 -/P/- 0 NONE
    3 32abf448 32abf028 I/P/A 6 NONE
    6 32552dd8 359f73e4 I/P/A 6 NONE
    8 32552d30 338bcb30 I/P/A 6 NONE
    9 32552e3c 39da97ac I/P/A 6 NONE
    10 32552ea0 34e3f364 I/P/A 6 NONE
    ------------- BLOCKING LOCK ------------
    ----------------------------------------
    SO: 2fbb848c, type: 51, owner: 2fc7e7c0, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=2fbb848c handle=34635bec mode=S
    call pin=2fbb968c session pin=0
    htl=2fbb84c8[2fbd2808,2f0189c4] htb=2fbd2808
    user=2e4926a0 session=2e4926a0 count=1 flags=PNC/[04] savepoint=193147
    LIBRARY OBJECT HANDLE: handle=34635bec
    name=IDD500_OWNER.IDD_TRANSACTION
    hash=b1d1782b timestamp=09-24-2006 19:16:49
    namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
    kkkk-dddd-llll=0000-074d-074d lock=S pin=S latch#=1
    lwt=34635c04[2f018998,2f018998] ltm=34635c0c[34635c0c,34635c0c]
    pwt=34635c1c[34635c1c,34635c1c] ptm=34635c74[34635c74,34635c74]
    ref=34635bf4[34635bf4, 34635bf4] lnd=34635c80[386f7c24,3450bbc4]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
    366efbe0 4 DEP[01]
    35397d44 3 DEP[01]
    36acb07c 3 DEP[01]
    37684a3c 3 DEP[01]
    3913d8f0 3 DEP/INV[05]
    32761b30 2 DEP[01]
    338db9c4 0 DEP[01]
    36d24d08 0 DEP[01]
    LOCK OWNERS:
    lock user session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    2fbb848c 2e4926a0 2e4926a0 1 S PNC/[04]
    2f018088 2e49d080 2e49d080 1 S PNC/[04]
    2fd6f888 2e4827d0 2e4827d0 1 S PNC/[04]
    2fe11e84 2e49b430 2e49b430 1 S PNC/[04]
    38b56004 2e4a70f0 2e4a70f0 1 S PNC/[04]
    2f017f68 2e483140 2e483140 1 S PNC/[04]
    LOCK WAITERS:
    lock user session count mode
    -------- -------- -------- ----- ----
    2f018988 2e4926a0 2e487cc0 0 X
    PIN OWNERS:
    pin user session lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    2f0198b8 2e49d080 2e49d080 2f018088 2 S 0749
    2fbb9644 2e4827d0 2e4827d0 2fd6f888 2 S 0749
    2fbb9494 2e49b430 2e49b430 2fe11e84 2 S 0749
    37f24ac4 2e4a70f0 2e4a70f0 38b56004 2 S 0749
    2fd6d7a0 2e483140 2e483140 2f017f68 2 S 0749
    2fbb968c 2e4926a0 2e4926a0 2fbb848c 2 S 0749
    LIBRARY OBJECT: object=331b1dcc
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:
    data# heap pointer status pins change
    ----- -------- -------- ------ ---- ------
    0 333f2bcc 331b1e54 I/P/A 0 NONE
    2 32abf4ac 0 -/P/- 0 NONE
    3 32abf448 32abf028 I/P/A 6 NONE
    6 32552dd8 359f73e4 I/P/A 6 NONE
    8 32552d30 338bcb30 I/P/A 6 NONE
    9 32552e3c 39da97ac I/P/A 6 NONE
    10 32552ea0 34e3f364 I/P/A 6 NONE
    --------------------------------------------------------
    This lock request was aborted.
    *** 2006-09-25 10:31:20.193
    A deadlock among DDL and parse locks is detected.
    This deadlock is usually due to user errors in
    the design of an application or from issuing a set
    of concurrent statements which can cause a deadlock.
    This should not be reported to Oracle Support.
    The following information may aid in finding
    the errors which cause the deadlock:
    ORA-04020: deadlock detected while trying to lock object IDD500
    .....

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone explain what is going on with this ORA-4020.
    As the #1 STICKY post states visit http://asktom.oracle.com
    & do a keyword search on ORA-04020
    Simply put, you are suffering from poor application design/implementation.
    The only fix is to re-write the application.
    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
    Oct 2006
    Posts
    2

    Ora-04020

    I read the Oracle documentation and the postings on 'Ask Tom' before posting here. This is not a simple A->B, B->A deadlock problem. Furthermore, evidence suggests that the problem is NOT in the client application:

    1) The problem occurred during a simple INSERT INTO TABLE call
    2) The problem occurs infrequently, and seems to be related to the recompilation of an invalid trigger on the Oracle server.
    3) The .trc file suggests a loop on the server side; Oracle detects a deadlock between DDL and parse locks, writes to the .trc file and aborts the lock request. Without returning to the client, the same sequence repeats for hours, see attached file.

    There are two questions:
    1) What are the elements involved in the self deadlock
    2) Why, once Oracle detected the self deadlock, did it continue to repeat the same lock acquire steps.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Can you create a test case?

Posting Permissions

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