CODEWITHSUNDEEP
databasecoldfusiontransactions
Reuben Romandy
Reuben Romandy

Reputation: 53

What is the behavior of Coldfusion cftransaction tag when multiple databases are accessed?

The coldfusion documentation, (I'm using CF8) states:

Changes to data that is requested by the queries are not committed to the datasource until all actions within the transaction block have executed successfully.

But it also states:

In a transaction block, you can write queries to more than one database, but you must commit or roll back a transaction to one database before writing a query to another

I have multiple transactions in my code base which access 2 databases for both selects and update/inserts. The code assumes that all queries will either succeed or they will all be rolled back. But I don't know if that is true based upon the line in the docs that says: "but you must commit or roll back a transaction to one database before writing a query to another".

What is the behavior if a write to the first database succeeds, then the subsequent write to another database fails? Will the first be rolled back?

Upvotes: 0

Views: 2210

Answers (2)

J.T.
J.T.

Reputation: 2616

What the documentation means is that you must put a <cftransaction action="commit"> after the queries to one database before you can move on to using another datasource. It will throw an error if it detects that you have <cfquery> tags with different datasources inside of a transaction without using the commit. See your database documentation for exact transaction support as the CFML via the database driver is only sending in transaction commands on your behalf, it is not responsible for their execution or behavior. Enable JDBC logging in your database to see this in action.

Won't work:

  <cftransaction action="begin">

    <cfquery datasource="foo">
    select * from foo_test
    </cfquery>

    <cfquery datasource="bar">
    select * from bar_test
    </cfquery>

  </cftransaction>

Will work

<cftransaction action="begin">

    <cfquery datasource="foo">
    select * from foo_test
    </cfquery>

  <cftransaction action="commit"><!-- Commit before switching DSNs --->

   <cfquery datasource="bar">
    select * from bar_test
    </cfquery>

</cftransaction>

If you are using three part names for multiple database access through a single datasource, the transaction control will work. 

<cftransaction action="begin">

    <cfquery datasource="foo">
    INSERT INTO foo_test ( id )
    VALUES ( 70 )
    </cfquery>

    <!-- insert into the bar database via the foo datasource --->
    <cfquery datasource="foo">
    INSERT INTO bar.dbo.bar_test (id )
    VALUES ( 'frank' ) <!-- Fails because not an int and the prior insert into foo is rolled back -->
    </cfquery>

</cftransaction>

Upvotes: 4

William Greenly
William Greenly

Reputation: 3989

The default behaviour for CFTransaction is that all writes will be rolled back if there is an exception anywhere within the transaction block. So if one query fails, all queries are rolled back.

This is only if the database supports commits and rollbacks based on Transaction Control Language, a subset of SQL.

However you can granulary control how CF transaction works, beyond the default behaviour, including features such as savepoints and nested transactions.

Upvotes: -2

Related Questions