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

RE: Deadlock Detection

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 22 Jun 2001 11:45:34 -0700
Message-ID: <F001.0033429E.20010622114819@fatcity.com> 







Anita 


& Riyaj,


<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> 


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> 


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 
              <FONT 
        face=sans-serif size=1>        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> > 


Received on Fri Jun 22 2001 - 13:45:34 CDT












Original text of this message













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