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" or "commit" or "rollback"
   isolation = "read_uncommitted" or "read_committed" or 
      "repeatable_read" >
</cftransaction>
 
See also

cfinsert, cfprocparam, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cfupdate; "Commits, rollbacks, and transactions" in Chapter 19, "Introduction to Databases and SQL," in ColdFusion MX Developer's Guide

 
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.

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

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

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

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

 
Example
<p>CFTRANSACTION can be used to group multiple queries that use CFQUERY
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> 
--->
ACTION  
  Optional
 
Default value: "begin"
  • begin: the start of the block of code to execute.
  • commit: commits a pending transaction.
  • rollback: rolls back a pending transaction.
ISOLATION  
  Optional
 

ODBC lock type:

  • read_uncommitted: reads without regard for other transactions that are taking place. Sometimes called a `dirty read' because data that is read can be in a transitional state and therefore not accurate.
  • read_committed: uses shared locks to assure that no other transaction modifies rows that this transaction uses.
  • repeatable_read: same as read_committed, except that rows in the recordset are exclusively locked until the transaction completes. Due to high overhead, Macromedia does not recommend this isolation level for normal database access.
  • serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access.