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: Deadlock Detection - Resolved

RE: Deadlock Detection - Resolved

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 27 Jun 2001 07:56:38 -0700
Message-ID: <F001.0033A0C0.20010627080837@fatcity.com> 







<SPAN 


class=704155414-27062001>All,


<SPAN 


class=704155414-27062001> 


thanks 


to Anita, Waleed, Riyaj, Vladimir and Ravinder for pointing me to Bug #<FONT 
size=2>132899 in regard to my Deadlock problem.
<SPAN 


class=704155414-27062001> 


I 


upgraded the database from release 8.1.6 to 8.1.7.1 (on NT) and the problem went 
away.


<SPAN 


class=704155414-27062001> 


thanks 


again


 


Tom Mercadante <FONT face=Arial 


size=2>Oracle Certified Professional 



  <FONT face=Tahoma 



  size=2>-----Original Message-----From: Mercadante, Thomas F 
  [mailto:NDATFM_at_labor.state.ny.us]Sent: Friday, June 22, 2001 3:48 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Deadlock Detection


  <SPAN 



  class=935062118-22062001>Anita & Riyaj,


  <SPAN 



  class=935062118-22062001> 


  <SPAN 



  class=935062118-22062001>Thanks for the replies.  My email server "went 
  away" for awhile, so I am just getting your replies now.


  <SPAN 



  class=935062118-22062001> 


  <SPAN 



  class=935062118-22062001>Anita, I am able to modify INITRANS for the tables (I 
  tried this on a test table), and I will try this.  Right now, we are 
  performing other stress tests to narrow down the problem to be sure that it is 
  update related (has to be, right?).


  <SPAN 



  class=935062118-22062001> 


  <SPAN 



  class=935062118-22062001>Riyaj, I checked the value of  max_utilization 
  vs initial_Allocation in the v$resource_Limit view for distributed 
  transactions.  Max_util=5, while init_alloc=61.  Is the value of 61 
  a default?  I do not have it set in my init.ora (maybe I should! - 
  something else to try).  The value of Transactions is max=5, init=247 and 
  limit=247.


  <SPAN 



  class=935062118-22062001> 


  It 


  doesn't seem to be stressed according to these values, 
  right?


  <SPAN 



  class=935062118-22062001> 


  As 


  for distributed Trans, we are using VB calling ADO using COM+ connecting to 
  Oracle.  My impression is that ADO and COM+ are performing the 
  distributed trans, but that the ADO performs the commits?  
  


  <SPAN 



  class=935062118-22062001> 


  I'm 


  not at all familiar with how ADO/COM+ works, but I guess I better bring a book 
  home and figure it out.


  <SPAN 



  class=935062118-22062001> 


  Any 


  other ideas, I would greatly appreciate it.


  <SPAN 



  class=935062118-22062001> 


  <SPAN 



  class=935062118-22062001>thanks


  Tom Mercadante <FONT face=Arial 


  size=2>Oracle Certified Professional 
  


    <FONT face=Tahoma 


    size=2>-----Original Message-----From: Riyaj_Shamsudeen_at_i2.com 
    [mailto:Riyaj_Shamsudeen_at_i2.com]Sent: Friday, June 22, 2001 2:17 
    PMTo: Multiple recipients of list ORACLE-LSubject: Re: 
    Deadlock DetectionHi 


    Anita         If 


    it is an ITL problem, then the resource type would be TX instead of DX. If 
    the ITL table is full then the process requesting an ITL entry in that block 
    will randomly select one of the transaction holding an ITL entry and wait 
    for that process to complete or rollback. Since the process will wait for a 
    transaction and as you are well aware of, the transaction id is a slot in a 
    rollback segment and hence the enqueue type would be TX. Since this trace 
    indicates that it is a DX type enqueue I would incline to think that this is 
    a distributed transaction problem. Feel free to correct me if I am missing 
    something.. ThanksRiyaj 


    "Re-yas" ShamsudeenCertified Oracle DBAi2 technologies   
    www.i2.com 
    

      
      
        
        "A. Bardeen" 
          <abardeen1_at_yahoo.com> <FONT face=sans-serif 
          size=1>Sent by: root_at_fatcity.com 
          06/22/01 10:50 AM <FONT 
          face=sans-serif size=1>Please respond to ORACLE-L 
                
                  
          To:        Multiple recipients of list ORACLE-L 
          <ORACLE-L_at_fatcity.com> <FONT face=sans-serif 
          size=1>        cc:       
                  
            Subject:        Re: Deadlock 
          Detection<FONT 




    face="Courier New" size=2>Tom,The information you need should be in 
    the trace file,but it's not in the excerpt you've listed, but 
    thetrue problem is listed:> Rows waited on:> Session 
    27: no row"No row" indicates that the deadlock is due to a 
    lackof available ITL slots in the datablock.Unfortunately 
    resolving this requires recreating theobject with either a high initrans 
    value (toexplicitly reserve space for more ITL slots) or ahigher 
    pctfree value (to give the ITL table more roomto grow).Depending 
    on the application it could also be thatmultiple sessions are acquiring 
    the same block off thefree list so using multiple free lists could 
    alsohelp.  Starting with 8.1.6 this setting can be 
    changeddynamically; prior to that you must recreate 
    theobject.Note: 62365.1 also has some good 
    infoHTH,-- Anita--- "Mercadante, Thomas F" 
    <NDATFM_at_labor.state.ny.us>wrote:> All,> > My 
    current application (still under development) is> experiencing 
    Oracle> deadlock problems.  The applications people are> 
    performing stress testing> where the application is being repeatedly 
    called> simulating actual users> hitting the database.> 
    > The application is written using VB thru ADO and> COM, 
    Oracle 816 on NT.> > My problem is that, while I can review 
    the trace> file produced, I can't> figure out what the actual 
    deadlock is occurring on.>  I have seen deadlock> trace 
    files that clearly state "table blah", but in> this case, I 
    get:> > > *** 2001-06-21 14:32:03.841> *** 
    SESSION ID:(27.31211) 2001-06-21 14:32:03.810> DEADLOCK 

    DETECTED> Deadlock graph:>           
                


     ---------Blocker(s)--------> 


    ---------Waiter(s)---------> Resource Name       
       process session holds waits > process session 
    holds> waits> DX-0000003b-00000000       
     22      18     X       


     >     24      27> X> 


    session 18: DID 0001-0018-0000003C             
        session 27: DID> 0001-0018-0000003C> Rows waited 
    on:> Session 27: no row> *** 2001-06-21 14:32:03.857> 
    ksedmp: internal or fatal error> ORA-00060: deadlock detected while 
    waiting for> resource> > Is there something like TKPROF 
    that will process the> trace file and give me> more info on 
    what is happening?  It looks like I am> waiting for a 
    resource> to be freed, but which one is the question.> 
    > thanks for any help.> > Tom Mercadante> Oracle 
    Certified Professional> > -- > Please see the official 
    ORACLE-L FAQ:> http://www.orafaq.com> -- > Author: 
    Mercadante, Thomas F>   INET: NDATFM_at_labor.state.ny.us> 
    > 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).__________________________________________________Do 


    You Yahoo!?Get personalized email addresses from Yahoo! 
    Mailhttp://personal.mail.yahoo.com/-- Please see the official 
    ORACLE-L FAQ: http://www.orafaq.com-- Author: A. 
    Bardeen INET: abardeen1_at_yahoo.comFat City Network Services 
       -- (858) 538-5051  FAX: (858) 538-5051San Diego, 
    California        -- Public Internet access / Mailing 

    Lists--------------------------------------------------------------------To 


    REMOVE yourself from this mailing list, send an E-Mail messageto: 
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe 
    message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
    mailing list you want to be removed from).  You mayalso send the 
    HELP command for other information (like 
  subscribing).
Received on Wed Jun 27 2001 - 09:56:38 CDT












Original text of this message













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