How to handle database transaction failures in ColdFusion?

Question

I am getting a database time out exception, but not consistently. Some times it executes and some times it fails. I would like to write a log file on database failure. My query is enclosed with CFTRANSACTION.

So my question is how can I detect the TRANSACTION failure so that I can write a log? Also, where do I put try/catch block; inside the CFTRANSACTION or put the CFTRANSACTION inside the try/catch block?

Answer 1

This might be a starting point. Here is a link to simulate a long running query in SQL Server: Forcing a query timeout in SQL Server

<cftransaction>

    <cftry>
        <cfquery datasource="mydatasource" timeout="1">
            <!--- problem query --->
        </cfquery>

        <cfcatch type="Database">
        <!--- write to log --->
            <cftransaction action="rollback" />
        </cfcatch>
    </cftry>

</cftransaction>