FreeTDS Development Group

["Pickett, David" <David.Pickett AT phlx.com>]

  -- A suggestion for reasonable connection pooling behavior --

The spirit of pooled connections is to conserve resources and elapsed time
by replacing disconnect and connect with pool issue and return.  Issuing a
bad connection to a requestor seems a bit nasty, as getting a connection
from a pool is supposed to be equivalent to connecting, i.e., your implicit
API contract is that you get an error or a good connection.

Deferring connection validation creates several bad situations.  First,
through no fault of your own, you may get a stale connection and fail,
although your code is good and the server is up.  Second, a connection error
later confuses diagnosis.  Third, if you want to suppress errors from
accidentally issued stale connections, life is complicated:  You'd need to
intercept error handling for all operations.  If an error was a 'bad
connection' error on the 'first operation since issue' on a stale
connection, you must silently attempt to reconnect this connection object,
even if there is another, good connection object in the pool, since pointers
to this object have been issued.  Even if there is some sort of timer to
know that the connection is 'unused since issue' and 'stale', this strategy
is a burden on all operations, a waste of resources worse than doing
something at the less frequent 'connection issue' level.

Both some sort of TDS level heartbeat and a check every time before issuing
from the pool are an inappropriate, gratuitous waste of resources.  However,
if an issue time check was conditional on some elapsed time since last use,
the cost only occurs infrequently, before peak activity when coming out of a
low activity period, where a little longer issue time is not a detriment.
The elapsed time should be shorter than any conceivable server timeout,
maybe defaulting to just two (2) seconds (actually 1+ to 3- seconds), so it
is logically simple (t1 - t2 >= 2), overhead disappears when things get
busy, and does not need to be configured to match the server side.

I an ignorant of some details.  Does 'connection return to pool' implicitly
exchange messages?  In similar code, I used to make sure there was no state
left, like open cursors or transactions.  To minimize resource usage, you
must trust you have well behaved callers.  Keeping the UNIX second of the
'last message in' would be relatively cheap, if not already available.  With
it, at issue time you can detect connections that are stale and check them.
The check should be a simple status check, something valid, innocuous and
really low in cost that does not change the state of the connection.  On
issue, the first pass through the pool should look for 'not stale' open
connections.  The second pass would test each open stale one, and if dead,
mark it disconnected and continue.  If all stale are dead, connect to fill
the first disconnected slot.  This minimizes the count of open connections,
and as a bonus, improves both sides' locality of reference by using the more
recently active connections first.

Best regards,

David

-----Original Message-----
From: James K. Lowden [mailto:jklowden AT freetds.org]
Sent: Saturday, November 26, 2005 11:23 AM
To: FreeTDS Development Group
Subject: Re: [freetds] Connection life


martin A. wrote:
> Hello, I have a question about connection and disconnection. How can I 
> detect if a network connection is dead using db-lib? I have seen that
> there  is a function called  dbdead, but this function does not check 
> if the  network socket is alive ( I use  MS Sql Server), it never
> connects to the  server... 

The DBDEAD() function simply checks the status of the connection as
maintained by lower levels.  When the connection is dead, the library
marks it so.  As in so many things, it's easy to know if something's dead;
it's much harder to know if it will be dead soon.  

TCP has no heartbeat mechanism that I know of; TDS definitely doesn't. 
For that reason, idle connections are strictly idle: no packets are
exchanged.  If the remote end dies unexpectedly -- say, the machine loses
power -- the remote OS has no opportunity to tear down the connection, and
the local OS never receives a packet indicating the connection should be
closed.  The local status will remain "open" until a write fails or a read
times out.  

> I need this because I have a connection pooling and I havte to 
> detect connections dead to reconnect if it is needed, I keep the
> connection  without calling a dbclose for a long time. But if the dbdead
> does not check  the socket the result of calling dbdead is not reliable.
> How can I solve this problem?

The correct solution IMHO is not available to you yet.  It is described by
the db-lib docs, but not correctly implemented in FreeTDS.  

What you should be able to do is set a short timeout, say 5 seconds, and
an error handler that understands what's happening.  You issue your query.
 For easy queries, the server responds quickly, no problem.  For longer
ones, the error handler knows to wait, and tells the library to retry. 
When the error handler loses patience, it can tell the library to cancel
the operation, returning an error to the caller of dbnextrow() or
whatever.  

AFAIK that's been the standard answer for 20 years.  We're almost there.  

Regards, 

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds