Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at the New York ColdFusion User Group (May. 2009) with: Abraham Lloyd and Peter Bell and Gert Franz and Mark Drew
Ben Nadel at the New York ColdFusion User Group (May. 2009) with: Abraham Lloyd@abrahamlloyd ) , Peter Bell@peterbell ) , Gert Franz@gert_railo ) , and Mark Drew@markdrew )

The CFQuery Tag Supports Silent Asynchronous Query Execution In Lucee 5.3.2.77

By Ben Nadel on
Tags: ColdFusion

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.



Reader Comments

@Zac,

I am not sure if it is a bug? I say this because it seems that any error that is thrown inside a Future that 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 how Futures are implemented.

So, making a leap that this feature may be feature ontop of the Future or runAsync() 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.

Reply to this Comment

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!

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.