Hi,
A few weeks ago we had a thread about *atomic* queries and
transaction/rollback processing and how a few databases handled it (or
didn't handle it). I was wondering if the ColdFusion tag "<CFTRANSACTION>"
would supply rollback capabilities, but didn't do any testing.
Well...I just did some testing and yes, indeed, it does provide that
capability.
First, I wrote 600+ lines of CF, containing 3-5 queries, including update
and insert queries, and a bunch of other logic (no HTML in the code). I then
ran some tests that I knew would crash one or more of the queries. Sure
enough, if the third query crashed, the writes for the first and second
queries were committed to the database, but the data that was to be inserted
by the 3rd query was munged. This is what I expected.
Now, to test the CFTRANSACTION tag, I wrapped all of the code in it, logic
and queries. I then ran the same test, trying to crash the 3rd query. Well,
CF still generated an error when the 3rd query crashed, but the changes that
would have resulted from query 1 and 2 were not committed to disk!
I ran my tests using MS Access, but will test on SQL Server and, if I get
around to it, MySQL on my Linux box. MS Access has built-in functions to
handle transaction rollbacks, but I don't know if those are invoked by the
CFTRANSACTION tag or if CF is doing the work. In other words, I wonder, if
CF is doing the work, whether it will supply this capability externally to
MySQL, which lacks it.
Here are the related CF docs, if anyone cares:
-------------------
CFTRANSACTION
Use CFTRANSACTION to group multiple queries into a single unit.
CFTRANSACTION can also provide rollback processing.
Syntax
<CFTRANSACTION ISOLATION="ODBC_lock">
...
</CFTRANSACTION>
ISOLATION
Optional. ODBC lock type. Valid entries are:
Read_Uncommitted
Read_Committed
Repeatable_Read
Serializable
Usage
Any queries executed with CFQUERY and placed between <CFTRANSACTION> and
</CFTRANSACTION> tags are treated as a single transaction. Changes to data
requested by these queries are not committed to the database until all
actions within the transaction block have executed successfully. If an error
occurs in a query, all changes made by previous queries within the
transaction block are rolled back.
Use the ISOLATION attribute for additional control over how the database
engine performs locking during the transaction.
-----------------------
Jack
____________________________________________________________________
--------------------------------------------------------------------
Join The NEW Web Consultants Association FORUMS and CHAT:
Register Today at: http://just4u.com/forums/
Web Consultants Web Site : http://just4u.com/webconsultants
Give the Gift of Life This Year...
Just4U Stop Smoking Support forum - helping smokers for
over three years-tell a friend: http://just4u.com/forums/
To get 500 Banner Ads for FREE
go to http://www.linkbuddies.com/start.go?id=111261
---------------------------------------------------------------------
