Controlling Database Transactions in ColdFusion MX

Age : 45 Joined : 08 Mar 2008 Posts : 1850

Many developers choose ColdFusion for web application development because it is easy to use and because CFML minimizes the complexity of common application coding activities. This allows developers to spend more time working on the more complex functionality in their applications. One way in which ColdFusion simplifies the development process is in the way it connects to and extracts data from a database-usually one of the first things that a ColdFusion developer learns. This is accomplished with the cfquery tag.

As easy as it may seem to be to make your database accessible to web applications, doing so efficiently is not as simple as putting cfquery tags on a page. Many individuals can browse a web application simultaneously. When web application users concurrently query database data, it is possible that some users may receive incorrect data if the application doesn't handle queries in the context of that user's current transaction. In fact, even a single application visitor may be able to create or retrieve invalid data if that user's database interaction isn't executed in the protected scope of a database transaction. The classic example of invalid data retrieval would be that of an application that inserts a new record then executes a select statement in order to retrieve the unique id of the freshly inserted row; but instead of receiving the correct ID, the app receives the unique id of a row inserted in a concurrent transaction. In this article, we will examine:

In addition to setting a locking isolation level and executing the SQL command(s) that comprise the transaction, a transaction can perform three other actions. A transaction can begin, commit, and roll back. Since every transaction must begin, this action always occurs. If a transaction performs a roll back, transaction undoes any data it has modified, inserted, or deleted by the SQL commands within the transaction up to this point. It is as if the commands were never executed. If a transaction commits, all SQL commands in the transaction are committed, or completed in the database. After a commit, you cannot undo the commands executed up to that point.

Why Are Transactions Important?
Transactions are important for two reasons. First, transactions allow your application to execute multiple SQL statements as a single logical unit. An application may need to ensure that the results of SELECT statement(s) remain unaffected by other currently executing transactions. The earlier example discussed an application that inserts a new record into a database table and then selects the MAX ID for that table in order to retrieve the Unique ID for the recently inserted row. A developer can only ensure that his/her SELECT query will select the proper ID and return it to the to the proper user session by defining the INSERT statement and the statement that retrieves the MAX ID as members of the same transaction. By placing both the SELECT and INSERT statement inside of a single transaction, you instruct the database to lock access to the current resource, thus preventing other sessions from modifying the data until the current transaction finishes. Not locking access to the table data may result in one user errantly retrieving the ID of another.

Secondly, you cannot blindly execute and commit all SQL statements. For instance, some SQL statements depend on the results of other SQL statements before running successfully. An example of this would be an online banking system. If a user wants to withdraw money from a savings account and deposit it into a checking account, the application must roll the transaction back to keep the books accurate if either of the two operations fails. Sometimes a SQL statement can violate business rules. An application must be able to roll back these SQL statements in the event that they violate business rule(s). A good example of this is an online banking system-customers can withdraw money, but if doing so leaves a negative account balance, the application should roll back the operation and prevent the customer from being able to withdraw more money than is available in the account.

Who Should Use Transactions?
Truthfully, every developer who develops ColdFusion applications that read from and write to a database should use transactions. I urge developers whose applications query Oracle, SQL Server, or any other enterprise RDBMS software for data, to learn everything they can about that RDBMS platform and take advantage of its strengths. In particular, I recommend that you store the majority of an application's SQL inside of stored procedures (to get started with stored procedures, read Sam Neff's Learning Stored Procedure Basics in ColdFusion MX).

Stored procedures give database programmers complete control over transactional constructs like commit, rollback, and all of the isolation levels. Of course, many ColdFusion developers don't yet have the database expertise required to write transactional stored procedures on their respective database platform. Also, many developers use Microsoft Access for their back-end database. Microsoft Access doesn't support transactional processing or stored procedures through any familiar interface to ColdFusion developers. Access does support transactions and transactional processing through its DAO interface when you instantiate it from Visual Basic code, and within the property sheet settings of an Access Query. Unfortunately, ColdFusion developers cannot programmatically access these hooks, nor do the majority of developers have free time to play around with trying to get Access to do what we want. Never fear - there is an easy way to leverage transactional processing in your ColdFusion pages!