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 Introduction to Databases and SQL in ColdFusion MX Developer's Guide

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.

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.

Usage

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

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:

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

View comments in LiveDocs