"Ryan" <rgaffuri_at_cox.net> wrote in
news:XDZ_9.81330$GX4.3404614_at_news2.east.cox.net:
> I have seen cases where a small transaction will stay locked and PMON
> does not clear it up. Ive seen several people ask about this on
> lazyDBA as well. So its not unique.
>
>
><Kenneth Koenraadt> wrote in message
> news:3e3c34f8.1036870_at_news.inet.tele.dk...
>> On Sat, 01 Feb 2003 19:48:10 GMT, "Ryan" <rgaffuri_at_cox.net> wrote:
>>
>> >well what if you have a web application with say 50 userse. often
>> >times
> you
>> >wont run those in shared server mode.
>> >
>>
>> That's what I called an "in-house" application. Whether it is
>> web-based or OCI-based or anything else is irrelevant in this
>> context.
>>
>> When a user gets disconnected, the PMON will clear the server
>> process, rollback its uncommitted transactions and release its locks.
>> As such no problem, but if a large (uncommitted) transaction was
>> taking place at the time of the disconnect, the clean up and thus the
>> release of the locks may require some time.
>>
>>
>> >is there any documentation on resolving deadlocks? not just avoiding
> them.
>>
>> Sure, see the on-line doc --> deadlocks.
>>
>>
>> >
>> >its not just a deadlock though. User A locks a row. User A gets
> disconnect.
>> >Row is still locked. Noone else can touch the lock until its
>> >released.
>> >
>> That's correct. In fact, thats' the definition of a lock :-).
>> Be glad that PMON keeps the lock until it has recovered fully, else
>> your DB would soon be inconsistent.
>>
>>
>> >my undrestanding is that its only a 'deadlock' if User A and User B
>> >block each other?
>>
>> Yep. But deadlocks are not "only", as you suggest. They are usually
>> much more likely to undermine you application than single locks
>> waiting to be released because of users getting disconnected.
>> >
>> ><Kenneth Koenraadt> wrote in message
>> >news:3e3c0aaa.4004057_at_news.inet.tele.dk...
>> >> On Sat, 01 Feb 2003 17:51:24 GMT, "Ryan" <rgaffuri_at_cox.net> wrote:
>> >>
>> >> >I have seen this question posted in other places without adequate
>> >answers. I
>> >> >think this happened to me one time as well.
>> >> >
>> >> >If you are connected to the internet through the web and lock a
>> >> >row.
> You
>> >> >lose your connection. Lock stays on. My assumption is you have to
>> >> >kill
>> >the
>> >> >session.
>> >> >
>> >> >What if you have 500 users and this happens. What is the best
>> >> >process
> for
>>
>
>
>
Depending upon how desparate you are,
one of the V$%LOCK% views contains a field
which is the time when the lock was obtained.
Via DBMS_JOB you could either simply kill the
offending session which held a lock "too long",
or notify a human so s/he could make the decision.
FWIW - I suggest that you only monitor "exclusive"
locks & not be concerened about share locks.
HTH & YMMV
HAND!
Received on Sat Feb 01 2003 - 20:34:01 CST
