Cftransaction doesn't seem to be working quite right. Still deleting a record when purposely testing a error in the mix.

Hello, cftransaction doesn't seem to be doing what I assume it would when purposely causing a error. The first 2 delete query's will remove 1 record of each and then proceed to the 3rd query where <cfcatch> then throws the message. I assumed that cftransaction would not allow any record updates if there was an error.

<cfquery name="qResults">
SELECT records
FROM table
</cfquery>

<cftransaction action="begin">

<cftry>

    <cfloop query="qResults">

    <cfquery>
    DELETE
    FROM table1
    WHERE record_id = #qResults.record_id#
    </cfquery>

    <cfquery>
    DELETE
    FROM table2
    WHERE record_id = #qResults.record_id#
    </cfquery>

    <cfquery>
    DELETE
    FROM table3
    WHERE record_id = #qResults.record_id#
    </cfquery>

    </cfloop>

    <cfcatch type="any">
        <cftransaction action="rollback"/>
        Dang, an error...
    </cfcatch>

</cftry>

</cftransaction>

Open in new window

brihol44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
Correct, if there is an error in those delete statements, the database should rollback to before the page was run - no changes

Are you saying that the records have been deleted from the database even though there was an error?
0
LajuanTaylorCommented:
@brihol44 - Try the following...

<cfquery name="qResults">
SELECT records
FROM table
</cfquery>


<!---if any errors occur in working with the database handle them in the catch block that follows--->
<cftry>
        
        <!---Begin data deletion across several tables. If any of the delete queries fail, roll back the changes that were made. --->        
         <cftransaction action="begin">
		 
				<!--- cfquery operations that all must complete successfully --->
				<cfloop query="qResults">
					<cfquery>
						DELETE
						FROM table1
						WHERE record_id = #qResults.record_id#
					</cfquery>

					<cfquery>
						DELETE
						FROM table2
						WHERE record_id = #qResults.record_id#
					</cfquery>

					<cfquery>
						DELETE
						FROM table3
						WHERE record_id = #qResults.record_id#
					</cfquery>
				</cfloop>				
				
		<!---If the program has reached this point, no database errors have occurred and we can now commit all the record deletions --->        
        <cftransaction action="commit" />
            
		</cftransaction> 
    
    
    <!---There was a problem with a query above --->        
    <cfcatch type="any">            
		Dang, an error...
		
		<!---tell the databse to rollback anything done to the tables since we began the transaction--->
		<cftransaction action="rollback" />
                
    </cfcatch><!---end catch block--->
            
</cftry><!---end try block--->

Open in new window

0
brihol44Author Commented:
I tried that and it seems to error with The tag <cftry>  requires an end tag. . Yes, Gdemeria, if I refresh the page I'm loosing 1 record each for tables 1 & 2 still. Seems like it's not respecting cftransaction at all so I'm not sure.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

LajuanTaylorCommented:
@brihol44 - Are you using SQL Server or MySQL?

Have tested against your database server using only TSQL to see if the rollback is working at the DB level?
0
brihol44Author Commented:
Using MySQL. No I haven't, I'm not sure how to go about that.
0
gdemariaCommented:
Weird, are you seeing the error message?   Oh Dang... that you have there?   To be sure the code is getting to that spot?

@Lajuan, the cftransaction has to go outside the cftry... in your example you have the rollback outside of the cftransaction tags.

I do agree with Lajuan's suggestion to verify a rollback is working at all... perhaps a simple update statement with a rollback without the catch and try...

It is really curious.
0
brihol44Author Commented:
Yep, I see the message and I didn't include it in my example but I have the record count of table 1, table 2 and table 3. Tables 1 & 2 decrease by 1 each time I refresh the page. Checking the db or mysql I see the count decrease there as well.  I'll try the additional approach of removing the try/catch next...
0
_agx_Commented:
Which version of MySQL and is it an MYISAM/ISAM or InnoDB? IIRC, past versions of MySQL only supported transactions for InnoDB.  If you're using MYISAM/ISAM, that could explain the results you're seeing.  Since it auto commits each statement, a transaction would have no affect.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brihol44Author Commented:
innoDb was it. Works fine now. Thx everyone!
0
LajuanTaylorCommented:
@_agx_ - Good info about MySQL. My current test environment is CF9 and SQL Server 2012...
0
gdemariaCommented:
what was the solution?
0
LajuanTaylorCommented:
@gdemaria - @_agx_ pointed out that
 If you're using MYISAM/ISAM, that could explain the results you're seeing.  Since it auto commits each statement, a transaction would have no affect.

I'm assuming that @brihol44 switched to the using the newer (innoDb) storage engine for MySQL, which resolved the issue of cftransaction not working as expected.

innoDb supports transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. Valid link as of: 11/22/2015
 https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html
0
_agx_Commented:
@LajuanTaylor - Heh, since I rarely use MyISAM I'd almost forgotten it doesn't support transactions until I saw this question ;-)

@gdemaria - If you're curious, here's a small example that demonstrates the difference. Notice record_id=1 is still deleted from table1, despite the ROLLBACK, since table1 is type MyISAM.

create table table1 ( record_id int) ENGINE MyISAM;
create table table2 ( record_id int) ENGINE InnoDB;

insert into table1 values (1),(2),(3);
insert into table2 values (1),(2),(3);

start transaction;
delete from table1 where record_id  = 1;
delete from table2 where record_id  = 1;
rollback;

-- record is deleted from table1, but NOT table2 (InnoDB)
select * from table1;
select * from table2;

Open in new window

0
gdemariaCommented:
Got it, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.