The other day, I was trying to track down inconsistent behavior in some ColdFusion code. Sometimes it would run properly; other times it would fail. At first, the issue wasn't entirely obvious. But, after digging into the workflow, I realized that it was a transaction race-condition in which an asynchronous task was being triggered - and sometimes executing - before the transaction completed. The problem with this was that if the asynchronous task ran too quickly, it wouldn't have access to the uncommitted record inside the transaction.
To see what I mean, take a look at this simplified example. We're going to insert a data table record; then, from within the "bounds" of the CFTransaction, we're going to read that data using both synchronous and asynchronous methods:
<!--- Reset out data table so we know that we're only dealing with one record. ---> <cfquery name="reset" datasource="testing"> TRUNCATE TABLE friend; </cfquery> <cftransaction action="begin"> <!--- Insert our test record. ---> <cfquery name="insert" datasource="testing"> INSERT INTO friend ( id, name ) VALUES ( <cfqueryparam value="1" cfsqltype="cf_sql_integer" />, <cfqueryparam value="Tricia" cfsqltype="cf_sql_varchar" /> ); </cfquery> <!--- At this point, we're still inside the Transaction. ---> <cfquery name="preThread" datasource="testing"> SELECT id, name FROM friend ; </cfquery> <cfthread name="async"> <!--- At this point, we're no longer inside the Transaction. And, we can't read the record that has not yet been committed. ---> <cfquery name="thread.friends" datasource="testing"> SELECT id, name FROM friend ; </cfquery> </cfthread> <!--- Make sure the thread finishes executing before the transaction is closed. ---> <cfthread action="join" /> </cftransaction> <!--- Output the data we got mid-transaction, but outside the CFThread. ---> <cfdump var="#preThread#" label="Pre CFThread" /> <!--- Output the data we got mid-transaction, inside the CFThread. ---> <cfdump var="#cfthread#" label="CFTHread" />
In this case, I'm using the CFThread[action=join] to make sure that the CFThread is actually executed (and not simply queued) before the close (and commit) of the CFTransaction tag. When we run this code, we get the following CFDump output:
As you can see, while both SELECT queries ran mid-transaction, only the synchronous one was able to read the uncommitted data; the one in the CFThread came back empty.
This is a documented behavior of ColdFusion:
The cftransaction tag does not work as expected if you use the cfthread tag in it to make query calls.
The problem I had, in the code I was debugging, was that the CFThread tag was actually like 2 method calls deep. So it wasn't immediately apparent that one of the method calls was spawning a thread. The bigger philosophical problem, however, was that too much was happening inside the transaction. Transactions should be kept nice and lean.