Resource Point

Database Transactions in Coldfusion MX

October 22, 2009 – 7:55 pm by KA

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:

1. What are transactions?
2. Why are transactions important?
3. Who should use transactions?
4. How to use transactions in ColdFusion
5. Transaction best practices, limitations, and known issues with transactions

 

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.

Posted in Cold Fusion | No Comments »

CFLOCK Confusions and to be used in Correct Way

October 22, 2009 – 6:37 pm by KA

Following article was delivered by Simon Horwith at (http://www.horwith.com)

It’s recently come to my attention that a lot of us ColdFusion Developers, including those who think they understand CFLOCK, don’t fully understand or appreciate how the CFLOCK tag actually works and when it should be used, so I thought I’d explain… starting at the begining with a very brief CFLOCK history lesson and a short overview of how CFLOCK works.

The ColdFusion Application Server runs on top of Java and though this underlying Java engine is inherently thread safe, there are occasions when developers need to programmatically control execution of code. The two most common reasons to do this are:

1.To control access to a shared resource
2.To prevent race conditions in business logic
In order to allow developers to control the execution of code blocks, CF Developers have a tag known as CFLOCK. Prior to ColdFusion MX the CFML engine wasn’t thread safe and scope level locking had to be performed in order to ensure data integrity when accessing shared memory scopes. As I already mentioned, as of CFMX the server is inherently thread safe, which means we don’t need to perform scope level locking in our code anymore. In the interest of being thorough I will mention that scope level locking is implemented by providing a “scope” attribute to the CFLOCK tag (the value being either “application” or “session”). From this point further we won’t discuss scope locks since their use has not been recommended since ColdFusion 5.

The other type of locking, which continues to be useful in CF(MX) 6 and all subsequent versions of the server, is known as “named locking”. A named lock is a CFLOCK that, rather than being passed a “scope” attribute, is passed a “name” attribute – the value of which is a user-defined string. The CFLOCK tag has a “timeout” attribute as well as a “throwontimeout” attribute. “Timeout” is a number representing the number of seconds that the CFLOCK should wait for other locks to complete. If a CFLOCK waits beyond that number of seconds and the “throwontimeout” attribute is “true” then an error is thrown – otherwise the server skips that CFLOCK block and continues processing.

As I mentioned, the CFLOCK tag has a “name” attribute. Named locks obey other locks with the same name. In order to define how locks obey each other, developers pass the CFLOCK tag a “type” attribute – the value of which can either be “readonly” or “exclusive”.

When the ColdFusion server encounters an “exclusive” CFLOCK it checks to see if any other thread is already executing an exclusive lock with that name and, if so, it waits until it’s timeout value is reached at which point an error is thrown or the CFLOCK block is skipped. If no other exclusive CFLOCK with the same name is executing, then the current thread creates the exclusive lock and executes the code within the CFLOCK block (releasing the lock when it’s done).

When the ColdFusion server encounters a “readonly” lock, it immediately executes the code in that block UNLESS an exclusive lock with the same name is currently executing. If another thread is already executing an exclusive lock with that name then the thread waits until no exclusive locks with the same name are running or until its timeout value is reached (at which point an error is thrown or the CFLOCK block is skipped)

So those are the basics of how CFLOCK works, and that overview is an accurate description of how most developers who know CFLOCK would explain it’s use. Now let’s discuss some specifics about CFLOCK that many developers don’t know, are unsure of, or are mistaken about.

First we’ll revisit the two most common reasons to use CFLOCK that I mentioned earlier, and discuss specifics about when and why you may want to use CFLOCK in a ColdFusion application. The first occasion for CFLOCK use is controlling access to shared resources. Though databases are the most common form of shared resources in web applications, using CFLOCK to control database access is generally not recommended. This is because database systems have support for transactional processing and, unlike CFLOCK, database transactions will be obeyed by every application that attempts to access the database. On the other hand, there is no native transaction manager for the file system so applications that access files and directories should use CFLOCK appropriately to prevent file corruption, dirty reads, or other possibly undesirable results when reading/writing shared file system resources.

The second occasion developers should use CFLOCK is to prevent “race conditions” in business logic. In simple terms a “race condition” is when the results of one process are dependent on the timing of other processes in order to ensure accuracy. For the most part, though not always, race conditions in applications occur when two processes are accessing and manipulating the same data in memory. So a race condition is really no different than our first common reason for using CFLOCK – accessing a shared resource. In the case of a race condition the shared resource we’re controlling access to is some data (variables) in memory. In either case our concern is the integrity of the resource in question – in the case of race conditions specifically, the concern is usually the assignment of a false value to shared data. In my experience, the most commonly occuring need for CFLOCK in CF applications is to “thread-safe” access to shared data.

It is commonly accepted that ColdFusion Components (CFCs) are an invaluable tool in the construction of ColdFusion applications and their use is strongly recommended in applications. The more complex the business logic and the more heavy the reliance on CFCs for both business logic and data encapsulation and persistance, the more likely the need to use CFLOCK to prevent race conditions and ensure data integrity. Let’s look at a simple example. Suppose you want to programatically track how many sessions are active in an application using a CFC. Let’s say you want to keep track of the session ID, timestamp the session began, and timestamp of last request. The CFC has an init method that acts like a constructor – it simply creates a private variable which will be used to store information about the active sessions. This CFC is a singleton – an application has One instance of it in the application scope – so we can create that instance and call init() within the onApplicationStart() method of Application.cfc. To add a session or update a session’s last request timestamp our session tracker object has a method called logRequest() which accepts the session ID as it’s only argument. LogRequest is called in the Application.cfc onSessionStart() method when a session starts and it’s also called in the onRequestStart() method to record the timestamp of each request. The CFC has a removeSession() method which also accepts a session ID – that method simply removes the session information from the object’s internal data. It is called from the Application.cfc onSessionEnd() method. Lastly, our object has a method called getSessionCount() that returns a number to tell me how many sessions are active. This function could be called from anywhere in our application. The code inside of the init(), logRequest(), and removeSession() methods each requires an exclusive named lock because each of them contains code that modifies the persisted data. The code inside the getSessionCount() method requires a readonly lock in order to ensure that the value returned reflect an accurate count – that it waited for any code that modifies the data to complete before returning a value.

It’s worth noting that the possibility that a race condition will rear its ugly head increases in direct proportion to increase in the length of time that blocks of code take to run. In our simple example, none of the methods described should realistically take more than a few milliseconds to execute because there’s very little actual business logic involved. This means that even without CFLOCK the odds are fairly good that the methods will return accurate values and that data values are accurate. If we were to change the requirements so that rather than just tracking session information in memory, session information is also persisted in a database or XML file somewhere, the code in these methods would most likely take much longer to execute.
We’ve discussed the history of CFLOCK, how it works from a simple point of view, and when/why/how to use CFLOCK in an application… but there’s a fairly common misconception about CFLOCK that still needs clarification which is the rules of engagement for CFLOCK in terms of how and when one CFLOCK obeys the rules of another. This misconception is what prompted me to write this rather lengthy essay. I often times hear CFLOCK explained by developers like so: “the code in an exclusive named lock or a readonly named lock will wait for any code currently executing in an exclusive named lock with the same name before it will run”. In other words, any lock with a name will wait for an exclusive lock with the same name to complete first. This is a misleading description that, without clarification, is inaccurate. The reality is that this description of how CFLOCK works is only accurate when you’re describing how locking is handled by separate requests. One user’s request will obey the locking rules as described with regards to currently executing requests initiated by other users. But what about locked code that calls other locked code in the same request? The rules don’t apply quite the same.

The truth is that it’s entirely possible and even commonplace in some applications, for the code in two exclusive CFLOCKs with the same name to execute at the same time. It’s even possible for the code in a readonly named lock to execute at the same time as the code in an exclusive lock with the same name (so long as the readonly CFLOCK block was called by the exclusive CFLOCK block code). Why? When ColdFusion is running code in a named CFLOCK block and that code attempts to run code containing a CFLOCK with the same name, if both CFLOCK tags have the same level of locking (both are exclusive or readonly) then the the second lock can safely be ignored (the currently executing thread aleady has a lock that follows the same rules). If, however, the second CFLOCK has a different level of locking then the behavior changes. From an exclusive readonly lock, attempting to execute a readonly lock of the same name results in the second lock being ignored. In this case the readonly locked code executes in an exclusive locked mode (the lock is “updgraded”). On the other hand, if the first CFLOCK executed by a server thread is a readonly named lock and it in turn attempts to run code within an exclusive lock of the same name, the exclusive lock code will never execute. This is because in order for code in a named CFLOCK to be called from code in another CFLOCK with the same name, the second cFLOCK must be able to run as if it is a lock of the same type as the first CFLOCK (it must be able to “upgrade” to the level of the first CFLOCK). Readonly locks from different requests can execete in parellel and they obey exclusive locks of other requests – so from within a readonly lock it would be irresponsible for the server to assume that it is OK to execute an exclusive lock. Here is a simple code example illustrating how exclusive locks can be executed from each other:

In the preceding code example code in one function, from with an exclusive CFLOCK, executes another function whose code is in an exclusive lock with the same name. Paste the contents into a new file an run it and you’ll see that it works just fine – the second CFLOCK (the one in test2) doesn’t wait for the CFLOCK already in progress to complete. Change the first CFLOCK to be readonly like in the following example, and you’ll see that because the current thread is executing inside a readonly lock, it is unable to run code inside an exclusive lock with the same name… thus an error is thrown.

The important lesson here is that, when developing complex logic that needs to be thread safe, always ensure that no code inside of a readonly named lock attempts to execute code inside of an exclusive named lock of the same name. You will generally be safe following the old rule of putting exclusive locks around code that modifies data and readonly locks around code that only reads the data, but not 100% of the time. It’s not unheard of for a method that returns a ‘count’ value to first call a maintenance or ‘clean-up’ method first. Following the ‘golden rule’ could get you into trouble in this scenario. This brings up another question I’ve heard in response to learning all this, “can’t I just use exclusive named CFLOCKs for everything and not have to worry about it?”. The short answer is “yes” – if all the named locks in CFC methods use the same name and are exclusive, you won’t have to worry about locks timing out because of the way in which the code is calling it. It is not ideal to use exclusive locks alone however, because readonly locks executed by different user requests are allowed to execute simultaneously. If you have methods that are called from outside your CFC and that only return data then you want readonly locks – to use exclusive locks would single-thread the execution of those requests, which means a performance hit as well as risk of CFLOCK timeouts – something you don’t have when the lock is readonly.

The documentation for CFLOCK does describe all of this, except that it’s not entirely clear about code in two different places that call each other (i.e. CFLOCK tags in methods that call each other) – it describes the rules for CFLOCK as they pertain to different user requests and as they pertain to nested CFLOCK tags. Though methods calling each other don’t have their respected CFLOCK blocks nested, the rules in the CF documentation for CFLOCK pertaining to nesting one CFLOCK inside of another also apply to this method call locking scenario. It’s a nuance, but one I felt was worth mentioning. Hopefully, in the process of writing so much in one sitting, I have succeeded in making my point clear and haven’t confused the issue for anyone.

Posted in Cold Fusion | No Comments »