Controlling Commits

So far, we reviewed an example in which <cftransaction> took no attributes. We briefly explained isolation levels. The only other attribute for <cftransaction> is action, which controls what is and is not committed to a database.

commit

When a transaction is finished, and the ColdFusion server finds the closing tag of <cftransaction>, all updates made to the database are subsequently committed. This means that the status of the updates is changed from questionable to certain, and the locks are released.

If, however, a commit within a given transaction makes sense before the transaction is complete, a nested <cftransaction> tag can be used with an action of commit. Turning back to the wire transfer example, say that the woman decided to give wire transfers to all her children instead of just one. In this new situation, the bank considers this a single transaction and thus locks any databases once. But each successful deposit can force a COMMIT to the database and allow other transactions to read more accurate balance information. The new example looks like this:

 <cftransaction>   <!--- here is a list of all her children's bank IDs ---->   <cfset bankIDList="234234234,2345234,6456456456,34534345">   <!--- here is a list of all her children's account numbers --->   <cfset AccountIDList="34234234,234562345,6123563,635789345">   <!--- four accounts... loop four times --->   <cfloop from=1 TO=4 index="i">     <!--- remove $100 from the woman's account --->     <cfquery datasource="AccountDB">     UPDATE Account     SET Balance = Balance - 100     WHERE AccountID = 4334044033     </cfquery>     <!--- call the wire transfer tag (sends data to bank #2) --->     <cfx_sendwire bnk                    amount="100"                   accountid ="#listGetAt(AccountIDList,i)#">     <cfif wireResult is 0>       <cfabort>     </cfif>     <cftransaction action="COMMIT"/>       <!--- commit each deposit when it completes --->   </cfloop>   <!--- enter the woman's name in the wire transfer log --->   <cfquery datasource="TransferLog">   INSERT INTO transferLog     (accountID,Time,BankIDList,wiredAmount,recipientAccountIDList)   VALUES (4334044033,getDate(),'#bankIDList#',100,'#accountIDList#')   </cfquery> </cftransaction> 

NOTE

The statement <cftransaction action="commit"/> may look strange because of the forward slash at the end. This slash is XML syntax and is used here in lieu of an end tag. Because this specific example of <cftransaction> doesn't have an end tag, you must signify the end tag with the forward slash or as <cftransaction action="commit"><cftransaction/> (with nothing between the two tags). This is both legal and supported.

rollback

Another action type in addition to commit is rollback. Rolling back is a way of telling the transaction to purposely fail. In database vernacular, these two are the only options for any transaction. Either a transaction commits the new changes, or it rolls back to the original state of the database.

For our example, using rollback is a better solution than using <cfabort>. If you look at the sample code so far, it will abort in the event of a deposit failure at one of the children's banks. This is not the best scenario because each deposit is contingent on the success of the previous one. Instead of using <cfabort>, you can use <cftransaction action="rollback"/> (paying attention to the XML syntax as explained in the preceding Note):

 <cftransaction>   <!--- here is a list of all her children's bank IDs ---->   <cfset bankIDList="234234234,2345234,6456456456,34534345">   <!--- here is a list of all her children's account numbers --->   <cfset AccountIDList="34234234,234562345,6123563,635789345">   <!--- four accounts... loop four times --->   <cfloop from=1 TO=4 index="i">     <!--- remove $100 from the woman's account --->     <cfquery datasource="AccountDB">     UPDATE Account     SET Balance = Balance - 100     WHERE AccountID = 4334044033     </cfquery>     <!--- call the wire transfer tag (sends data to bank #2) --->     <cfx_sendwire bnk                    amount="100"                   accountid ="#listGetAt(AccountIDList,i)#">     <cfif wireResult is 0>       <cftransaction action="rollback"/>      </cfif>     <!--- commit each deposit when it completes --->     <cftransaction action="commit"/>    </cfloop>   <!--- enter the woman's name in the wire transfer log --->   <cfquery datasource="TransferLog">   INSERT INTO TransferLog      (accountID,Time,BankIDList,wiredAmount,recipientAccountIDList)   VALUES (4334044033,getDate(),'#bankIDList#',100,'#accountIDList#')   </cfquery> </cftransaction> 

NOTE

The third and last option for the action attribute is begin. It indicates that a transaction is starting and is the default value.

NOTE

Some operations may require transactions that span requests. Unfortunately, the nature of the Web and the way in which ColdFusion uses it makes this impossible. All transactions must start and end on the same page.

TIP

A popular technique of database administrators is to build transactions into stored procedures instead of using the <cftransaction> tag. As was discussed in Chapter 23, stored procedures are precompiled and thus run faster than passing SQL from ColdFusion. In addition, multiple transactions can be placed within a single stored procedure, allowing transactions to complete regardless of the ColdFusion server's availability. Finally, some databases, such as Microsoft's SQL Server, offer robust features wherein a transaction can spawn remote procedures on other databases.

Calling stored procedures via CFML was covered in Chapter 23, "Stored Procedures."