Rollback with CFTRANSACTION

You may be surprised to find out that software documentation is not always clear or complete. Shocking, I know. Even when it is, sometimes I just have to see things in action for myself. This is one of those cases.

A question came up today at work about the <cftransaction> tag. (Yes, we still use ColdFusion. There are dozens of us. Dozens!) The question was if the tag would automatically rollback changes or if you needed to explicitly type <cftransaction action="rollback"> to rollback the changes. I have always assumed the latter.

So here is my test case. First I created 2 test tables in the database for testing each with a test record.

CREATE TABLE test_table_1 (
  tt1_record_id NUMBER(3) NOT NULL,
  tt1_text VARCHAR2(100),
  CONSTRAINT pk_test_table_1 PRIMARY KEY (tt1_record_id)
);

CREATE TABLE test_table_2 (
  tt2_record_id NUMBER(3) NOT NULL,
  tt2_text VARCHAR2(100),
  CONSTRAINT pk_test_table_2 PRIMARY KEY (tt2_record_id)
);

INSERT INTO test_table_1 (
  tt1_record_id,
  tt1_text
)
VALUES (
  1,
  'Blue'
);

INSERT INTO test_table_2 (
  tt2_record_id,
  tt2_text
)
VALUES (
  1,
  'Triangle'
);

And now the ColdFusion code. This block of code first outputs the text from the database. Then it updates the first table while intentionally failing to update the second table. Lastly, it displays the text from the database.

<cfquery name="variables.qryBefore" datasource="#application.dsn#">
SELECT
  tt1_text, tt2_text
FROM
  test_table_1,
  test_table_2
WHERE
  tt1_record_id = tt2_record_id
</cfquery>

<p>
    <cfoutput>
        Before: 
        #variables.qryBefore.tt1_text#
        #variables.qryBefore.tt2_text#
    </cfoutput>
</p>

<cftry>
    <cftransaction>
        <cfquery name="variables.qryUpdate1" datasource="#application.dsn#">
            UPDATE
                test_table_1
            SET
                tt1_text = 'Red'
            WHERE
                tt1_record_id = 1
        </cfquery>

        <cfquery name="variables.qryUpdate2" datasource="#application.dsn#">
            UPDATE
                test_table_2
            SET
                tt2_text = 'Square'
            WHERE
                foo = bar
        </cfquery>
    </cftransaction>
    <cfcatch type="any">
        <p>
            Database update failed.  Changes should have been rolled back.
        </p>
    </cfcatch>
</cftry>

<cfquery name="variables.qryAfter" datasource="#application.dsn#">
SELECT
  tt1_text, tt2_text
FROM
  test_table_1,
  test_table_2
WHERE
  tt1_record_id = tt2_record_id
</cfquery>

<p>
    <cfoutput>
        After: 
        #variables.qryAfter.tt1_text#
        #variables.qryAfter.tt2_text#
    </cfoutput>
</p>

The database values remained the same. The first table was updated but when the second table failed to update, the changes were rolled back. I could have added <cftransaction action="rollback"> to the <cfcatch> block and gotten the same result.

So why use the rollback command then? You may only want to rollback to a certain savepoint. Another use case I have found for it is unit testing. In my test, I want to execute the entire method even the database code. However, before I leave the <cftransaction> block, I rollback my changes:

<cfif this.testing>
 <cftransaction action="rollback" />
</cfif>