cftransaction rollback problem

[whatchamakeofit]

Hi. I'm trying to do a DB transaction, and I'm having trouble trying
to do an explicit rollback. If I, for example, misspelled
LAST_INSERT_ID, the cfcatch will catch the error, but the
<cftransaction action="rollback" /> won't perform a rollback of the
first query "insert1." I'm running CF 6 and MySQL. Here's my code:

<cftry>
<cftransaction>
<cfquery name="insert1" datasource="test">
insert into bmw1 (fname) values ('#form.fname#')
</cfquery>
<cfquery name="getid" datasource="test">
select LAST_INSERT_ID() as newid
</cfquery>
<cfset newid = getid.newid>
<cfquery name="insert2" datasource="test">
insert into bmw2 (lname, bmw1_id) values ('#form.lname#', #newid#)
</cfquery>
<cfquery name="insert3" datasource="test">
insert into bmw2 (mname, bmw1_id) values ('#form.mname#', #newid#)
</cfquery>
</cftransaction>
<cfcatch type="database">
bad transcation
<cftransaction action = "rollback"/>
</cfcatch>
</cftry>


Please advise. Thanks!

[Nick Voss]

Hi.  I'm trying to do a DB transaction, and I'm having trouble trying
to do an explicit rollback.  If I, for example, misspelled
LAST_INSERT_ID, the cfcatch will catch the error, but the
<cftransaction action="rollback" /> won't perform a rollback of the
<cftry>
        <cftransaction>
                <cfquery name="insert1" datasource="test">
                        insert into bmw1 (fname) values ('#form.fname#')
                </cfquery>
                <cfquery name="getid" datasource="test">
                        select LAST_INSERT_ID() as newid
                </cfquery>
                <cfset newid = getid.newid>
                <cfquery name="insert2" datasource="test">
                        insert into bmw2 (lname, bmw1_id) values ('#form.lname#', #newid#)
                </cfquery>
                <cfquery name="insert3" datasource="test">
                        insert into bmw2 (mname, bmw1_id) values ('#form.mname#', #newid#)
                </cfquery>
        </cftransaction>
        <cfcatch type="database">
                bad transcation
                <cftransaction action = "rollback"/>
        </cfcatch>
</cftry>
Please advise.  Thanks!

I think you need your transaction code outside your try/catch.
Otherwise your original transaction is completing and then the catch
is firing and its told to rollback a transaction, but there no longer
is one because it's complete.

Nick

[whatchamakeofit]

found out the error was because the mysql engine i was using was
myisam which doesn't support transactional processing. i had to
convert my mysql table to Innodb.