Adobe ColdFusion 8

cftransaction

Description

For enterprise database management systems that support transaction processing, instructs the database management system to treat multiple database operations as a single transaction. Provides database commit and rollback processing. See the documentation for your database management system to determine whether it supports SQL transaction processing.

Category

Database manipulation tags

Syntax

<cftransaction 
    action = "begin|commit|rollback|setsavepoint"
    isolation = "read_uncommitted|read_committed|repeatable_read"
    savepoint = "savepoint name">
</cftransaction>

Note: You can specify this tag's attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag's attribute names as structure keys.

See also

cfinsert, cfprocparam, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cfupdate; "Commits, rollbacks, and transactions" 4 in the ColdFusion Developer's Guide

History

ColdFusion 8: Added the setsavepoint value to the action attribute. Added the savepoint attribute.

Attributes

Attribute

Req/Opt

Default

Description

action

Optional

begin

  • begin: The start of the block of code to execute.
  • commit: Commits a pending transaction.
  • rollback: Rolls back a pending transaction.
  • setsavepoint: Saves a specific state within a transaction

isolation

Optional

 

Isolation level, which indicates which type of read can occur during the execution of concurrent SQL transactions. The possible read actions include dirty read, in which a second SQL transaction reads a row before the first SQL transaction executes a COMMIT; non-repeatable read, in which a SQL transaction reads a row and then a second SQL transaction modifies or deletes the row and executes a COMMIT; and phantom, in which a SQL transaction reads rows that meet search criteria, a second SQL transaction then generates at least one row that meets the first transaction's search criteria, and then the first transaction repeats the search, resulting in a different result set.

  • read_uncommitted: Allows dirty read, non-repeatable read, and phantom
  • read_committed: Allows non-repeatable read and phantom. Does not allow dirty read.
  • repeatable_read: Allows phantom. Does not allow dirty read or non-repeatable read.
  • serializable: Does not allow dirty read, non-repeatable read, or phantom.

savepoint

Optional

 

The name of the savepoint in the transaction. Setting savepoints lets you roll back portions of a transaction. For example, if your transaction includes an insert, an update, and a delete, and you set a savepoint after the update, you can roll back the transaction to exclude the delete.

Usage

If you do not specify a value for the action attribute, automatic transaction processing proceeds as follows:

  • If the cfquery operations within the transaction block complete without an error, the transaction is committed.
  • If a cfquery tag generates an error within a cftransaction block, all cfquery operations in the transaction roll back.

If you do not specify a value for the isolation attribute, ColdFusion uses the default isolation level for the associated database.

By using CFML error handling and the action attribute, however, you can explicitly control whether a transaction is committed or rolled back, based on the success or failure of the database query. In a transaction block, you can do the following:

  • Commit a database transaction by nesting the <cftransaction action = "commit"/> tag in the block.
  • Roll back a transaction by nesting the <cftransaction action = "rollback"/> tag in the block.

(In these examples, the slash is an alternate syntax that is the equivalent of an end tag.)

In a transaction block, you can write queries to more than one database, but you must commit or roll back a transaction to one database before writing a query to another.

To control how the database engine performs locking during the transaction, use the isolation attribute.

The cftransaction tag does not work as expected if you use the cfthread tag in it to make query calls.

Example

<p>The cftransaction tag can be used to group multiple queries that use 
the cfquery tag into one business event. Changes to data that is requested
by the queries are not committed to the datasource until all actions within
the transaction block have executed successfully.
<p>This a view-only example. 
<!--- 
<cftransaction>
    <cfquery name='makeNewCourse' datasource='Snippets'>
    INSERT INTO Courses
        (Number, Descript)
    VALUES
        ('#myNumber#', '#myDescription#')
    </cfquery>

    <cfquery name='insertNewCourseToList' datasource='Snippets'>
    INSERT INTO CourseList
        (CorNumber, CorDesc, Dept_ID,
        CorName, CorLevel, LastUpdate)
    VALUES
        ('#myNumber#', '#myDescription#', '#myDepartment#',
        '#myDescription#', '#myCorLevel#', #Now()#)
    </cfquery>
</cftransaction> 
--->

You can set savepoints at the completion of insert, update, and delete actions of a transaction. You then use error handling logic to determine whether it is necessary to roll back to a previous savepoint.

Example

<!--- This example performs batch processing of withdrawals --->
<!--- from a bank account. The withdrawal amounts are stored --->
<!--- in an array. --->
<!--- There is a CFC named bank.cfc whose contains appear --->
<!--- after the example. --->

<cftransaction>
    <!--- Get the account balance. --->
    <cfinvoke component="bank" method="getBalance" 
        returnvariable="getacctbalance" accountnum=1>

<cfloop index="withdrawnum" from="1" to="#ArrayLen(withdrawals)#">
    <!--- Set a savepoint before making the withdrawal. --->
    <cfset noxfer = "point" & #withdrawnum#>
    <cftransaction action = "setsavepoint" savepoint = "#noxfer#"/>

    <!--- Make the withdrawal. --->
    <cfinvoke component="bank" method="makewithdrawal" 
        returnvariable="getacctbalance" accountnum=1 
        withdrawamount="#withdrawals[withdrawnum]#">    

    <!--- Get the account balance. --->
    <cfinvoke component="bank" method="getBalance" 
        returnvariable="getacctbalance" accountnum=1>
    
    <!--- If the balance is negative, roll back the transaction. --->
    <cfif getacctbalance.balance lt 0>
        <cftransaction action="rollback" savepoint="#noxfer#" />
    </cfif>
    </cfloop>
</cftransaction>

<!--- The bank.cfc contains the following:

cfcomponent>
    <cffunction name="getBalance" access="public" returntype="query">
        <cfargument name="accountnum" type="numeric" required="yes">
            <cfquery name="getacctbalance" datasource="testsqlserver">
                SELECT * FROM dbo.mybank 
                WHERE accountid = #accountnum#
            </cfquery>
        <cfreturn getacctbalance>
    </cffunction>
    
    <cffunction name="makewithdrawal" access="public" returntype="query">
        <cfargument name="accountnum" type="numeric" required="yes">
        <cfargument name="withdrawamount" type="numeric" required="yes">        
            <cfquery name="withdrawfromacct" datasource="testsqlserver">
                UPDATE dbo.mybank SET balance = balance - #withdrawamount#
                WHERE accountid = 1
            </cfquery>
            <cfinvoke method="getBalance" returnvariable="getacctbalance" 
        accountnum=1>
        <cfreturn getacctbalance>    
    </cffunction>
</cfcomponent>
--->