Re: kill session -- find out the reason for blocking

"Doron" <doron_almog_at_msn.com> schrieb im Newsbeitrag 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,

also find out why the app gets the session blocking. It may also be a data model design mistake that I observed some months ago: If You have Foreign Keys defined __but not indexed__, You will get full table locks, and locks upon locks,
then You have blocking sessions (v$session.lockwait is not null). And so if You have about 200+ users using Your app, the system gets stuck several times a day.

In the first place I too thought that killing the blocking session would be the only way out,
but it isnīt. After creating the missing indexes, You may see a remarkable decrease of blocking.
In my case, they almost deminished.

hth, Jan Received on Tue Jan 13 2004 - 05:31:16 CST