FreeTDS Development Group

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

Bhagya Prasad wrote:

As Frediano, said, what you're seeing is expected. 

> 1. Establish connection to MSSQL server via FreeTDS and make couple of
> SQL queries which succeed.
> 2. Now disconnect the MSSQL server from network and make an SQL query by
> setting the query timeout of 10 seconds

With a disconnected cable, that's expected, see below.  To get a 10-second
timeout, leave the cable attached and cancel a long-running query instead.
 
> 3. Note that application receives the response after 20 seconds (instead
> of 10 seconds)

The client and server work in tandem, "sharing state" as it's called. 
Each knows what it last did, and what it expects from the other.  

When the client sends a query to the server, it waits for a response.  It
needs a way to say "oh, never mind" when it takes too long (FSVO "long"). 
How?  It sends a CANCEL packet to the server.  

On a working network, with the server functioning correctly but slowly,
the server will quickly acknowledge the the CANCEL packet.  The query is
cancelled, and the server is ready for the next query.  Very important:
Both the server and the client *know* this, because the client received
the server's cancel-acknowledgement.  

Because both parties know the connection is OK, the API library continues
to use the same connection handle.  At the TCP/IP level, the socket is
still intact.  Communications can continue.  That's why you can press ^C
in isql to cancel your query and continue using the tool, rather than
exiting.  

That's also how completed queries are cancelled.  If the server starts
returning 1,000,000 rows, the client can send a CANCEL packet after the
first N.  The server will keep sending rows until it receives and
processes the CANCEL packet, after which it will send a final data packet
(with a bit flag indicating the results were cancelled) followed by a
cancel acknowledgement.  

On a disconnected network, the client sends the CANCEL packet and ...
waits.  The acknowledgement never arrives (perhaps because the server
never got it).  

How long should the client wait for acknowledegement?  We decided the
QUERY_TIMEOUT was the right value.  It's debatable.  It should scale with
the speed of the network and the load on the server.  On the other hand, 2
* QUERY_TIMEOUT is predictable, and if your network is that broken, you
have bigger problems than timing out.  

That's where your 20 seconds comes from: 10 for the timeout, and 10 more
waiting for the cancel-acknowledgement packet. 

When the client does not receive the cancel acknowledgement, it assumes
the server is gone.  It invalidates the connection handle and destroys the
TCP/IP socket.  The client has to reconnect to resume using the server.  

> Is there a way we can bypass cancellation?  

There is no API call to say "abandon this connection handle now".  There
are documented ways to do asynchronous querying, but FreeTDS doesn't
support them.  

HTH.  

--jkl