The CFQuery Tag Supports Silent Asynchronous Query Execution In Lucee 5.3.2.77
As I've been digging through the Lucee CFML 5.3.2.77 documentation, one feature that I came across was the ability to execute SQL queries asynchronously with the async attribute of the cfquery tag (and the corresponding queryExecute() function). The Lucee documentation doesn't really tell you how this cfquery attribute works - or what side-effects it has; so, I wanted to take a quick look at it myself.
When you add the async attribute to the cfquery tag or the queryExecute() function, there is no response or result value. At least, not that I could find. It doesn't even appear to alter the cfthread scope. So, as far as I can tell, adding async to the SQL execution kicks the query into true "set it and forget it" mode.
To see this in action, we can create a SQL query with an artificial SLEEP() command and track how long the page takes to run:
<cfscript>
startedAt = getTickCount();
// NOTE: By setting "async:true" on the query configuration, there is no return value
// from this function. The page does not block on this query. It simply triggers the
// query and continues on with the page processing.
// --
// CAUTION: Errors thrown in this query DO NOT SHOW UP IN THE APPLICATION LOGS.
queryExecute(
"
SELECT SLEEP( 3 ); -- Slow down the query, sleep for 3-seconds.
DELETE
t.*
FROM
ben_test t
;
",
nullValue(),
{
datasource: "testing",
async: true // <=== Causes the query to run asynchronously.
}
);
echo( "Query initiated, #numberFormat( getTickCount() - startedAt )#ms." );
</cfscript>
As you can see, the SQL query is going to sleep for 3-seconds before it even executes the DELETE operation. However, when we run this Lucee CFML code, we get the following page output:
Query initiated, 1ms.
Clearly, the query is executing asynchronously and the parent page is cruising right passed what would normally be a blocking operation.
Now, as I was testing this async feature, one thing that I noticed was that errors don't seem to show up anywhere. They don't get caught by the Application.cfc's onError() event-handler. And, they don't seem to get logged to the ColdFusion exception log. They just disappear into the ether.
Because of this error-handling behavior, I would be hesitant to use the async attribute for any SQL query that has critical business value. Meaning, I would only use it in cases where a SQL exception (such as a deadlock timeout) wouldn't really matter all that much (possibly because it would be implicitly handled in a subsequent idempotent operation).
The good news is, we already have a number of ways to execute asynchronous code in Lucee ColdFusion with more explicit control. If we really want to run a SQL query using a "set it and forget it" mentality, we can just wrap it in a runAsync() call and add a little error-handling:
<cfscript>
/**
* I execute the given query asynchronously, catching and logging errors.
*
* @sql I am the SQL statement to execute.
* @queryParameters I am the set of prepared-statement parameters.
* @querySettings I am the cfquery tag configuration.
*/
public void function queryExecuteAsync(
required string sql,
struct queryParameters,
struct querySettings
) {
// Execute the query outside of the main request thread.
runAsync(
() => {
try {
queryExecute( sql, queryParameters, querySettings );
} catch ( any error ) {
systemOutput( error, true, true );
}
}
);
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
startedAt = getTickCount();
// This will run the query asynchronously in a "set it and forget it" fashion. But,
// at least it will catch and log errors under the hood.
queryExecuteAsync(
"
SELECT SLEEP( 3 ); -- Slow down the query, sleep for 3-seconds.
DELETE
t.*
FROM
ben_test t
WHERE
t.id = SOME_INVALID_FUNCTION()
;
",
nullValue(),
{
datasource: "testing"
}
);
echo( "Query initiated, #numberFormat( getTickCount() - startedAt )#ms." );
</cfscript>
In this version of the code, we're achieving the same outcome - running the SQL query asynchronously. But, we're doing it in such a way that will trap and log database errors to the ColdFusion error log. Now, if we run the above Lucee ColdFusion code, we get the following page output:
Query initiated, 0ms.
We still get asynchronous execution of the SQL query. The difference is that, this time, the error thrown by the SOME_INVALID_FUNCTION() call will actually get logged to the exception log:
lucee.runtime.exp.DatabaseException: FUNCTION invisionapp.SOME_INVALID_FUNCTION does not exist
In general, I am loving the move towards asynchronous, non-blocking operations in the ColdFusion world. And, it's nice that the cfquery tag and queryExecute() function have a way to kick-in asynchronous control-flow with no effort. But, I'd probably limit the usage of this particular async feature to a very specific subset of queries - ones in which a given failure is not a critical business failure.
Want to use code from this post? Check out the license.
Reader Comments
Can you file a bug about the error handling?
@Zac,
I am not sure if it is a bug? I say this because it seems that any error that is thrown inside a
Futurethat is not explicitly handled in the code will also disappear into the ether. After looking at some Stack Overflow threads on a related topic, people were arguing that this is be design -- that it "the point" of howFuturesare implemented.So, making a leap that this feature may be feature ontop of the
FutureorrunAsync()concept, it would follow that errors are not logged.That said, I'm a relative nOOb in the Lucee world :D So, I'm more than happy to hear other opinions on the matter.
Ugg, half of that came out as non-English :D Not enough caffeine yet.
IMHO errors should always be logged, no excuses!
Lucee being open source, I had a quick look at the commit for that feature and there was some changes to the error handling, so it definitely was a consideration.
The general rule from the Lucee devs is file bugs, or they don't exist.
That said, it's good to ask for advice before filing!
@Zac,
I can dig it. I know that I've been a bit confused about how to file bugs in Lucee in the past. I vaguely remember hitting some sort of login-wall in the past where I had to have a JIRA account or something to file a bug. Will take a closer look when I have a moment.
Thanks for doing this digging in the Lucee release! I didn't know about the the runAsync function, I can already some application for it.
@Frédéric,
Very cool -- I'm glad to be able to shed some light. I feel like a kid in a candy store, switching over to Lucee :D
Nice stuff, enjoying this new batch of lucee goodness... since you started your cfml journey until now, always informative interesting angles to cfml.
Keep up the great work bro.
@Dawesi,
Awesome, I'm glad to hear it. I've been using ColdFusion consistently for the last decade; but, only recently just switched to Lucee, so suddenly lots of new stuff to investigate.