Re: How to free a locked object?

On Jan 23, 2:37 am, dbagtcol <cx4gt...@xxxxxxxxx> wrote:
hi all,
I'm trying a compile a proc but the run would just go forever and
throw 'ORA-04021: timeout occurred while waiting to lock object xxx'
at the end. Upon checking the v$session_wait, i found "latch: cache
buffers chains" as Event and "Concurrency" as Wait_class. On checking v
$locked_object, i found one object being held. I don't have access to
alert_log file. How do I find who is using this object and how do I
free it for myself?

Appreciate your kind responses.
gtcol

The alert log won't tell you who (which user and session) is holding
that lock, but V$LOCKED_OBJECT will; I'm surprised you didn't see that
information from your query. You could run this query (presuming you
have DBA access):

select s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name
object, k.kaddr,
decode(l.locked_mode, 1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Shared Table',
5, 'Shared Row Exclusive',
6, 'Exclusive') locked_mode,
decode(k.type, 'BL','Buffer Cache Management (PCM lock)',
'CF','Controlfile Transaction',
'CI','Cross Instance Call',
'CU','Bind Enqueue',
'DF','Data File',
'DL','Direct Loader',
'DM','Database Mount',
'DR','Distributed Recovery',
'DX','Distributed Transaction',
'FS','File Set',
'IN','Instance Number',
'IR','Instance Recovery',
'IS','Instance State',
'IV','Library Cache Invalidation',
'JQ','Job Queue',
'KK','Redo Log Kick',
'LA','Library Cache Lock',
'LB','Library Cache Lock',
'LC','Library Cache Lock',
'LD','Library Cache Lock',
'LE','Library Cache Lock',
'LF','Library Cache Lock',
'LG','Library Cache Lock',
'LH','Library Cache Lock',
'LI','Library Cache Lock',
'LJ','Library Cache Lock',
'LK','Library Cache Lock',
'LL','Library Cache Lock',
'LM','Library Cache Lock',
'LN','Library Cache Lock',
'LO','Library Cache Lock',
'LP','Library Cache Lock',
'MM','Mount Definition',
'MR','Media Recovery',
'NA','Library Cache Pin',
'NB','Library Cache Pin',
'NC','Library Cache Pin',
'ND','Library Cache Pin',
'NE','Library Cache Pin',
'NF','Library Cache Pin',
'NG','Library Cache Pin',
'NH','Library Cache Pin',
'NI','Library Cache Pin',
'NJ','Library Cache Pin',
'NK','Library Cache Pin',
'NL','Library Cache Pin',
'NM','Library Cache Pin',
'NN','Library Cache Pin',
'NO','Library Cache Pin',
'NP','Library Cache Pin',
'NQ','Library Cache Pin',
'NR','Library Cache Pin',
'NS','Library Cache Pin',
'NT','Library Cache Pin',
'NU','Library Cache Pin',
'NV','Library Cache Pin',
'NW','Library Cache Pin',
'NX','Library Cache Pin',
'NY','Library Cache Pin',
'NZ','Library Cache Pin',
'PF','Password File',
'PI','Parallel Slaves',
'PR','Process Startup',
'PS','Parallel slave Synchronization',
'QA','Row Cache Lock',
'QB','Row Cache Lock',
'QC','Row Cache Lock',
'QD','Row Cache Lock',
'QE','Row Cache Lock',
'QF','Row Cache Lock',
'QG','Row Cache Lock',
'QH','Row Cache Lock',
'QI','Row Cache Lock',
'QJ','Row Cache Lock',
'QK','Row Cache Lock',
'QL','Row Cache Lock',
'QM','Row Cache Lock',
'QN','Row Cache Lock',
'QO','Row Cache Lock',
'QP','Row Cache Lock',
'QQ','Row Cache Lock',
'QR','Row Cache Lock',
'QS','Row Cache Lock',
'QT','Row Cache Lock',
'QU','Row Cache Lock',
'QV','Row Cache Lock',
'QW','Row Cache Lock',
'QX','Row Cache Lock',
'QY','Row Cache Lock',
'QZ','Row Cache Lock',
'RT','Redo Thread',
'SC','System Commit number',
'SM','SMON synchronization',
'SN','Sequence Number',
'SQ','Sequence Enqueue',
'SR','Synchronous Replication',
'SS','Sort Segment',
'ST','Space Management Transaction',
'SV','Sequence Number Value',
'TA','Transaction Recovery',
'TM','DML Enqueue',
'TS','Table Space (or Temporary Segment)',
'TT','Temporary Table',
'TX','Transaction',
'UL','User-defined Locks',
'UN','User Name',
'US','Undo segment Serialization',
'WL','Writing redo Log',
'XA','Instance Attribute Lock',
'XI','Instance Registration Lock') type
from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects
o, sys.v_$lock k, v$_lock v
where o.object_id = l.object_id
and l.session_id = s.sid
and k.sid = s.sid
and s.saddr = c.saddr
and k.kaddr = c.kaddr
and k.kaddr = v.kaddr
and v.saddr = s.saddr
and k.lmode = l.locked_mode
and k.lmode = c.lmode
and k.request = c.request
order by object;

This should provide all of the information you would need about the
lock and who is holding it. And, again, presuming you have DBA access
you could kill the 'offending' session; that's the only way you'll
clear that lock if it isn't your current session holding it.

If you haven't DBA access then you need to wait until the offending
session issues a commit, a rollback or dies.


David Fitzjarrell
.

Relevant Pages

  • Re: Problem with BDC "View Profile" link
    ... but it seems to "short circuit" while it is reading the BDC cache. ... 71qj High Acquired Read lock on LobSystemInstance cache ... 71qj High Acquired Read lock on MethodInstance cache ... 71qj High Acquired Read lock on TypeDescriptor cache ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Fenceless atomic RMW
    ... let's assume that the cache line is already in cache in E state ... cache lock or address lock (or even a bus lock for ... So, the question is, how do you do the load part of the atomic RMWS? ... draining the store buffer so that the RMW is sequentially consistentg ...
    (comp.arch)
  • Re: ASP requests and locking
    ... Typically you would cache immutable content. ... There is no need to lock ... How many properties will you reading per request and will they ... Anthony Jones - MVP ASP/ASP.NET ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: queued spinlock code and results
    ... max number of times in a row that a lock is acquired, ... xadd-lock in cache takes 8.93ns ... static inline void unlock ... static int xlock_is_locked ...
    (Linux-Kernel)
  • Re: Simulation software
    ... Pin is a generic instrumentation framework for binary code. ... instruction mix measurements, to concurrency detection. ... There is at least one example of a cache simulation pintool. ...
    (comp.arch)