Re: Oracle lock timeouts

In article <1993Aug3.093818.3498_at_eisgen'.dofasco.ca>, hfraser_at_dqc.dofasco.ca (Charlie Toor) writes:
> We are running Oracle 7 on an HP/UX box with Powerbuilder as a PC client
> tool.
> Occasionally, a PC will crash (?) or otherwise do an unclean disconnect from
> the
> database (connect via TCP/IP to the PC clients), often leaving records locked.
> It
> appears that somewhere in the Oracle<->SQL server<->HP TCP/IP<->FTP
> Software<->Powerbuilder string somethingof packages, Oracle fails to
> recognize
> the connection has been dropped and doesn't release the record locks. It's
> been
> suggested that we put 30 minute timeouts on the locks, but that seems to be a
> crude solution and in any event is quite intolerable for production
> environments.
> Does anybody have any other suggestions?

This is a very common problem in a Client(PC)-Server environment. I asked a number of questions a while back on this and other networking and security issues
a while back and hardly got any thing. We had to work it out ourself BUT I do have an answer for you.

The problem SHOULD be addressed by Oracle's networking guys/gals. What should be done is for the Server and/or Client network process (ie SQL*Net) to send a heart beat every so often (TIME1) and if a reply is not obtained within a certain time frame (TIME2), it should then close the connection and release any resource it currently has. TIME1 & TIME2 should be configurable.

That is what we would like but is not available. What the Networking people have done is to rely on TCP's KEEPALIVE . NOTE THIS IS NOT THE SAME AS SQL*Net's
timeout option. That is used for timing out on CONNECTION.

What I am referring to is a feature of TCP that will timeout after certain time if
there are not packets from the remote process (IP). It has a clock that starts counting down from a default time interval. This is 7200 seconds ie 2hours. It starts counting down from 2 hours and time out if it gets to 0. When it gets a packet the counter is reset back to 2 hours. If you wait long enough, it should terminate and release any resources.

You may change this value. The file and parameter that you have to change is different between machines and Op/Sys. What I am about to specify is from memory so please check it out with your UNIX or networking guys. On SUN OS 4.1.2/3 the file is
 /usr/kvm/sys/netinet/tcp_timer.h
and the parameter is something like TCP_KEEPALIVE?????.

Don't make it too short an interval as it might affect other TCP daemon. Hope that helps.

If there are any one out there that has a different approach to this problem or disagree with the above, please either reply to me directly or to the newsgroup.
Would really love to hear from Oracle Networking developers/designer and those using SQL*Net V2.

ta
tony

-- 
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  _        /_ __ /       |Colonial Mutual Invest Mgmt,Aust (ACN004021809)
 /(_)/ )(_/ \_/(///(/_)/_(   |EMAIL:  TJambu_at_cmutual.com.au
 \_______/                   |PHONE:  +61-3-6418448       FAX:  +61-3-6076198