Re: "For Update no Wait" and Database Sessions.

"termoPilucco" <termop3141927_at_hotmail.com> wrote in message news:<bpi8om$1ovvp6$1_at_ID-188026.news.uni-berlin.de>...
> Hello,
> I have (i think) a trouble with Oracle lock/sessions:
> Normally I use FOR UPDATE NOWAIT to acquire a lock on rows.
> I've simulated client crash without problem (the rows was correctly
> unlocked).
> The problem come when, after locked some rows, i have unplugged out the
> client net cable.
> This results in a Database Session alive in Oracle that lock my rows for
> unspecified time; and this session rest alive also after reconnecting client
> cable and terminating the application who generated the conection.
>
> I use Tcp/ip protocol for the connections between machines and all my
> queries are under transaction.
>
> I suppose Oracle knows when the client is disconnected (tcp connection
> results in time out), so i'm interrogating about this restless session.
> Actually i have to "disconnect immediate" manually and i'd like to avoid
> this workaround.
>
> Another question: during my tests, it seems that, casually, in a token ring
> environnement the connection between Client and Oracle server get lost and
> resulting in above behaviour. Any experiences about?
>
> thanks and sorry for my dreadful english
> <x:termoPilucco>

Termo, what you are talking about is dead session detection. Oracle session background processes that no longer have a foreground process.  There is a sqlnet [net8, Oranet] parameter that may be of interest to detect dead sessions: SQLNET.EXPIRE_TIME which "Determines time interval to send a probe to verify the session is alive " (Net8 Admin). Warning - this parameter may not suitable in an environment where you have programs that connect to Oracle and maintain the connection that can go into a wait state while waiting on an external signal such as a mailbox in VMS. The probe to see if the session is still alive will not cause the program to be become active and the Oracle background will be killed.

You can also write scripts to join v$transaction to v$session that finds transactions whose matching session has a status of inactive and that have a large value in last_call_et (time of last command). Rows returned from such a query would be candidates for being dead sessions.

HTH -- Mark D Powell -- Received on Thu Nov 20 2003 - 08:55:45 CST