Transactions

The last item that we discuss in this chapter is the concept of transactions and the CFTRANSACTION tag. There are two reasons that transactions are important to us. One reason is that if any one query fails, all the other queries are, in effect, failed by the database server. Transactions can also be used to prevent multiple users from editing the same data at the same time. An application that displays inventory is an example of such a situation. You obviously would not want two users attempting to modify inventory at the same time. What happens if FlyByNite Airlines enables two customers to purchase a ticket for the same seat on Flight 123 at the same time? We could easily have a situation where both customers purchase the same or the last ticket. Maybe FlyByNite Airlines is not a good example because they intentionally oversell flights, but it should demonstrate the concept; you are trying to prevent two users from modifying the data at the same time.

Let's take this example another step. A transaction can be a series of queries that either succeeds in bulk or not at all. We are attempting to purchase a ticket on Flight 123. What are some of the tasks that would have to be accomplished for a customer to purchase a ticket? A couple of simple actions would be adding the customer information, assigning the customer a seat on the flight, and charging his or her credit card.

Assume that Mr. Smith is at work and needs to fly from Los Angles to Chicago on a business trip next week. He has completed the online form, provided his personal information (including credit card number), and has requested seat 5B because it is the only seat left. Just as he is about to click the Submit button, the phone rings and he becomes involved in a five-minute conversation with a friend about plans for this weekend. After he hang ups the phone, he clicks the Submit button, but unfortunately, while he was on the phone, the last seat on Flight 123 was purchased.

If the Submit page were created as a simple series of queries, what would happen? The query to insert his customer information would succeed, but when the query to insert his customer number or flight manifest ran, it would fail because there were no seats left. The next query, which bills his credit card, would then execute. We obviously do not want Mr. Smith to be billed if he cannot purchase the ticket. Initially, you might think the solution is to add a conditional statement around the credit card query so that if the ticket is not issued, the credit card is not billed. However, what happens when a ticket is issued and then it turns out that Mr. Smith has reached his limit on his credit card? You can see where there is a need to be able to treat this series of queries as one so that if one fails, the others are not executed, and any previous updates are rolled back.

CFTRANSACTION

The CFTRANSACTION tag enables you to treat all query operations within the open and closing tags as a single transaction. This means that none of the changes to a database are committed until all the queries within the transaction have successfully completed. The CFTRANSACTION tag has two optional attributes: action and isolation. The action attribute specifies the transaction action to be taken. The supported options are begin,commit, and rollback. We examine these options in detail shortly. The isolation attribute specifies the ODBC lock type to be used.

This is good place to discuss the concepts of locking and isolation. There are two types of locking: exclusive and shared. When exclusive locking is employed, no other user can access the data being locked. With shared locking, other users can read but not modify the data.

ColdFusion cannot declare locks on a database. It can, however, provide the database with a suggestion or plan for locking with use of isolation levels in the CFTRANSACTION tag.

There are four levels of isolation that are supported by ColdFusion. Serializable isolation is the highest level and is the equivalent of the exclusive lock. The Repeatable Read isolation level enables other SQL statements to perform inserts. When using this level of isolation, you need to ensure that inserts do not affect your transactions. Read Committed enables both inserts and reads. This means that if a user reads data and then reads the data a second time, it is possible for the data to have changed between the reads. One caveat to this is that the data does not change until a commit has been run by the transaction that changed the data. The last level of isolation is the Read Uncommitted. This is similar to the Read Committed level except that users read changes to data as soon as they occur without a commit. This means that you are reading "dirty" data, which could actually change once more if the transaction fails and the data is rolled back.

So how does the CFTRANSACTION tag work? The transaction starts with the first ColdFusion tag after the CFTRANSACTION tag and ends with the CFTRANSACTION closing tag. It should be noted that only ColdFusion tags that require a datasource are considered to be part of the transaction. Additionally, all ColdFusion tags must use the same datasource. This is probably the one drawback to the use of the CFTRANSACTION tag it cannot span multiple datasources. When ColdFusion encounters the CFTRANSACTION end tag, the transaction is committed. If at any point the database returns an error, the database actions to this point are rolled back and the transaction is ended.

 <!--- Customer has cancelled order. We want to ensure that when order is deleted from  ORDER table all associated records from ORDERLINE table are also deleted--->  <cftransaction>       <!--- Delete order information from ORDER table --->        <cfquery NAME="DeleteOrder" DATASOURCE="MyTest">            DELETE FROM ORDER            WHERE ORDERID = #OrderID#        </cfquery>       <!--- Delete associated records from ORDERLINE table--->       <cfquery  NAME="DeleteOrderLine" DATASOURCE="MyTest">            DELETE FROM ORDERLINE            WHERE ORDERID = #OrderID#       </cfquery>  </cftransaction> 

There are situations where we might need additional control over the behavior of the CFTRANSACTION tag. One application that we worked on had several steps involved in the processing of an order for merchandise. The steps included deducting the item(s) from inventory, processing the credit card charge, and making an entry in the inventory log table. If the credit card processing failed, we wanted the inventory rolled back. Conversely, if the entry to the inventory log table failed, we did not want the entire transaction to rollback.

Another example is that of transactions that involve batch processing a number of individual transactions nested inside a transaction. If we are processing a number of credit card transactions, we would want to roll back an individual failure but not have the rollback affect the credit card transactions that had been successful. We can have more control over the CFTRANSACTION tag behavior with use of the action attribute of the CFTRANSACTION tag. There are three supported values for the action attribute:

 action="begin" (default)  action="commit"  action="rollback"  

BEGIN

The default action option is BEGIN. This specifies the beginning of the block of code to be executed. If no action attribute is specified, action=begin is assumed.

COMMIT

This option is used to commit pending transactions. CFTRANSACTION tags using either action=commit or action=rollback must be nested inside a <CFTRANSACTION></CFTRANSACTION> block.

 <cftransaction ACTION="COMMIT"/> 

Let's modify our previous example a bit so that we are deleting a number of orders and have a commit after each order is processed:

 <!--- A part of our business model we have decided to manually verify each credit card  order that specifies a shipping address that is different from the customer's address. At  the end of each business day we delete all orders that cannot be verified.  This is  accomplished with a batch process.--->  <cftransaction>        <!--- Obtain list of orders to be deleted--->        <cfset OrderList = #ordernumbers#>        <!--- Delete order information from ORDER table --->        <cfloop LIST="#OrderList#" INDEX="OrderID">              <cfquery NAME="DeleteOrder" DATASOURCE="MyTest">                   DELETE FROM ORDER                   WHERE ORDERID = #OrderID#              </CFQUERY>        <!--- Delete associated records from ORDERLINE table--->        <cfquery NAME="DeleteOrderLine" DATASOURCE="MyTest">              DELETE FROM ORDERLINE              WHERE ORDERID = #OrderID#        </cfquery>        <!--- We want to commit with each loop, this way if one        transaction fails the other are already committed and will not be        rolled back--->        <cftransaction ACTION="COMMIT"/>        </cfloop>  </cftransaction> 

You might recall that we said one of the drawbacks of the CFTRANSACTION tag is that it cannot work across multiple datasources. That is not entirely true, as we can use the CFTRANSACTION ACTION="commit"/ tag to make this happen. By nesting the CFTRANSACTION ACTION="commit"/ tag, we can commit a transaction and change the datasource.

 <cftransaction>       <!---Insert a record and then run select against the record to obtain some  output--->       <cfquery NAME="test_test" DATASOURCE="test">          SET NOCOUNT ON          INSERT INTO tbl_news (title,body,dept_id)          VALUES ('Test Test','testing test and transactions',1)          SELECT title_id = @@identity          SET NOCOUNT OFF        </cfquery>        <cfquery NAME ="get_test" DATASOURCE ="test">         SELECT news_id          FROM tbl_news          WHERE news_id = #test_test.title_id#        </cfquery>        <!---use the ACTION="COMMIT--->        <cftransaction ACTION="COMMIT"/>        <cfquery NAME="test_rits" DATASOURCE="rits">          INSERT INTO auth_users (userid, pwd, lastName, sysadmin,          createAccounts)          VALUES ('clearwater','dstm','Gimpy','1','1')        </cfquery>        <cfquery NAME="GET_rits" DATASOURCE="rits">          SELECT userid,pwd          FROM auth_users          WHERE userid='clearwater'        </cfquery>  </cftransaction>  <cfoutput>  news id - #get_test.news_id# <br>  transaction change<br>  userid - #get_rits.userid#<br>  pwd - #get_rits.pwd#  </cfoutput> 

Figure 23.2 shows the results of the preceding code with CFTRANSACTION ACTION="commit"/ commented out. Notice the resulting error reads "More than one datasource used in a CFTRANSACTION."

Figure 23.2. Error occurred while processing.

ROLLBACK

The ROLLBACK option forces a rollback of pending transactions:

 <cftransaction ACTION="ROLLBACK"/> 

You should notice the trailing forward slash after the action attribute with both the COMMIT and ROLLBACK options. This tells ColdFusion that there is no closing tag. In this next example, we are going to show how to use the CFTRY and CFCATCH tags in conjunction with CFTRANSACTION:

 <cfset commitMe="Yes">  <cftransaction>      <cftry>        <cfquery NAME="DeleteOrder" DATASOURCE="MyTest">             DELETE FROM ORDER             WHERE ORDERID = #OrderID#        </cfquery>        <cfcatch TYPE="Database">          <cftransaction ACTION="ROLLBACK"/>          <cfoutput>          WARNING a database error has occurred<br>          Message :#CFCATCH.message# <br>          Type :#CFCATCH.type# <br>          </cfoutput>          <cfset commitMe = "No">        </cfcatch>        <cfif commitMe EQ 'Yes'>        <cftransaction ACTION="COMMIT"/>        <cfelse>        <cfset commitMe = "Yes">        </cfif>      </cftry>  </cftransaction> 

There is one "misconception" related to the CFTRANSACTION tag that we would like to point out. Many developers use the CFTRANSACTION tag to guarantee the retrieval of the ID of the record that was just submitted. The common approach in Microsoft SQL using an ID Column of CustomerID would be something like this:

 <cftransaction>       <cfquery NAME="Test" DATASOURCE="MyTest">            INSERT INTO CUSTOMER (FirstName,LastName)            VALUES ('#FirstName#','#LastName#')       </cfquery>        <cfquery NAME="getMaxID">            SELECT Max(CustomerID) AS MaxID FROM CUSTOMER       </cfquery>  </cftransaction> 

Although this works a high percentage of the time, it does not guarantee that the correct value is returned. When we are developing with Microsoft SQL, we prefer to use the following method instead:

 <cfquery NAME="Test" DATASOURCE="MyTest">       <!--- Set NOCOUNT ON prevents the return of number of rows from the       insert--->       SET NOCOUNT ON       INSERT INTO CUSTOMER (FirstName,LastName)       VALUES ('#FirstName#','#LastName#')       SELECT  cust_id = @@identity       SET NOCOUNT OFF  </cfquery> 

Of course, only some databases such Microsoft SQL Server 2000 and Sybase ASE 12 support the previous example. With Oracle, you must take another approach involving sequences.

Summary

Transactions provide a way for data manipulation to be processed as a single event. The advantage is that either all SQL statements execute successfully and are committed or all are rolled back. Databases that support transactions provide levels of locking. Although ColdFusion cannot control the level of locking used by the database, the CFTRANSACTION tag can influence the lock with the use of the isolation attribute. The action attribute provides you additional control over the behavior of the CFTRANSACTION tag. It is important to remember that although most commercial databases support transactions, there are some popular open-source databases that do not or did not until recent releases.