Using the cftransaction Tag

Using the <cftransaction> Tag

In spite of handling errors at all layers, the bug might still appear at the database level. As a result, the database might crash or the query may not execute. The <cftransaction> tag protects database integrity and adds a final layer of error handling. This tag brings your database to its original condition if an operation fails. <cftransaction> follows the same principle as SQL or Oracle's transaction command. The syntax for this tag is

 <CFTRANSACTION ACTION="begin or commit or rollback"     ISOLATION ="read_uncommitted or read_committed or repeatable_read"> </CFTRANSACTION> 

<cftransaction> has two attributes, action and isolation, both of which are optional. action allows you to begin, commit, or roll back a transaction. Here, begin indicates the beginning of the block code to execute, commit saves the data, and rollback undoes the code executed for the database transaction. isolation takes four values: read_uncommitted, read_committed, repeatable_read, and serializable. read_uncommitted allows a query to run without placing the lock on it. Under this option, data can change during execution. As a result, you may not get accurate data. read_committed allows a query to read data that has been locked by another query. serializable ensures that the data returned is accurate. repeatable_read is similar to serializable. It ensures that if two selects have been made on the same data, the end user gets the same results.

The code to use <cftransaction> is

 <CFSET insertresult = "S"> <CFTRANSACTION> <CFTRY>     <CFQUERY NAME="Myquery_1" DATASOURCE="Mydb_1">       Insert into tab_1 values (field1, field2, field3, field4)     </CFQUERY>     <CFSET insertresult = "F">     <CFCATCH TYPE="database">       <CFOUTPUT>        #cfcatch.message#       </CFOUTPUT>     </CFCATCH>     <CFQUERY NAME="Myquery_2" DATASOURCE="Mydb_2">       Insert into tab_2 values (field1, field2, field3, field4)     </CFQUERY>     <CFSET insertresult = "F">     <CFCATCH TYPE="database">       <CFOUTPUT>         #cfcatch.message#       </CFOUTPUT>     </CFCATCH>     <CFIF insertresult EQ "S">       <CFTRANSACTION ACTION = "commit">     <CFELSEIF insertresult EQ "F">       <CFTRANSACTION ACTION = "rollback">     </CFIF> </CFTRY> </CFTRANSACTION>