FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

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