Re: Sybase not closing connection when scripts is terminated

Lenga, Yair wrote:
> Hi,
>  
> I retested with different keepalive setting. I was able to replicate the 
> problem with Sybase 12.5.0.1, but not on 12.5.1.0, using the following 
> simple ISQL based script:
>  
> isql ...
> -- some_table has ~1000 rows, 10 columns
> select * from some_table
> select * from some_table
> select * from some_table
> select * from some_table
> select * from some_table
> go
>  
> While the script is running, I hit Ctrl/Z, then I used "kill" to kill 
> the process. With 12.5.0.1, sp_who is reporting the process to be in 
> SELECT mode. netstat/lsof (on the server) show terminated connection 
> (port number is '*'). Netstat/lsof on the client shows nothing. The 
> server does not properly clean the connection, even after 4 hours (with 
> keepalive at 2 hours). With 12.5.1.0, the server canceled the connection 
> the process get killed, and remove any blocks associated with the 
> connection. The 12.5.1.0 is configured with keepalive at 2 hours.
>  
> Does anyone know if a specific fix to 12.5.0.* or 12.5.1.* that explain 
> this behavior ?

Both 12.5.0.x and 12.5.1 are quite old by now, and I wouldn't be 
surprised if there was something in them that could cause this sort of 
behavior.

You should probably check with Sybase - either via a tech support case, 
or maybe via the newsgroups at forums.sybase.com

Michael



>  
> Thank
> Yair
>  
> 
> ------------------------------------------------------------------------
> *From:* [email protected] 
> [mailto:[email protected]]
> *Sent:* Thursday, August 23, 2007 8:15 AM
> *To:* Lenga, Yair [CMB-FICC]
> *Cc:* [email protected]
> *Subject:* Re: Sybase not closing connection when scripts is terminated
> 
> 
> The most likely problem is the setting of the tcp keep_alive kernel 
> parameter. This parameter controls how much time is required before the 
> OS realizes that a connection is "dead" when one of the parties has 
> disapeared.
> 
> Under linux this is controled via the /etc/sysctl.conf file, for example:
> 
> # Keepalive settings for Sybase.
> net.ipv4.tcp_keepalive_time = 600
> net.ipv4.tcp_keepalive_intvl = 10
> net.ipv4.tcp_keepalive_probes = 5
> 
> The default values for these parameters usually specify a timeout of 
> several days... You should check with the system administrator of your 
> system for details regarding how to configure this.
> 
> Michael
> 
> 
> 
> 
> 
> *I*nternet
> [email protected]@peppler.org - 23.08.2007 14:04
>  
> 
> Sent by:        [email protected]
> 
> To:        sybperl-l
> 
> cc:        
> 
> Subject:        *Sybase not closing connection when scripts is terminated*
> 
> Good Morning,
> 
> I using DBI-1.34/DBD-Sybase-0.95 to access Sybase servers, using Sybase
> client 11.5.1, regular CGI/command line scripts (No Mod Perl, etc.).
> Running against ASE 12.5.0.1
> 
> I'm trying to understand a problem that occur very infrequently (once
> every 2 month, on a very busy server). Each time the problem occur on
> different script, but the pattern is the same:
> - Script get launched.
> - Issuing a query that result in large results set - Read Only
> - Script is aborted with Signal (or Ctrl/C).
> - Script is terminated.
> - Sybase Server does not detect that the script is not running any more
> - READ ONLY locks remain from the query.
> - Another scripts is running 12-24 hours later - trying to update/insert
> - get locked forever.
> 
> At the time of the problem, Sybase is showing open connection (sp_who)
> to the process. We verified that the process is not running on the
> server (using ps), and does not have any forked children that may have
> kept the connection open.
> 
> I suspect that the core problem is that Sybase server is unable to
> detect closed connection, when it is in the middle of sending the result
> set to the client, but I could not replicate the problem.
> 
> Any help with the following questions will be appreciated:
> - Does anyone know/experience similar problem - and know the source ?
> - Is there any setting that can be used to say "Abort transaction if it
> can not be executed in <X> seconds" ?
> - Any way to force Sybase to actively check if the client is alive every
> <X> minutes.
> 
> 
> Thanks
> 
> Yair Lenga
> 
>  


-- 
Michael Peppler           -                 Peppler Consulting SaRL
[email protected]      -                  http://www.peppler.org
Sybase DBA/Developer      -   TeamSybase: http://www.teamsybase.com
Sybase on Linux FAQ       -   http://www.peppler.org/FAQ/linux.html