cftransaction

<cftransaction 
   action = "begin" or "commit" or "rollback"
   isolation = "read_uncommitted" or "read_committed" or 
      "repeatable_read" >
</cftransaction>

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

Comments

myPitboss said on Apr 4, 2004 at 4:32 PM :

Please expand the explanation of (read_uncommitted,read_committed,
repeatable_read,serializable) for the CFTRANSACTION tag.

Chris.Velevitch said on Apr 19, 2004 at 12:47 AM :

Please explain what happens at the end of the transaction block,especially when there are no embedded cftransaction tags in the block.

Please explain what happens to the remainder of the block if an error occurrs.

halL said on Apr 19, 2004 at 7:32 AM :

For more detailed information on cftransaction, see the Devnet artilce "Controlling Database Transactions in ColdFusion MX" at http://www.macromedia.com/devnet/mx/coldfusion/articles/cftransaction.html.
I believe that this article anwsers both of the preceding questions.

No screen name said on Jun 9, 2004 at 8:04 AM :

How does this work under the hood? If I have 3 inserts within a cftransaction tag and the first 2 inserts succeed but the last one fails, how does CF roll it back?

Did CFautomatically do a Select to store the previous values of the record and then when the 3rd insert blows it updates the first 2 to the orginal status?

What if you were going to do a cascading delete and you had to delete through a hierarchy of 6 tables and the last one blows for some reason? And one of the deletes blew away 1000 rows. Did CF store that 1000 rows and then reinsert them? But what if an identity was bieng used that would blow the relationships?

I haven't tested this but I just don't see how this can work at all and would love an explanation of how it works. I did read the mentioned article which was good but didn't explain how CF handles the processing.

Mike.Schwartz said on Jul 6, 2004 at 11:35 AM :

In answer to the previous question, this is all handled entirely by the database. As such, there should be something in the docs that says that this requires a database that handles transactions. Also of interest would be what happens when you use this tag on db's which don't support transactions.

bizarrojack said on Jul 16, 2004 at 12:35 PM :

Can modules safely be called in a cftransaction? This pseudocode roughly describes my question scenario - assume each module has an insert or update cfquery.

<cftransaction>
<!--- suppose a query in this file is good --->
<cfmodule template=modify_table1 (attributes)>
<!--- suppose a query in this file should fail --->
<cfmodule template=modify_table2 (attributes)>
</cftransaction>

I have gotten at least one test to work like this; That is, the desired result is for the inserts or updates in the first module would not be committed unless the inserts or updates in the latter module should complete. I ask this because some other developers that I know have claimed that they have had problems with similar setups.

Is there some sort of trick to it?
I am specifically curious if it might depend on the type of error that comes from the second module.

McGarnagle said on Aug 4, 2004 at 9:20 AM :

Oracle docs on isolation levels:

http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96590/adg08sql.htm#2655

bizarrojack said on Aug 16, 2004 at 5:41 PM :

Is it possible to simply turn off autocommit, rather than be forced to comply with the constraints enforced by the context validator? I find that requiring the matching <cftransaction> and </cftransaction> is unreasonable.

For instance, if you have a multi-step process that needs to be part of a transaction, but could also be a required part of a longer transaction, It can't be written as a module that starts a transaction only if it isn't already in one, because putting </cftransaction> inside <cfif>'s will cause a context validator error.

It would be nice to be able to turn off autocommit if it wasnt already off, and not turn it back on until you had completed a parent transaction.

also, it seems peculiar that </cftransaction> requires a start tag, but <cftransaction> doesnt require an end tag.

Daniel Elmore said on Nov 13, 2004 at 10:05 PM :

I sniffed the packets generated from ColdFusion to a MS SQL 2000 server and found that CFTRANSACTION adds the following commands to query groups.

SET TRANSACTION ISOLATION LEVEL ... (attribute specifed in tag)
SET IMPLICIT_TRANSACTIONS ON

queries here

IF @@TRANCOUNT > 0 COMMIT TRAN

tinu8805 said on Nov 15, 2004 at 4:54 PM :

I don't understand cftransaction at all if compared to CF 5.0. I just recently upgraded our business app from CF 5 to CFMX 6.1. However, to achieve this, I had to commet out ALL <cftransaction action="commit"> or ...action="rollback" ... if I let them there, CFMX would ALWAYS generate a runtime error. I could reproduce that silly behavior and also put that problem into the user support forum about 2 years ago. Nobody not even Macromedia could solve the problem I reported.

Another forum member answered my question like this: He was playing with cftransaction and found it to autmatically rollback if an error was thrown and to automtically commit if the </cftransction> was reached. With this help, I commented out all <cftransaction action="..."> in my code.

For most of the time, I can accept that behavior. But I DO have some transaction where I deliberately want to rollback a transaction. This is now disabled becvause if I uncomment the <cftransaction action="rollback"> CFMX would generate an runtime error.

PS: To make it clear: If I completely remove all cftransaction tags, the application works in CFMX as it did in CF 5. So, CFMX throwing a runtiem exception was not because of an exception raised by the execution.

I use Sybase ASA 8 as my ODBC connected RDBMS.

Up to today, I have not yet received any better answer than from that forum member. And of course, my deliberate rollback is still out of order ... :-((

mcollins said on Nov 16, 2004 at 12:58 PM :

If you have not found resolution on the User Forums, I would suggest you contact Product Support directly.

No screen name said on Apr 1, 2005 at 5:17 AM :

tinu8805: Hi. same problem here, but i found the solution.
replace <cftransaction action="commit">
with <cftransaction action="commit" />

my guess is Macromedia fixed something in the tag parsing code somewhere else and it butterfly-effected and broke this. :)

EjayHire@hotmail.com

carehart said on Sep 16, 2005 at 7:52 AM :

The link offered above (in another comment) to some Oracle docs on transaction isolations fails. I found another place where those particular docs are available online:

http://www.mpi-sb.mpg.de/departments/d5/teaching/ss05/is05/oracle/appdev.920/a96590/adg08sql.htm#2655

There may be still better or more modern references to those oracle docs, but I'll leave that for others to explore.

No screen name said on Feb 13, 2006 at 1:53 AM :

Hi

the following document is about exception handling in cf mx, but it contains some information about cftransaction and a description of its options.

http://www.how2cf.com/files/papers/exceptions.pdf

mybe it helps understanding.