New for CF9 (and 9.0.1): a query timeout that may really work, with a caveat
Note: This blog post is from 2010. Some content, links and indeed comments from others may be outdated--though not necessarily. Corrections are welcome, in the comments. I may revise the content if necessary.This is a very interesting change in CF9 (and 9.0.1), which has slipped under the radar for the most part it seems.
Did you know there is now a setting in the DSN page of the CF Admin (for most of the Adobe-provided DB drivers) which allows you to set a maximum timeout for queries against that DSN?
It's a new feature enabled for the DataDirect drivers, as updated in CF 9. (You will not see it if you use an "other" datasource type, such as when using a downloaded JDBC driver that you implement on CF.)
The caveat? This timeout is ONLY settable there in the DSN definition, not in CFQUERY (or CFSTOREDPROC) itself, which is a shame. The existing TIMEOUT attribute for those (CF10 added it for CFSTOREDPROC) is not the same and generally does not work. Still, the value of this even at the DSN level is too important to ignore for some challenges. More on that (and some other thoughts) in a moment.
As for the setting, it's in the "Advanced Settings" section for a DSN in the CF Admin for CF 9 (or greater), and it's called "Query Timeout". This should not to be confused with the older settings, "Timeout" (which is about inactive connections) or "Login Timeout" (which is about logging in to the connection). The screenshot at right shows all 3. (This blog entry continues, with more information below it.)
I've run a test (more below), and it really does do the job (at least in that testing), which is huge.
Why us this an important development?
It's been a long-time issue that if a CFQUERY got hung up waiting for a response, that request thread (doing the CFQUERY) is then hung until the query finishes, which can sometimes be many minutes, or even hours or days, due to some odd situations. More important, a thread waiting for a query with no timeout can't be terminated (by the JVM, or CF, or the monitoring tools) because the thread was in a native thread state.
With this new option specified, if the request exceeds the timeout, the request does now fail, with a JDBC error, "Execution timeout expired." The same test does NOT timeout with the older cfquery TIMEOUT attribute.
(If you want some code to use to test a request waiting a long time for the DB to return, most databases offer a statement that tells the DB to wait for some time. In SQL Server, that's WAITFOR DELAY 'hrs:mins:secs'. Just use that in a CFQUERY, assuming your DSN definition doesn't limit what SQL statements you can use, in the 'Allowed SQL' section of the Advanced Settings page.)
If you're suffering situations where requests are hanging due to long-running queries, and you have not been able to solve the real root cause for why they are hanging (which I always recommend first and foremost), then at least this option can help avoid a situation where queries can run without time limit. An Admin can decide that no queries should be allowed to run more than x seconds.
Beware impacting all queries
With this power comes responsibility, of course, and caution. You wouldn't want to preclude someone being able to run a query that really needed to take a long time. That's why it's really better if this was settable on a per-query basis. (And no, the CF page timeout settings are NOT the solution here, because again as I said above, they cannot timeout some kinds of long-running tags, like CFQUERY, CFHTTP, CFINVOKE of a web service, etc.)
What one could do, though (for now), is create different DSNs, where one could be used for most query processing, and another could be used for long-running requests. Yes, it's ok to have 2 DSNs point to the same DB. This same technique has been used when wanting to have most queries run against one DSN with a limited set of "allowed SQL" (per the DSN advanced settings) while another DSN has unfettered SQL access.
More to consider about the new setting
Here are some other notes on the new feature:
- It works with most of the database drivers. I have confirmed that the setting appears in the DSN settings page for SQL Server, Oracle, MySQL (datadirect), DB2, Informix, Sybase) in CF 9 Enterprise, and in both SQL Server and MySQL (Datadirect) on CF 9 Standard, for example. It will not be there if you choose "other" for the datasource type, to point to some other JDBC driver.
- The timeout's specified in seconds.
- You can learn more in the CF Admin guide, in this specific page.
- Oddly, the Admin manual page above only references this new setting in that CF Admin manual is in the the MySQL settings, but again it does appear in all the drivers above.
- The manual page does even reference the other DBMSs by name in its naming the methods of the Admin API (for other DBMSs) which you can use as well, which can be used to set this default setting in the DSN programmatically.
- That said, again there is sadly no new QueryTimeout (or QTimeout) attribute for CFQUERY, so for now we can only set this at the datasource level, not per query.
- If you look under the covers (in [cf]\lib\neo-datasource.xml), there is in fact a querytimeout connectionstring that this setting controls. If only there was a way we could pass connectionstring values to the CFQUERY, we'd be golden. Some may recall use used to have just such an attribute (ConnectString), but sadly it was deprecated in CF 6. I did try it, to no avail.
will raisehave raised a bug with Adobe to get a new attribute for CFQUERY related to this. When I do that, I'll report it here.It's bug 83592. Please add your vote of support for it.
- There's another page on this Admin setting, in the CF Dev Guide, for those who may be interested in following any other possible places where this feature may be discussed (in the comments there).
I think this is a very important setting, and though it has been a hidden gem, it seems, it's one that people should consider. That's why I've flagged this in my CF911 category.
Hope this is helpful to some. Let me know what you think, whether this was helpful or if you feel I left something out. Especially please let me know if you may know of a way that we can indeed pass querystring values on a per-query basis.
For more like this: