sybperl-l Archive

From: "Lenga, Yair " <yair dot lenga at citi dot com>
Subject: RE: Sybase not closing connection when scripts is terminated
Date: Aug 24 2007 2:08PM

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 ?
 
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 





Internet 
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