Link to home
Start Free TrialLog in
Avatar of Jacob Leis
Jacob Leis

asked on

Lucee & <cftransaction.... /> WITAF?

By all means, after thoroughly reviewing Lucee's documentation, this should* work.  But of course, that isn't the case.

<cftransaction action="begin" >
    <cfloop query="getDonations">
        <cfquery name="updateDonation" datasource="myDSN">
            UPDATE  donations
            SET     firstname = "Seth"
            WHERE   id = #getDonations.id#
        </cfquery>
        <cftransaction action="commit" />
        <cfif getDOnations.currentrow MOD 2 EQ 0>
            <h4>A: #getDOnations.currentrow#</h4>
            <cftransaction action="rollback" />
        <cfelse>
            <h4>B: #getDOnations.currentrow#</h4>
        </cfif>
    </cfloop>
</cftransaction>

Open in new window


Hopefully a second set of eyes will catch what I'm missing.  Why won't this work?  Every time I run it, the update IS NOT rolled back.

Your insight and help is greatly appreciated.  Thanks in advance
Avatar of _agx_
_agx_
Flag of United States of America image

It's not working because the code commits after each update.  Then tries to do a rollback.  You can't rollback a transaction after it's already committed.
Avatar of Jacob Leis
Jacob Leis

ASKER

I thought so too, but the code in my question matches the documentation that I found.  I tried the following as well with the same result (all commit):

<cftransaction action="begin" >
    <cfloop query="getDonations">
        <cfquery name="updateDonation" datasource="#stateIndex#org_landingpages">
            UPDATE  donations
            SET     firstname = "Luke"
            WHERE   id = #getDonations.id#
        </cfquery>
        <cfif getDOnations.currentrow MOD 2 EQ 0>
            <h4>A: #getDOnations.currentrow#</h4>
            <cftransaction action="rollback" />
        <cfelse>
            <h4>B: #getDOnations.currentrow#</h4>
            <cftransaction action="commit" />
        </cfif>
    </cfloop>
</cftransaction>

Open in new window

It should also be noted that the MySQL engine is InnoDB, as opposed to MYISAM/ISAM, since the latter do not support transactions.
What documentation are you looking at? That's not the way transactions usually work. Though some db's do support more granularity, normally, you run two or more statements and either COMMIT or ROLLBACK everything in one shot.

Honestly, I'm not sure what the expected behavior is for that example. It's very odd, in that it alternates a commit and rollback.  That's not a typical real-world scenario

<cfset getDonations = queryNew("")>
<cfset queryAddColumn(getDonations, "ID", [5,8,9,10])>

    <cfoutput query="getDonations">
        <br>RUN UPDATE 
        <cfif getDOnations.currentrow MOD 2 EQ 0>
            <h4>ROLLBACK: #getDOnations.currentrow#</h4>
        <cfelse>
            <h4>COMMIT: #getDOnations.currentrow#</h4>
        </cfif>
    </cfoutput>

Open in new window


Output

RUN UPDATE
COMMIT: 1

RUN UPDATE
ROLLBACK: 2

RUN UPDATE
COMMIT: 3

RUN UPDATE
ROLLBACK: 4
It's certainly not real world.  I'm just trying to prove that it works.
EDIT: Well, I'm pretty sure what you're trying isn't valid.  The rules for transactions are governed by you dbms. If you look at the docs for SQL server, a single BEGIN TRAN, followed by multiple commit/rollback statements isn't valid.  There has to be an open transaction before you can do anything with it.  The CF code only opens 1 transaction, so I'd guess only one of the actions succeeds.  To achieve that level of granularity, you'd need nested or named transactions - which the current code doesn't have. That's probably why it doesn't work.

Unless you actually have a need for that scenario, a better test would be what I mentioned above.  Use a single commit/rollback as you typically would in a real application.  

<cftransaction action="begin" >
    <cfloop query="getDonations">
        <cfquery name="updateDonation" datasource="myDSN">
            UPDATE  donations
            SET     firstname = "Seth"
            WHERE   id = <cfqueryparam value="#getDonations.id#" cfsqltype="cf_sql_integer">
        </cfquery>
    </cfloop>
    <cfif someTestVariableSaysRollback> 
            <cftransaction action="rollback" /> 
     <cfelse>
        <cftransaction action="commit" />
     </cfif>
</cftransaction>

Open in new window

>> If you look at the docs for SQL server
Just saw you're using MySQL, but the general rules are going to be similar.  I'd have to run tests to verify what you're seeing but ... there needs to be an open transaction before you can doing anything with it.  If the CF code only opens 1 transaction, then commits/rolls it back, I wouldn't expect subsequent calls to commit/rollback to do anything.  

>> that the MySQL engine is InnoDB
Ok, good. That was going to be my next question.
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I suppose it comes down to the fact that MySQL is a dumpster fire...
Eh.. MySQL's not so bad. Doesn't work in SQL Server either. Only difference is SQL Server yells at you that the extra commit/rollback are missing an opening transaction.  So MySQL's doing the right thing, granted it should throw an error letting you know the commands aren't valid for the current state..