For a long time, I had an application that was getting the following ColdFusion CFTransaction:
Data source dsn.name verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same. The specific sequence of files included or processed is: C:\...
This was an old application that has several versions running at a given time including two on the local development machine - one for internal development and one for client beta testing. As such, I assumed that the above error was being caused by DSN conflicts between the two local applications. Since our DSN values are stored in per application variables, the only thing that I could think of to explain this error was that somehow, one application was overwriting the other application's DSN mid-page. That way, somehow, a transaction might have one query with one DSN and then a second query with another DSN.
To get to the bottom of this, I poured over the code looking for ways in which ColdFusion would let this happen. The application names were totally different and file-location dependent. No possible Application name hijacking. I went over and over the queries in the CFTransaction and nothing seemed to be suspect. I couldn't figure anything out. Sadly, I never got to the bottom of this problem.
Then, yesterday, Spencer Stickland, who has since taken over the project, told me that he finally cracked the problem. After digging through numerous forums and developer resources, he came across this Adobe ColdFusion Tech Note: ColdFusion MX 6.1 and MX 7: Incorrect cftransaction error thrown. In the tech note, it explains that if you use CFExit in the middle of the CFTransaction tag there is a problem with the DSN not be released from the thread; this doesn't cause a problem with the current request, but will cause the above error to be thrown on subsequent ColdFusion requests that contain different DSN information.
I guess the lesson here is that you shouldn't put too much control logic inside of a CFTransaction tag.