Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-04020: deadlock detected while trying to lock object...

RE: ORA-04020: deadlock detected while trying to lock object...

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Fri, 09 Feb 2001 09:01:53 -0800
Message-ID: <F001.002B0708.20010209082050@fatcity.com>

Hi

   When you are compiling an object, Oracle has to pin the dependent objects in an exclusive mode. In rare cases, you may go in to deadlock situations if your dependent objects has some form of recursive relationship and need to be compiled too . I would suggest to find the dependent objects (look at dba_dependencies) and compile them first. Then try to compile your object. If still you have a problem, then when the process is hanging, look at the v$session_Wait to find out what the session is waiting for. If it is waiting for library cache pin on a particular object then you could look at x$kgllk table to find out who else if using that particular object(kglnaobj is the object name and you could use this column ).
  Otherwise, before compiling the object from sqlplus , dump the errorstack like this and then compile the object. Look at the trace file generated to find the offending session.:

     alter session set events '4020 trace name errorstack, level 15';

  Hope this helps!!
~
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"These are my opinions and does not bind my employer"

                                                                                       
                       
                    "Mohammad                                                          
                       
                    Rafiq"               To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>  
                    <rafiq9857_at_ho        cc:                                           
                       
                    tmail.com>           Subject:     RE: ORA-04020: deadlock detected 
while trying to lock   
                    Sent by:             object...                                     
                       
                    root_at_fatcity.                                                      
                       
                    com                                                                
                       
                                                                                       
                       
                                                                                       
                       
                    02/09/01                                                           
                       
                    08:36 AM                                                           
                       
                    Please                                                             
                       
                    respond to                                                         
                       
                    ORACLE-L                                                           
                       
                                                                                       
                       
                                                                                       
                       




It also memory related issue. Reboot your(tired) box (if possible) and try again. You will see objects will be compiled without locking problem and will be much fast....

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 09 Feb 2001 05:10:25 -0800

I saw this note on MetaLink that may help.

Rick

  Error: ORA 4020
  Text: deadlock detected while trying to lock object %s%s%s%s%s


---
  Cause:  While trying to lock a library object, a deadlock is detected.
  Explanation:
          During compilation we hit a deadlock situation.
          This is generally caused by a circular dependency of some form.

  Diagnosis:
          Find out what objects are being compiled in what order.

          Changing the order of creation should prevent this.
          When creating objects a SAFE order of creation is:
                  TABLE, PACKAGE, PACKAGE BODY, TRIGGER



> -----Original Message-----
> From: Uldis [SMTP:u.pavuls_at_konts.lv] > Sent: Friday, February 09, 2001 7:05 AM > To: Multiple recipients of list ORACLE-L > Subject: ORA-04020: deadlock detected while trying to lock object... > > Need guru! :) > > At attempt to compile procedure we're getting subj. Nevertheless there is > no > more sessions accessing the schema. > > About environment: > Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production > PL/SQL Release 8.1.6.0.0 - Production > CORE 8.1.6.0.0 Production > TNS for Solaris: Version 8.1.6.0.0 - Production > NLSRTL Version 3.4.0.0.0 - Production > > TIA, > Uldis > > Uldis Pavuls > DBA, Tieto Konts Financial Systems Ltd. > Kr.Barona 32, Riga, Latvia, LV 1011 > phone +371 7 286 660, fax +371 7 243 000 > mailto:U.Pavuls@konts.lv, http://www.konts.lv > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Uldis > INET: u.pavuls_at_konts.lv > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: RICHARD.T.CALE_at_saic.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Riyaj_Shamsudeen_at_i2.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Fri Feb 09 2001 - 11:01:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US