FreeTDS Development Group

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

The tdspool server should respond to a disconnected client (usually a poll()
or select() inspired read() = EOF on the socket fd) by 'cleaning up' the
corresponding DB connection, closing it if cleanup fails (and reopening
either on speculation or on demand, according to whatever policy you prefer.
Current activity like cursors on the connection should be cancelled, and all
open transactions rolled back, clearing locks.  (I seem to recall using
something like 'while trancount > 0 do rollback' in a similar, Sybase Tuxedo
server situation.)

Closing every time a client disappears could be very expensive if a flawed
but popular query is deployed, especially if the server does not share
itself well during close and open DB, holding locks or hogging a critical
thread.  This part of a server often is not the focus of fastidious coding,
justifiably since these are supposed to be infrequent activities.  The
activity increase on the db server is not free, either.

It's also be good for the tdspoll server to 'time out' idle incoming client
connections, so protocol violation lockups are detected, the terminal state
of the dialog is logged, and the incoming connection is closed (and maybe
you do, I am just including good practice while in the vicinity).  If you
want to be really forgiving, you can only time out when all pool connections
are in use, but then you are being silently inconsistent, may contribute to
exhausting any DB server connection limit and are hindering debugging.

The tdspool client side should also respond to a disconnected or
unresponsive server by closing the socket and returning appropriate
notification to the caller.  A well behaved caller will then decide if it
will exit, attempt a few reconnects, pass the error up the call chain or in
a response to a next layer client like a web browser.

Best regards,

David

-----Original Message-----
From: James K. Lowden [mailto:jklowden AT freetds.org]
Sent: Tuesday, December 13, 2005 10:03 PM
To: FreeTDS Development Group
Subject: Re: [freetds] tdspool lock handling


cui yanxin wrote:
> I am using latest freetds freetds-0.64.dev.20051213, it seems tdspool
> does  not handle lock very well.
> 
> 
> Test case 1:
> 'tdspool mypool' is running, 2 applications connecting to sql server 7 
> throught odbc using the pool.
> step1:Application A: using one connection, doing "select * from
> employees  with(updlock)", before doing commit, waiting key press...
> step 2:Application B: using one connection, doing "select * from
> employees  with(updlock)"
> The normal behavior for Application B should be waiting for locks being 
> unlocked from application A, but application B retrieves the records!!!
> step 3: run tsql>select * from employees with(updlock);
> tsql behaves correctly, it waits for the lock being released from 
> application A.
> step 4: exit application A and B, they both disconnects and frees
> handles. tsql select statement still does not return!!!
> step 5: kill tdspool
> tsql select statement returns back.
... 
> It seem tdspool is not handling locks properly? Should I avoid doing
> 'select  ... with(updlock)' and 'update' if I use tdspool?

tdspool doesn't know anything about locks.  It knows about connections,
not SQL. It's just maintaining proxy connections for you so you don't have
to wait for a login and don't have to maintain a connection.  It sounds
like you should be maintaining connections and suffer the login delay.  

When you exit the application (step 4), the locks stay in place, because
tdspool remains connected to the server, and the server maintains the
locks on behalf of the connection.  If the client is well behaved, it
releases its resources before abandoning the connection (cancelling the
query would work).  But if it simply disconnects, how is tdspool to know
what to do?  

By killing tdspool, you close the connections and the server frees the
resources associated with them.  

Actually, I see a solution, at a cost.  tdspool could disconnect and
reconnect everytime a client disconnects.  The connection would be
temporarily removed from the pool; new incoming requests would be routed
to an already-connected connection.  This would have the added benefit of
cleaning up temporary tables and the like.  The disadvantage is that the
server would be burdened with accepting a new connection for every inbound
request; today, tdspool forms all its connections at startup, once.  

A different approach would be to have tdspool respond intelligently to to
SIGHUP, recycling unused connections.  That might be a nice administrative
knob, but it's hard to see how it would make for robust applications.  

A middle road would be to have tdspool inspect the state of the
connection, and recycle it only if it was in a querying state.  But that
wouldn't help with temporary tables or open transactions.  

But I have to tell you, the pool server is a bit of a step-child as far as
I'm concerned.  It's a kludge, and real applications don't use proxies.  I
don't expect to find myself working on it anytime soon.  

HTH.

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