When processing data, you sometimes will require a series of steps to complete a certair task. This is known as a transaction. If one of the steps is not completed, then the whole transaction should be cancelled or should be repelted from ste 1.You may also require a locking mechanism while communicating with your database. ~ou might need this tc ensure that users are not overwriting each other’s data. In yourColdFusion application. you can group a series of queries as a transaction and specify certain locking mechanisms. The characteristics of a transaction are: .

• It must have a beginning and an end.
• If it fails in the middle of the process, no part of it can be saved.
• It must be completely saved or completely undone.

Cold Fusion provides an easy way to create a transaction for multiple queries by using the CFTRANSACTION tag. Any queries placed between the opening and closing CFTRANSACTION tags are treated as a single transaction. The transaction begins at the first CFML tag connecting to a data source. None of the changes are committed to the database until all the transactions within the block are successfully executed: A rollback will occur if even one error occurs in the transaction.

Transaction isolation provides you with control over database locking during a transaction. You can control the level of isolation by using the ISOLATION attribute of the CFTRANSACTION tag and setting it to one of the values listed in Table 10.11. These isolation types refer to locking schemes implemented by various ODBC drivers.

In ColdFusion 4.5, you also have the additional optional attribute: ACTION. The three valid options’ for action are listed

The format for your CFTRANSACTION tag is:

. <CFTRANSACTION action2’action’ isolation=’isolation_level’>

A very nice feature about CFTRANSACTION is the ability to use queries that interact with more than one data source. In a single transaction, you can have one query deleting data from one database and then another updating data in a second database. In order to allow this type of interaction, ColdFusion requires you to commit or rollback the transaction with a single database before moving on to a new database. You can do this by nesting a CFTRANSACTION tag with the ACTION attribute set to COMMIT or ROLLBACK:


You can also rollback:


H this is not done, ColdFusion will throw an error when switching to a new data source within the CFTRANSACTION tag. You will notice a trailing II /” in the CFTRANSACTION tags just mentioned. This is so that ColdFusion recognizes that the tags are nested; it is a required for the tag to be recognized .

When the closing CFTRANSACTION tag is encountered, ColdFusion commits the transactions to the database and terminates the transaction block. To demonstrate the use of CFTRANSACTION, look at Listing 10.20 (a modified version of Listing 10.9) with results


Posted on November 14, 2015 in Using CFQuery and SQL to Interact with the Database

Share the Story

Back to Top
Share This