RE: Locking Issue

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 23 Aug 2001 07:07:33 -0700
Message-ID: <F001.0037515C.20010823072356@fatcity.com>

Take a  look at <A
href="http://www.vampired.net">www.vampired.net under scripts and locks, there are a few decent scripts there.
"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence <FONT

face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" 
size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS" 
size=2>Fax:    (707) 885-2275 

Fuelspot <FONT

face="Comic Sans MS" size=2>73 Princeton Street <FONT 
face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT 
face="Comic Sans MS" size=2>  

  

<FONT

  face=Tahoma size=2>-----Original Message-----From: Kevin Lange   [mailto:kgel_at_ppoone.com] Sent: Wednesday, August 22, 2001 2:47   PMTo: Multiple recipients of list ORACLE-LSubject:   Locking Issue
      <SPAN
  class=060542217-22082001>Back to the experts

  ................

<SPAN

  class=060542217-22082001> 
<SPAN

  class=060542217-22082001> 
  We have an
  application that, litterally overnight, developed locking   issues.
<SPAN

  class=060542217-22082001> 
  Our users
  start working just fine.   Then, right now cause unknown, our   inserts start being blocked.   Usually there are so many so fast   that we do not even know what is causing the original lock.
<SPAN

  class=060542217-22082001> 
  The tables
  the users are being blocked from have multiple triggers and the tables that   these triggers point to ofen have triggers of their own.
<SPAN

  class=060542217-22082001> 
<SPAN

  class=060542217-22082001> 
  So, the
  scenario is
  1. 
  Web Application pointing to an Oracle 8.0.5 database.   2. Things
  start out just fine.
  3. At a
  variable time later (i.e.  not the same time of day, not the same   cumulated time since startup, etc.) locks start to appear against our main   table.  The users getting the locks are trying to insert a   record.
  4. Locks
  cascade until the only recourse is to restart the database.
<SPAN

  class=060542217-22082001> 
  We are
  having trouble tracing the locks back to their origin.    

  There has
  got to be 1 thing that is causing the start of this   cascade.
<SPAN

  class=060542217-22082001> 
  If anyone
  has any insights on how to find this one cause ...  I would appreciate   hearing them.
<SPAN

  class=060542217-22082001> 
  And , if
  you happen to have any scripts that would help me trace the locks thru the   database I would appreciate them also.
<SPAN

  class=060542217-22082001> 
  Thanks   

<SPAN

  class=060542217-22082001> 
<SPAN

  class=060542217-22082001>Kevin Received on Thu Aug 23 2001 - 09:07:33 CDT