Re: Session does not terminate when a connection drops while running PLSQL.

jimlorraine_at_hotmail.com (jim rowe) wrote in message news:<9a05ce47.0207040526.6041c2a4_at_posting.google.com>...
> Oracle 8.1.7.0.0
> Platform NT.
>
> A client application is connected via net8 (TCP) to a database server.
> The client performs an update transaction which triggers a PL/SQL
> procedure which takes several minutes to run.
>
> While the PLSQL procedure is running the client application dies due
> to a catastrophic failure (e.g. a process failure or power failure on
> the client machine).
>
> On restarting the client and retrying the transaction we
> are finding that the original session is still active and
> still locking several resources. The session appears to
> be hung and never ends.
>
> The only way we have found to kill the original session is to shutdown
> and startup the database. (alter system kill session doesn't work.)
>
> Is this a known problem? How can we guarantee that the original
> session ends properly when the client application fails abnormally
> while the PLSQL procedure is running?
>
> Thanks,
> Jim.

If you have a client wait for "Several minutes" to return from a server call...you got more serious problems then once in a blue moon catastrophic failure. I am surprised that your users are not complaining about having to wait for "Several minutes" before proceeding with other tasks.

If you have complex business rules to satisfy on any (Insert/Update/Delete) which may take several minutes...then I would suggest you break the process in two steps. Step 1: Create a sort of holding table where users can insert their requests with all necessary parameters to run a complex process with a "Posted" flag set. Server call will return to client immediately after inserting a row...this may take only a second or so depending on your environment. At this time if there is a "catastrophic failure"...client can be safely disconnected.

Step 2: Create Oracle background process which picks up all user request/s with "Posted" flag on from the holding table at set times and processes the request...which may take several minutes or even an hour. After the process is completed..it changes the "Posted" flag to "Processed". This will happen independent of whether the user who requested the process is still connected or not.

No fuss, no hassles and most of all, happy users. -:)

HTH
//Rauf Sarwar Received on Thu Jul 04 2002 - 20:16:36 CDT