When performing a series of update or insert queries, you will most likely want to wrap the code with a <cftransaction> tag to have the entire set roll back in the event of an error. This is probably standard practice for most of us. The cftransaction tag has other uses as well. By setting the "isolation" attribute, it can also be used to limit access to certain data, similar to using a cflock tag. There are a few key differences between using <cflock> and <cftransaction>, though. For one, the cftransaction method allows the database to handle the locking, maintaining data integrity across applications. Another important difference is that the cflock method forces the second call to wait for the first call to finish, whereas the cftransaction method allows the second call to complete normally, but using the data as it appeared before the transaction began.

Let me explain.

Assume we keep a quick lookup table of product categories sorted by popularity, using an adjacency list model to store the hierarchical relationships. A periodic process reorders the categories based on recent product views or sales. And because the call to determine product popularity has to call out to a legacy ERP system, it could take several seconds for the reorder process to complete.

Before the reorder process runs, the data looks like this:

- Kitchen Appliances
– Ovens
– Refrigerators
– Dishwashers

A recent sale on refrigerators has moved them to the top of the list, so after the reorder process, the data would look like this:

- Kitchen Appliances
– Refrigerators
– Ovens
– Dishwashers

If the periodic process were wrapped in a <cftransaction> tag, any read queries against the quick lookup table while the reorder was in progress would return the following data:

- Kitchen Appliances
– Ovens
– Refrigierators
– Dishwashers

The default isolation mode of most common databases prevents reads of uncommitted data, which means a cflock is usually not necessary for simple operations such as this example. If other processes might attempt to update the lookup table at the same time, a higher isolation level might be necessary for the second process, but a cflock should still not be required, unless you also need to control access to the CFML code.

Something to keep in mind, though, is that a TRUNCATE TABLE call inside of the transaction will skew the results, causing external queries to return an empty recordset, instead of the normal pre-transaction snapshot. However, using a DELETE statement with no WHERE clause has the same effect of removing all rows from the table, but it does not interfere with the transaction, allowing external queries to retrieve the pre-transaction data.

The TRUNCATE TABLE behavior described above was seen with MySQL 5.1.42. Other database servers, or versions of MySQL, may behave differently.