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]

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.