CFTRY/CFCATCH and CFTRANSACTION

[ej_ist]

CF Version: 2016,0,03,300466 (Standard)

DB Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

I don't know if this is a bug or a feature, but when a query fails within a cftry/cfcatch block that is surrounded by a cftransaction tag, and that query is executed again in the cfcatch block (i.e. for timeout or deadlock purposes), all queries that ran before the cftry/cfcatch block are rolled back. For instance:

<cftransaction>

<cfquery name="query_1" datasource="#my_ds#">

     INSERT INTO Table1(Field1, Field2)

     VALUES(

          <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val1#"/>,

          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#val2#"/>

     )

</cfquery>

<cfquery name="query_2" datasource="#my_ds#">

     UPDATE Table2

     SET Field1 = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#val3#"/>,

     WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#id#"/>

</cfquery>

<cftry>

<cfquery name="query_3" datasource="#my_ds#">

     INSERT INTO Table3(Field1, Field2)

     VALUES(

          <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val4#"/>,

          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#val5#"/>

     )

</cfquery>

     <cfcatch>

<cfquery name="query_3" datasource="#my_ds#">

     INSERT INTO Table3(Field1, Field2)

     VALUES(

          <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val4#"/>,

          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#val5#"/>

     )

</cfquery>

     </cfcatch>

</cftry>

<cfquery name="query_4" datasource="#my_ds#">

     INSERT INTO Table4(Field1, Field2)

     VALUES(

          <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#val6#"/>,

          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#val7#"/>

     )

</cfquery>

</cftransaction>

If query_3 fails for any reason, the query is executed again in the cfcatch and processing continues. However, query_1 and query_2 are rolled back. Separately, query_4, which occurs after the cftry/cfcatch block is not rolled back.

Granted, this is a simple example and in the real world you would add conditional processing and other logic to the cfcatch to rethrow the error if that query failed. I wanted to make it as simple as possible, though, to illustrate the point.

The implication here is that you could be defeating the purpose of cftransaction and end up with orphaned records that may have been inserted after the cftry/cfcatch. Moreover, if this condition occurred towards the end of the request and you redirected the user to a "thank you" page or something similar after the DB operations, the user would be blind to the fact that there were any issues with the request.

???

-Eric

[haxtbh]

I would recommend reading the documentation for cftransaction - Adobe ColdFusion 9 * cftransaction

What is happening is expected.

If a cfquery tag generates an error within a cftransaction block, all cfquery operations in the transaction roll back.

The idea is to prevent orphaned records, if one fails, so do all of them. I wouldn't be re running them in a cfcatch, what if it fails again?. You should detect the issue and re run the lot after the transaction or give feedback to the user to retry.

Using the example you gave should produce the correct results. Query 1 and 2 run, 3 fails, and catches and runs again with a pass, query 4 runs and the transaction is posted.

In the scenario you posted you shouldn't get any orphans. If the catch failed as well then the whole transaction fails with no changes.

[ej_ist]

haxtbh,

Thanks for your reply.

I fully understand the documentation, but I think this is different because it is allowing orphaned records to be written simply by inserting a try/catch block inside of a transaction block. If the catch query threw an error (or you explicitly added a rethrow in the catch), all of the transactions would be rolled back anyway (and processing would stop, thus preventing transactions from being committed after the catch). You should be able to run a query again in a catch statement if the original query fails for reasons such as timing out or deadlocking. And you should be able to do this without previous transactions rolling back.

Also, to clarify, this only occurs when the catch error type is "database" - any other error type does not cause the problem. For instance, to force a database type, try inserting a varchar value into an int field. A deadlock or timeout on the database server would also cause it.

I can recreate the issue every time with the posted code if I force a database error on query 3: queries 1 and 2 are rolled back, query 3 is completed (with the catch version) and query 4 is completed.

I have also tried the default CF database driver for SQL Server - same results.

-Eric

[Steve Sommers]

Not sure if this will help but have you tried to include explicit COMMIT and ROLLBACK calls within the cftransaction block? It may produce different results.

If that fails I would restructure your code and wrap the entire cftransaction block within the cftry block - include a loop block around that and you're set:

for(local.i=1; local.i<=2; local.i++){

  try{

    transaction{

      query(1);

      ...

      query(4);

      break;

    } //transaction

  }catch(e){

    if(local.i==2){

      rethrow();

    }

  }

}

[ej_ist]

Thanks Steve,

I've tried explicit commits and rollbacks at various places in the code - nothing.

Your looping solution might work on a small scale like in my example, but that was just an example to illustrate the issue. In reality, this behavior is affecting an enterprise application with queries across many include files, with catch blocks specific to the query that may fail (mostly because of deadlocks).

The whole reason we did this was because of the amount of deadlock issues we were getting after moving to SQL 2014+. We spent a fair amount of time trying to solve these on the MS SQL side, but to no avail. In the end, just running the query again seemed like the best solution, but we could not have known at the time that such a fix would affect data integrity.

I think our best bet is just going to be removing the try/catch blocks altogether. Receiving a bunch of deadlock errors is still preferable to uncommitted transactions and orphaned records.

-Eric

[Steve Sommers]

From what you describe I would seriously consider rethinking the business logic or coding of the business logic so-as to not have the transaction include queries across many include files. I prefer to keep the inserts and updates as simple and together as possible because I know I'll be the one trying to debug future issues. Also deadlocks should be an exception so this tells me that some SQL schema optimization might need to be looked at as well. Lastly you'll want to keep the transactions blocks quick; having this via multiple include files may be a big part of the deadlock issues you are encountering. It sounds like a support nightmare.

I'm sure this is easier said than done. Good luck.

[ej_ist]

Thanks Steve.

This is a large, heavily-used university application written in the Fusebox framework that has been poked and prodded for about 15 years now. Personally, I would like to rewrite the thing in .NET, but given the size of the application and funding concerns, that probably isn't going to happen.

To ease the bleeding for now, we are going to write out the deadlock try/catches and convert the most problematic queries to stored procedures to see if that helps with the overhead any.

I still don't think that transactions should be rolled back if a query can be successfully executed a second time in a catch, but that is a concern for the CF architects to deal with if at all.

-Eric

[BKBK]

ej_ist wrote:

I still don't think that transactions should be rolled back if a query can be successfully executed a second time in a catch, but that is a concern for the CF architects to deal with if at all.

These are different rules. Transactions are designed to roll back if a query cannot be successfully executed the first time.

The result you get is probably due to the fact that your code contains elements that contradict each other. Consider

<cftransaction>

Query1

Query2

Query3 - fails

Query4

</cftransaction>

By design, the transaction rolls back when Query3 fails. Now consider your code design:

<cftransaction>

Query1

Query2

<cftry>

Query3 - fails

<cfcatch>

Query3

</cfcatch>

</cftry>

Query4

</cftransaction>

Here, as in the previous example, Query3 fails within the transaction. By design, a failed query within a transaction implies that the transaction has to roll back. But, here, the try/catch is forcing the execution to proceed to the catch block, rather than to roll back as expected. Hence the ambiguous result.

Cftransaction behaves similarly to try/catch. Also remember that the default action of <cftransaction> is "begin". It seems as if the failed query in the try/catch makes ColdFusion to implicitly set a rollback savepoint, and to begin anew from the point where Query3 failed. It tries to do its best when you command it to stop and go at the same time.

If you want finer tuning, you should use the actions "commit" and "rollback" to instruct ColdFusion. For example, something like

<cfset toRedo = False>

<cftransaction>

    <cftry>

    Query1

    Query2

    Query3

    Query4

    <cftransaction action="commit" />

    <cfcatch>

        <cftransaction action="rollback" />

        <cfset toRedo = True>

    </cfcatch>

    </cftry>

</cftransaction>

<cfif toRedo>

    <cftransaction>

        <cftry>

        Query1

        Query2

        Query3

        Query4

        <cftransaction action="commit" />

        <cfcatch>

            <cftransaction action="rollback" />

        </cfcatch>

        </cftry>

    </cftransaction>

</cfif>

You cannot assume that Query3 - or any other - will run the second time around. After all it failed before.