|
|
sybperl-l Archive
Up Prev Next
From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Sybase not closing connection when scripts is terminated
Date: Aug 24 2007 3:39PM
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:* michael.peppler@bnpparibas.com
> [mailto:michael.peppler@bnpparibas.com]
> *Sent:* Thursday, August 23, 2007 8:15 AM
> *To:* Lenga, Yair [CMB-FICC]
> *Cc:* sybperl-l@peppler.org
> *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
> yair.lenga@citi.com@peppler.org - 23.08.2007 14:04
>
>
> Sent by: owner-sybperl-l@peppler.org
>
> 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 seconds" ?
> - Any way to force Sybase to actively check if the client is alive every
> minutes.
>
>
> Thanks
>
> Yair Lenga
>
>
--
Michael Peppler - Peppler Consulting SaRL
mpeppler@peppler.org - http://www.peppler.org
Sybase DBA/Developer - TeamSybase: http://www.teamsybase.com
Sybase on Linux FAQ - http://www.peppler.org/FAQ/linux.html
|
