Re: kill session that holds lock for more then 10 sec

doron_almog_at_msn.com (Doron) wrote in message news:<995517bc.0401121011.3b9b6799_at_posting.google.com>...
> hi,
>
> our application requests exclusive locks at the table level ( i know
> it's very problematic but right now it's a must and i cant change the
> code).
>
> every now and then a user forgets to commit on a crucial table and all
> other users are put in wait. to resolve i have to manually kill the
> user's session.
>
> is it possible to automaticlly kill a session holding an exlusive lock
> for more then a predefined amount of time, lets say 60 sec. i know
> this will cause new problems but i want to give it a try.
>
> thanks,
> Doron

Doron,

with msn.com for a mail domain, I'm guessing that you're running on a win32 OS (as no self-respecting linux user would ever use msn.com ;P).

ALTER SYSTEM KILL SESSION is not the way to go.

you could conceivably wait hours for the session to be killed and the lock(s) to be released.
don't go with a DBMS_JOB, schedule an OS task so that you can leverage orakill.exe. that will seriously kill a session.

basically, use a batch file to call sqlplus. in sqlplus run a script to spool an orakill script quit sqlplus and execute the spooled .bat file for bonus points, you could log the offending user that held the lock too long (although 60 seconds seems to be a little too short to me).

D:\oracle\ora81\bin>orakill

Usage: orakill sid thread

  where sid = the Oracle instance to target

        thread = the thread id of the thread to kill

  The thread id should be retrieved from the spid column of a query such as:

        select spid, osuser, s.program from
        v$process p, v$session s where p.addr=s.paddr

I'd seriously look at what rows are being locked, this info is available from the v$views. You might find that whatever DML is holding locks is being too coarse, and making the where clause more selective can reduce the impact on concurrency. I'd also only kill sessions that are actually blocking other sessions (use the view dba_blockers) unless they have held locks for a long time and their last_call_et in v$session is large.

hth.

Pd Received on Tue Jan 13 2004 - 00:52:30 CST