Skip to main content
E
ej_ist
Participating Frequently
February 8, 2017
Question

CFTRY/CFCATCH and CFTRANSACTION

  • February 8, 2017
  • 1 reply
  • 4957 views

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

This topic has been closed for replies.

1 reply

H
haxtbh
Inspiring
February 8, 2017

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.

E
ej_istAuthor
Participating Frequently
February 8, 2017

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

S
Steve Sommers
Legend
February 8, 2017

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();

    }

  }

}

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices