[QUESTION] Difference / interactions between queryTimeout and cancelQueryTimeout

[stolsvik]

I find it very hard to understand the queryTimeout vs. cancelQueryTimeout.
The documentation here https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties says "this property can be used to cancel queryTimeout set on the connection."

What does that mean? Cancelling the existing set QueryTimeout? I've tried to parse this many times, but I don't get it.

Following, we read:
"Query execution hangs and does not throw exception if TCP connection to SQL Server is silently dropped."
.. and ..
"The driver waits the total amount of cancelQueryTimeout + queryTimeout seconds, to drop the connection and close the channel."

In our case, it seems like we do have a problem with such silently dropped connections. What happens if we do set queryTimeout, but do not set the cancelQueryTimeout? What is the point of these two differing settings?

Updating the doc would be nice - and telling me here if possible!

[lilgreenbird]

hi @stolsvik,

queryTimeout is the number of seconds to wait before a timeout will occur on a query, it is the time before which the command must complete before it's interrupted. cancelQueryTimeout is the time to cancel the query. This property was introduced to fix the issue 525 where the querytimeout hangs and does not throw an exception due to silent dropping of the connection. If this is set, the driver will wait for a total amount of queryTimeout + cancelQueryTimeout then terminate the connection. Note that when using this you will need to set both these properties as cancelQueryTimeout is only applicable if queryTimeout is also set.

[lilgreenbird]

hi @stolsvik

Just checking if this answers your question? Looking at these properties in the doc looks to me they are described accurately but of course I may have a different point of view than you. Please let us know what you find to be unclear and if you could suggest a better wording? Otherwise if we don't hear back from you we will assume you are satisfied with the answer and close the issue.

[lilgreenbird]

closing due to inactivity

[timgautier]

"this property can be used to cancel queryTimeout set on the connection."

That sentence seems to imply cancelQueryTimeout is some sort of cancelation function, rather than a setting that says how long to wait for a query to be cancelled. It could definitely be worded in a less confusing way.

[amcclain]

Agree strongly with the above. I would suggest something like the below (my edits only, not provided or reviewed by the driver dev team or support):

This property can be used as an additional layer of timeout protection over and above queryTimeout. Query execution hangs and doesn't throw an exception if the TCP connection to the server is silently dropped. This property ensures that the request will still be timed out even in this dropped-connection case: the driver waits the total amount of cancelQueryTimeout + queryTimeout seconds, then will drop the connection and close the channel if it has not yet returned normally. Only applicable if 'queryTimeout' is also set on the connection.

Edit - it would also be great (and is not clear to me) if the docs could offer some guidance on what value would be a reasonable default to use for this setting. I.e. what extra time does it need to allow for? The time taken to serialize and transfer the data?

I'm inclined to set it to something like 30(s) for an application use case where we allow a 60s queryTimeout, but again it's unclear what might happen during that extra 30s in normal operation. In the case of a "silently dropped" TCP connection I'd like it to time out immediately, but I don't want to set to a value that's so short it creates spurious timeouts due to some other factor.

[Jeffery-Wasty]

Hi all,

Thank you for the feedback! We're looking to do a doc update in the next few weeks, and plan on including greater clarification about timeouts.