DataFaucet ORM

Transactions

Transactional processing allows multiple SQL statements to be executed as a group. If any one statement fails, then the entire collection of statements can be rolled back so that the database reflects the state of the data prior to execution of the first statement. As an example, let's say you had two tables, a Content table and a ContentFile table, and when you insert data into the ContentFile table you always want to insert a matching record in the Content table. You would have two cfquery tags like this:

<cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
  INSERT INTO tblContent (a,b,c) VALUES (...) 
</cfquery>

<cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
  INSERT INTO tblContentFile (x,y,z) VALUES (...) 
</cfquery>

Unfortunately if an error occurs while inserting the record into tblContentFile, then because the tblContent insert already occurred, you'll have an extra unmatched record in your database, which you don't want. What you want is for the insert to be "all or nothing": either both records should be inserted or neither record should be inserted if an error occurs. This is where you might use a transaction like this:

<cftransaction action="begin" isolation="read_uncommitted">
  <cftry>
    <cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
      INSERT INTO tblContent (a,b,c) VALUES (...) 
    </cfquery>
    
    <cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
      INSERT INTO tblContentFile (x,y,z) VALUES (...) 
    </cfquery>
    
    <cfcatch>
      <cftransation action="rollback" />
      <cfrethrow />
    </cfcatch>
  </cftry>
<cftransaction>

In this example if an error occurs during either query, the transaction is rolled back, causing neither record to be inserted.

This is good, but unfortunately it's a bit limited. In a CFC object you might want to put a couple of different method calls inside a transaction. If either of those method calls contains another transaction however, ColdFusion produces an error.

Example:

<cfcomponent output="false">
  <cffunction name="insertStuff" access="public" output="false">
    <cfargument name="stuff" type="struct" required="false" />
    <cfset var update = 0 />
    
    <cftransaction isolation="read_uncommitted">
      <cfquery result="update" datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
        UPDATE tblStuff SET ... WHERE ... 
      </cfquery>
      
      <cfif not update.recordcount>
        <cfquery datasource="#MyDSN#" username="#dbUsr#" password="#dbPwd#">
          INSERT INTO tblStuff (...) VALUES (...) 
        </cfquery>
      </cfif>
    </cftransaction>
  </cffunction>
  
  <cffunction name="commitSomeData" access="public" output="false">
    <cfargument name="stuff" type="any" required="false" />
    <cfargument name="otherStuff" type="any" required="false" />
    
    <!--- create a big fat ERROR by nesting transactions --->
    
    <cftransaction isolation="serializable">
      <cfset insertStuff(stuff) />
      <cfset insertOtherStuff(otherStuff) />
    </cftransaction>
  </cffunction>
</cfcomponent>

This is particularly a problem for any application developed with an Object-Oriented approach in which each object is supposed to be a "black box", unaware of what the other objects are doing. If an individual object needs to create a transaction, it risks causing errors when another object executes its methods within a separate transaction.

The approach to dealing with that problem usually involves breaking with best practice and making the objects know more about each other than they should (tight coupling). Even without making the objects know more about each other, you might simply perform all transactions outside of the objects so any combination of methods that need to be executed in a batch can be safely anonymous, yet this poses the problem that the outer application now needs to know what needs or doesn't need to be locked, which yet again breaks with OO best practices in which the individual object should be able to understand its own transaction needs.

DataFaucet has one built-in tool for dealing with this problem of creating transactions, and that's a transaction.cfc object in the system directory. A new transaction can be fetched via the DataFaucet factory (or from any datasource object) like this:

<cfset trans = request.DataFaucet.getTransaction() />

Once you have the transaction object then you can use it to execute methods on other objects like this:

<cfset trans.execute(component, methodName [, args]) />

The last argument is a structure containing arguments to pass to the specified method of the component. So an object might transact one of its methods like this:

<cfcomponent output="false">
  <cffunction name="getTransaction" access="public" output="false">
    <cfreturn getDatasource().getTransaction() />
  </cffunction>
  
  <cffunction name="doStuff" access="public" output="false">
    <cfset getTransaction().execute(this,"doTransactedStuff") />
  </cffunction>
  
  <cffunction name="doTransactedStuff" access="public" output="false">
    <cfset insertThis() />
    <cfset updateThat() />
  </cffunction>
</cfcomponent>

The transaction object internally communicates with a transaction manager for the current requests and will create only one active transaction at a time. Additional nested transactions will be rolled into the current transaction.

The transaction object also extends the broadcaster.cfc object which allows you to attach listeners to the transaction for the events "commitTransaction" and "rollbackTransaction". If any errors occur within the transaction, any listeners to the assorted transactions are notified of a "rollbackTransaction" event and the error is rethrown. Otherwise once the last transaction is closed, all associated listeners are notified of a "commitTransaction" event.

The active record object extends the transaction object.