Cftransaction Explained

I've seen some code lately that has prompted me to write this blog entry. The code in question uses cftransactions heavily and in my opinion in completely unnecessary places in addition to the use of serializable isolation levels which are also unnecessary. So what is the cftransaction tag used for, when should I use it and what about that isolation level thing I just mentioned?

Lets start with the first question.

What is CFtransaction?

The CFtransaction tag allows a developer to tell the database to treat a series of queries as a single unit of work. Transactions are all or nothing either all queries execute successfully or none are committed to the database. (For Completion sake in the case of CFtransactions in CF8 that isn't exactly true because of the new "save point" attribute but we won't go into that now). According to the wikipedia definition of a database transaction

A database transaction, by definition, must be atomic, consistent, isolated and durable. These properties of database transactions are often referred to by the acronym ACID

So in the following code example all the queries inside the cftransaction block will be executed by the database as a single unit of work.

Note also that CFtransaction tag adds overhead (time). Use it wisely. It's not something you need to put around a single select statement, same goes for a single update statement, or for that matter any single query statement. A single statement is already a single unit of work so the use of a CFtransaction in this case is just adding unnecessary overhead to your application.

What is Isolation Level?

Isolation is a database property that defines how and when changes made by one transaction are visible to another concurrent transaction. This means that every time you execute a query against a database it's in the context of a transaction. Every transaction has an isolation level associated to it, whether you use the cftransaction tag or not.

The isolation level defines how the database will lock the resources that one transaction is accessing as well as to how well it adheres to locks by other concurrent transactions

Isolation level comes in a number of different flavors from quite unrestrictive (read_uncommitted) to very restrictive (serializable).

The default isolation level for most databases is READ_COMMITTED, this does not allow dirty reads but it does allow so called phantom reads.

The most restrictive isolation level is SERIALIZABLE. This basically forces the database to execute all the transactions completely isolated from another; one transaction is executed after the other.

Because of this you should be very careful when and where you use SERIALIZABLE isolation level. The main purpose of the SERIALIZABLE isolation is to prevent race conditions in your database

So when should you use a cftransaction?

• When the execution of a successive queries depends on the successful completion of the previous.
  
  <cftransaction>
      <cfquery name="deductFromAccount" datasource="#request.dsn#">
          update customerAccount
          set accountBalance = accountBalance - #amount#
          where customerID = 1
      </cfquery>
      
      <cfquery name="addToAccount" datasource="#request.dsn#">
          update customerAccount
          set accountBalance = accountBalance + #amount#
          where customerID = 2
      </cfquery>
  </cftransaction>
  
  Note this is just for example purposes, you wouldn't really do it this way.
• When retrieving the id of an inserted record.
  
  <cftransaction isolation="serializable">
      <cfquery name ="insertRecord" datasource="#request.dsn#">
          insert into foo (firstCol,SecondCol)
          values(1,2)
      </cfquery>
      
      <cfquery name="getID" datasource="#request.dsn#">
       select max(id) from foo
      </cfquery>
  </cftransaction>
  
  notice here the use of isolation="serializable". Depending on your database this may be the only way to be sure that you get the correct ID back as another row may be inserted before the first insert is committed, by putting the two statements in a transaction with the isolation of serializable you guarantee that another record won't be inserted between the first insert and the select.

Further Reading

Simon Horwith has a good write up on Devnet on transactions that is worth a read.

On wikipedia you can read more about the individual isolation levels.

You can also read about race conditions on wikipedia but not in the context of databases, but it should provide you with a good idea of the problem.

Happy Coding...