| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locking Issue
Kevin,
Here's one that has been posted to our site recently by a customer (www.cool-tools.co.uk >Support > User Defined Collections > BLOCKER):
select l.sid sid,
s.username username,
s.program program,
t.sql_text,
u.name owner,
o.name object,
l.type type,
lmode,
decode (lmode,1,'NULL',2,'Row Share',3,'Row
Exclusive',4,'Share',5,'Share Row',6,'Exclusive') mode_desc,
request,
decode (request,1,'NULL',2,'Row Share',3,'Row
Exclusive',4,'Share',5,'Share Row',6,'Exclusive') request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW','TM','TX','UL')
and l.sid=s.sid(+)
and l.id1 = o.obj# (+)
and o.owner#=u.user#(+)
and s.sql_hash_value = t.hash_value
and lmode > 0
The only problem with running this is that you still have a chance that you are not going to catch the offending statement, as it could have been aged out of the SQLAREA., or the offending user/session may have moved on to another transaction.
There was a thread about this on the list a little while ago, and it is quite hard (impossible) to *Guarantee* catching the offending statements. We are "five nines" sure that we can do this now though, unless in extreme cases where a user takes a lock out - when nobody else is accessing the system - and half an hour later somebody tries to access the table, and the lock has not been released. What we now do is run the "rule" every 1-2 minutes to monitor for blocked sessions.
You could most probably run a cron job to fire this statement off every 1 minute or so, and should be able to get to the bottom of the problem from there. Personally I would not leave this statement at such a low refresh interval continuously though, and I can't help with cron or as I haven't got a clue how to use it :P
HTH Mark
-----Original Message-----
Sent: Thursday, August 23, 2001 17:06
To: Multiple recipients of list ORACLE-L
Thanks Christopher. I will see what I can get out of them.
-----Original Message-----
Sent: Thursday, August 23, 2001 10:24 AM
To: Multiple recipients of list ORACLE-L
Take a look at 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
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Wednesday, August 22, 2001 2:47 PM
To: Multiple recipients of list ORACLE-L
Back to the experts ................
We have an application that, litterally overnight, developed locking issues.
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.
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.
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 samecumulated 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.
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.
If anyone has any insights on how to find this one cause ... I would appreciate hearing them.
And , if you happen to have any scripts that would help me trace the locks thru the database I would appreciate them also.
Thanks
Kevin
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Fri Aug 24 2001 - 05:27:10 CDT
--------------------------------------------------------------------
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).
 |
 |