Following article as written by Simon Horwith at (http://www.adobe.com)
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:
What Are Transactions?
Every time you execute a command on a database, it is in the context of a transaction. Every transaction has an isolation level associated with it. Isolation level defines the level of locking that is in effect when the transaction runs, as well as to what degree the transaction will obey other concurrent transaction locks. There are four types of isolation levels that a transaction can use:
Transaction Isolation Levels
| Isolation Level | Isolation Level Description |
|---|---|
| Read Uncommitted | Sometimes called a dirty read, read uncommitted transactions read data in a database without regard for current exclusive locks that other transactions may currently have in place, and does not place any shared lock on the data being accessed. During an uncommitted read, data read in may be changed by other transactions- resulting in a user receiving inaccurate data in a recordset. |
| Read Committed | Read-committed transactions use shared locks to assure that a request has access only to committed data (and that the transaction obeys other transactions) and that no other transaction modifies data rows that the current read committed transaction uses. This type of transaction does not prevent other transactions from modifying the data set after the read-committed transaction completes reading the data rows. By default, most RDBMS databases use a read committed for their transactions. |
| Repeatable Read | Repeatable-read isolation level locks behave the same as read committed, except the rows used in the recordset are exclusively locked until the transaction completes. Another transaction may add more data rows to the table(s) that the transaction uses through a repeatable-read transaction. Because repeatable-read transactions use a high level of locking, best practices discourage using it. |
| Serializable | Serializable isolation level transactions are the most data-consistent transactions, but also have the most overhead. A serializable transaction places an exclusive lock on every data-table in use for the duration of the transaction. Essentially, access to database tables is single-threaded when you use serializable transactions. Because serializable transactions use the highest locking levels, best practices discourage using it. |
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!
How to Use Transactions in ColdFusion?
We’ve already discussed the benefits of transactions as well as the types of actions and isolation levels that exist with transactions, so how can we leverage this in our ColdFusion pages? The cftransaction tag allows developers to group as many cfquery tags together as they desire in a single transactional unit. The cftransaction tag has two optional attributes (valid values below, too):
- action (valid values are begin, commit, or rollback)
- isolation (valid values are read_uncommitted, read_committed, repeatable_read, or serializable)
Each attribute and its values correspond directly with the transaction mechanisms already discussed. To simply treat two queries as a single logical unit, wrap the queries with one set of cftransaction tags. The examples shown below use the cfsnippets data source created when you install ColdFusion with the example applications. To insert a new employee into the Employees table and retrieve the new unique id within one transaction, you would use code that looks like the following:
If any cfquery tag generates an error within a cftransaction block, all other cfquery tag operations in the same cftransaction transaction will roll back. To test this, copy the above code to a file, and change the name of the ‘Employees’ table in the WHERE clause in the second query to ‘Employees2’, and then browse the page. An error message appears due to the errant table name in the second query. If you look at the cfsnippets employee database table-even though there was no error in the first query, it never committed to the table because it was within the cftransaction tag.
To explicitly roll back or commit a transaction depending on whether or not a query violates business rule(s), nest your cftransaction tags. The following example shows a transaction that inserts a record into the Orders table, retrieves the CardType and Order_ID for the row just inserted; it rolls back or commits the new row depending on whether the new record has a valid CardType.
The classic example of using rollback and commit in this scenario is in an online banking system that allows a user to transfer funds from one account to another. An obvious business rule in this scenario is that a user cannot withdraw or transfer more money from an account than that account contains. The code for transaction-based logic looks something like the following:
Best Practices, Limitations, and Known Issues
Before wrapping cftransaction tags around each and every query you’ve ever written, there are a few things to take into consideration. Like all other tags in ColdFusion, each time the ColdFusion server processes a cftransaction tag, the server must perform a task. In other words-every CFML tag is an instruction to the server and every tag comes with a certain amount of overhead. I like to think that most tags are worth the overhead required for execution, but as a best practice do not wrap each and every query with cftransaction tags-it will slow down page execution times. It is better to verify that the default transaction settings for your database are acceptable for most operations, and to only use cftransaction for those critical queries where you want to ensure data accuracy and manage concurrent transactions.
As a best practice, perform all transactional logic inside of stored procedures. For that matter, anytime you can have the database do something rather than the ColdFusion Server, it’s usually a best practice. The main reason for this is encapsulation. Keeping the transactional logic and SQL in one place makes it easier to maintain. It should also perform slightly better in most situations. Keep in mind that although transactions will benefit your applications architecturally, writing transactional procedures can mean a learning curve for developers. This learning curve is one reason to use the cftransaction tag-the other is lack of support for stored procedures on certain database platforms.
If concurrency is an issue, you are probably already using or considering using stored procedures in your application. Moving the transactional logic to the database also means one less thing for the ColdFusion Server to do. If you are using Microsoft Access, you will want to use cftransaction to control database locking behavior for concurrent requests, as Access has does not support stored procedures. Note that though you can save queries on an Access database (as Queries), these queries do not offer the benefits and behaviors usually associated with stored procedures.
If your application must use heavy locking transactions, such as serializable transactions, test the application under load to verify that database operations do not timeout with the current data source name settings. You may need to increase the timeout settings to prevent ColdFusion server from generating error messages during peak usage. That said, sometimes serializable transactions are required in order to maintain integrity. Earlier in this article, it was recommended that in order to accurately retrieve the unique id of the row that was most recently inserted, the two queries should be placed within a single cftransaction block.
TechNote 17000, Getting Auto-Increment Primary Key Values on the Macromedia website suggests that in order to guarantee the accurate retrieval of the new unique id of a row you just inserted, you must place the two queries within an exclusive type named cflock within the cftransaction block. This isn’t completely true because other applications that access the data will not obey the lock, nor will any other queries that ColdFusion executes obey the lock since they are not contained within the same named cflock. There is no need to use the cflock tag within cftransaction, but the TechNote is correct regarding concurrency locking issues. The only way to be 100% sure that the unique id retrieved is the newly inserted row, is to use a cftransaction block that specifies the isolation level as serializable. This keeps the control for table locking at the database level and treats the insert and select statements being executed as a single transactional unit.
One thing I didn’t discuss in this article is the use of more than one data source within a cftransaction block. While you can put cfquery statements that use more than one data source inside of a cftransaction block, they are not part of a single logical transaction. In order to use multiple data source names in one cftransaction, you must explicitly roll back or commit a transaction block using one data source before you begin one with another. You do this by nesting cftransaction tags that explicitly begin, then roll back or commit, one transaction after another, using the action attribute. Between each nested cftransaction tag pair that begins and then rolls back or commits, put the cfquery tag(s) that comprise that transaction. These queries will each access the same data source.
Transactional processing is an important concept to understand-and implementing it strategically in an application can ensure a successful user experience. The ColdFusion cftransaction tag makes it easy to implement transactional processing in new and existing applications. You’ll yield more robust exception handling, be able to enforce business rules, and modularize code as a result of using it.
