Re: Oracle lock timeouts

In article <23m3vk$ijg_at_pandora.sdsu.edu> oliver_at_io.nosc.mil (George Oliver) writes:

>Charlie Toor (hfraser_at_dqc.dofasco.ca) wrote:
>: 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?

>I have had the same problem with a Macintosh running SQLForms connecting to
>a Sun SS2. Oracle support (oxymoron?) said the only way to deal with this is
>to kill the process on the Sun that was created by your Macintosh connection
>which crashed. I'm afraid I have no better solution than that. I am not
>familiar with setting lock durations.

The problem as I understand it is that SQL*Net TCP/IP does use the "Keep Alive" protocol. If it did then TCP/IP connections would time out. We have been battling Oracle Support for over a year to get this fixed.

The work around of killing the shadow process works only if you can identify the process to kill. In version 6 you could not make the association between an Oracle PID and a System PID; I don't know if it is possible with V7. I have been shutting down the DB each night to clean up aborted processes.

There is hope. I believe that FTP Software has a TCP/IP for PC's that will keep track of all connections that it makes an clean up any aborted sessions after a powerfail, three finger salute, etc. Of course, FTP TCP/IP is a little pricy. Oh well, you get what you pay for.

Hope this helps.  

...............................................................................

Mike Dayton                                     | AT&T / NCR 
Software Services Automation                    | 2700 Snelling Avenue North
email: Mike.Dayton_at_StPaul.NCR.COM               | St. Paul, MN. 55113