Best Practice for Application Tier Database Transactions

Question

I have seen a number of different cftransaction examples and read different sites and still have not been able to find a definitive answer to what parts of cftransaction are necessary. What I am trying to accomplish is very simple:

• start a transaction
• run multiple inserts/updates
• close the transaction

If there is an error at any time during the inserts/updates, I want to rollback the transaction. Here is what I have done in the past:

<cftransaction>
 <cftry>
  <!--- multiple insert/update queries --->
 <cfcatch type="any">
  <cftransaction action="rollback">
  <!--- log error, show user message --->
 </cfcatch>
 </cftry>
<cftransaction action="commit">
</cftransaction>

Is this correct? What is the best practice? Not all of the instances of cftransaction are following the example above. Some only have the beginning and ending tags.

I have been seeing some issue with my database server and think that improper use of transactions could be one of the issues. I'm seeing a large number of: set transaction isolation level read committed processes in the activity monitor that are taking up a large amount of CPU.

Answer 1

Two notes:

I think you want to place the cftry block around the cftransaction. Otherwise, it will roll back the transaction, then try to commit it.

Second, I believe that if queries do not return errors, closing the transaction implies that you wish to commit. The commit line is not wrong, just redundant. If you want the automatic checking to kick in, omit it.

Answer 2

As you're doing it, the "commit" and "rollback" lines are redundant. They're really only useful if you want some of the queries committed and others rolled back. Since you're doing all or none you really don't need them.

I generally do this:

<cftry>
  <cftransaction>
    <!--- multiple insert/update queries --->
  </cftransaction>
  <cfcatch type="database">
    <!--- log error, show user message --->
  </cfcatch>
</cftry>