CFTransaction & Multiple data sources

[CFGumby]

We have the need to update to multiple data sources under CFTransaction. We have two MSSQL databases so I was able to create a view in one to the other and that works (unless someone knows of a problem that could cause). But we also have a MSAccess db that gets hit too under the same CFTransaction. CF does not allow you to have multiple data sources under one CFTransaction. If all the tranasction control is on the db side, why does CF care if there are different data sources?
Thanks.

[Newsgroup_User]

Hi there,

This has been copied and pasted straight from the CFMX7 manual -
"Within 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."

Well, CF does not care if there are more than one data sources. It just
wants you to complete the transaction (either commit or rollback) on one
database before doing anything with the others. Why? My personal take on
this is, because you want to perform a 'transaction' implying the whole
work you are about to perform is a single logical unit of the process.
As a result, the transaction must have the end result of either success
of failure, nothing inbetween.

People with a more deeper knowledge of CF transactions are welcome to
jump in and comment.

Thanks.
- Milind Joshi

CFGumby wrote:
> We have the need to update to multiple data sources under CFTransaction. We
> have two MSSQL databases so I was able to create a view in one to the other and
> that works (unless someone knows of a problem that could cause). But we also
> have a MSAccess db that gets hit too under the same CFTransaction. CF does not
> allow you to have multiple data sources under one CFTransaction. If all the
> tranasction control is on the db side, why does CF care if there are different
> data sources?
> Thanks.
>

[CFGumby]

>This has been copied and pasted straight from the CFMX7 manual<
A better question might be "why do we have to commit before writing to another datasource"? If we have to commit part of the transaction, why use cftransaction? To get an atomic transaction, we need data in both data surces to be either committed or rolled back.

>wants you to complete the transaction<
>single logical unit of the process.<
Again, the updates to both data sources are the complete transaction.

My thought is that it is not allowed because CF only remembers one data source per cftransaction therefore can only send one commit or rollback instruction. If that's the case, it sounds like a simple enhancement.

Thanks for your feedback.

[JMGibson3]

"If all the tranasction control is on the db side, why does CF care if there are different data sources". Never tried, does he really care? What's the error message. Perhaps he's just trying to keep you out of trouble by preventing you from relying on CFTRANSACTION in a situation that's actually impossble. As you acknowledged, CFTRANSACTION simply passes the fact on to the DB manager who will (depending on how well/if they do the job) lock/commit/rollback the set of queries, until the </cftransaction> is encountered. If you need to coordinate MSSQL and ACCESS you'll have to do it yourself with CFTRY/CATCH. What are you coordinating? Is one or the other not real time so you could use DTS?.

[Kronin555]

What you need in order to have a transaction span multiple databases is 2-phase commit.

Coldfusion doesn't support 2-phase commit.

You could do 2-phase commit if you drop down into Java and ensure that:
1) you're talking to databases that support 2-phase commit
- and -
2) the drivers you're using to talk to those databases support 2-phase commit.

http://en.wikipedia.org/wiki/2-phase_commit

[CFGumby]

We are a CF shop, not Java. The question is why can't it be done, not how can we work around it. We have a feature in CF (cftransaction) that won't let us do what we need to do and we don't know why. We've come up with several work arounds but the best, least costly is if cftransaction would do it for us. It may be a lost cause at this point. Maybe this was more of a theoretical question, and the answer might just be as simple as "because".

Thanks for your response.

[Kronin555]

As I said in my original post, in order for a transaction to span multiple databases, you need to do what's called "2-phase commit" or "distributed transactions". This is not a trivial thing to do, nor is it something that is often needed (especially in Coldfusion, which is 99% of the time talking to a single database).

Why do you need to do 2-phase commit? and why isn't it trivial?
"2PC is an acronym for 2 Phase Commit. This is a protocol by which data being committed to a database is committed in two phases. In the first phase, the transaction processor checks that all parts of the transaction can be committed. In the second phase, all parts of the transaction are committed. If any part of the transaction indicates in the first phase that it cannot be committed, the second phase does not occur. ODBC does not support two-phase commits."
http://docs.openlinksw.com/virtuoso/twopcimplementation.html

I don't think Access supports 2-phase commit. Access isn't really intended for the kinds of things that need 2-phase commit, which Microsoft readily admits (hence MS SQL Server).

> My thought is that it is not allowed because CF only remembers one data source per cftransaction therefore can only send one commit or rollback instruction. If that's the case, it sounds like a simple enhancement.

No, this isn't allowed because distributed transactions is a non-trivial thing. This isn't all handled on the database side, as you reference. If you're dealing with one datasource, yes, it does. Once you pull in a 2nd datasource, you need something that can talk to all databases involved (a transaction coordinator) and 1) check to make sure everything can be committed safely then 2) commit everything (hence 2-phase commit).

Basically, you're trying to do something that's non-trivial, you don't understand why it's non-trivial, so you're getting frustrated. I realize you're not a Java shop. I was simply trying to explain to you why it was non-trivial, and a method to work around that if you so desire.

[CFGumby]

Kronin555, I understand it's not a "trivial" thing. I never said it was although you made that very clear. Can you point me to some documentation that explains that you need 2PC for two data sources? You've explained everything else but that. If CF can control the commits and rollbacks (hence taking the burden of 2PC away from the data base, or ODBC), where does it state that CF can't do so if it's for more than one data source? Also, I'm not really as frustrated as you might assume and rewritting our syetem in Java would not be "trivial".

And Stressed_Simon, if we take Access out of the equation, CF still won't allow it. And as far as using the correct SQL, I wish it were as simple as that but this is a complex modular system with updates in multiple cfm's that can be included or run independantly. Simply using a storred procedure or one cfquery is not an option.

[Kronin555]

A very good overview of Transaction Processing, including as it relates to Distributed Transactions and where 2-phase commit fits into that:
http://www.subrahmanyam.com/articles/transactions/NutsAndBoltsOfTP.html

Stressed_Simon is correct, however. Access doesn't support this kind of thing. So if you need to do this, the first thing you're going to need to do to enable this going forward is get off of Access.

I wasn't very clear when I said you can drop down to Java to do this. I didn't mean you have to rewrite your whole application, you just need to write the parts that deal with transactions spanning multiple databases in Java. Coldfusion doesn't do anything with transactions at all. The reason it limits you to one datasource is because it's transaction capabilities are very basic. It doesn't have a transaction manager built into the platform. It simply sets the autocommit level of the database connection when a transaction is opened, then either commits or rolls back that connection's transaction at the end. It (coldfusion) doesn't actually manage or monitor anything. In order to get distributed transactions working, you need a transaction manager (as outlined in the link I provided).

Here's another link for you:
http://docs.openlinksw.com/mt/xamt.html

[CFGumby · Accepted solution]

You don't know how long I've wanted to get away from Access. Maybe you've given me the final staw to break management's back for us to finally break free of Access (they only change the minimum they can get away with). And as for the two data bases, again it's not needed and maybe now I can get management to bite the bullit and we can get it changed. I thank you for your insight and Java would be a solution but it's a solution for a problem that shouldn't be a problem.

Thanks to all who responded.

[CFGumby]

>Never tried, does he really care? What's the error message.<
The error message is "Data source DSN1 verification failed.The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same".

>Perhaps he's just trying to keep you out of trouble by preventing you from relying on CFTRANSACTION in a situation that's actually impossble<
I can use all the help I can get but if thats the case, CF would prevent you from using a cflocation inside a cftransaction without a rollback or commit. A cflocation will rollback your changes.

>you'll have to do it yourself with CFTRY/CATCH<
If we have to coordinate it with a try/catch block, why use cftransaction. If the updates to the second ds fail, we would have to manually roll back the changes to the first ds.

>Is one or the other not real time so you could use DTS?.<
These are real time transactions and the two data sources must be in synch.

Thanks for your comments.

[Stressed_Simon]

You are never going to get this to work with an Access Database. Access does not honour the T-SQL you need for this. Also, why use cftransaction? You are using MSSQL, so just use the correct SQL to do it instead of rellying on CF to get you out of a hole.