Re: Oracle LOCKS - looking for more info

Zoran (zoranm_at_echo-on.net) wrote:
> Hi all,
 

> I see the V$ tables like V$LOCK and V$LOCKED_OBJECT but am trying to
> make sense of what they are telling me.
 

> Where can I learn more about locks in Oracle 7.3?
 

> Specifically, what rows are locked by whom and who is being blocked.
> I've looked up these table descriptions in the SQL Server Reference
> manual, but am still unclear on what all that info is implying.
 

> I have an application that every now and then grinds to a halt. I have a
> peek at the V$LOCK and V$LOCKED_OBJECT and see some locks that are not
> going away. But I don't know if there's a deadlock (I'm SURE Oracle will
> detect a deadlock and rollback *An* offender) or not. I'm not convinced
> there is a DB deadlock, but knowing more about locking in Oracle would
> be of great help.

i think you need to get a better idea as to what is causing the application to halt as it may or may not be related with blocking locks.

Try looking at the v$session_wait table to find out what event each session is waiting on or running at the time the system is grinding to a halt. If you see a lot of "enqueue" events, then you are indeed seeing blocking locks. If you see a lot of other events, then it's probably not related with application locks...what it is will depend on what event you do see a lot of.

Oracle will always resolve deadlocks. Note that this is not the same as blocking locks, which is usually caused by row contention or poor application locking coding.

Hope this helps some,

Johnny Chan
Independent Oracle Specialist Received on Fri Mar 28 1997 - 00:00:00 CST