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.

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.

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.
nested	Optional	true	This attribute specifies whether the cftransaction tag can be nested inside another cftransaction tag. If the attribute value is false and there is a parent cftransaction tag, ColdFusion generates an error.

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.
You can now nest cftransaction tags. Typically, ColdFusion 9 does not support nested transactions, but you can embed one cftransaction tag inside another. If you nest these tags, only the outermost cftransaction tag takes effect.

This feature lets you write functions that must run in a transaction without considering whether the function is called by code that is inside a cftransaction tag. Use a cftransaction tag in the function. If the calling code is in a transaction, the tag has no effect. If the calling code is not in a transaction, the tag starts the transaction.

The following code shows nested transaction tags.
```
<cftransaction> 
<cfquery name="iquery" datasource="dsn"> 
insert into region(regionid, regiondescription) values('111', 'YPR') 
</cfquery> 
<cftransaction> 
<cfquery name="iquery" datasource="dsn"> 
update region set regiondescription = 'new' where regionid='111' 
</cfquery> 
</cftransaction> 
</cftransaction>
```
Note: In a realistic situation, the second cftransaction and cfquery can be written in a CFC that are, in turn, called by the first cftransaction and cfquery by passing the regionid value.

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> 
--->

cftransaction

Description

Category

Syntax

See also

History

Attributes

Usage

Example

Example