I will have a need to save changes to several different tables in several different databases all within a single transaction of some sort.  If any optimistic concurrency conflict occurs in any of the tables in any of the different databases, I want to roll back all attempted changes in all tables in all databases.

My research on the internet indicates that transactions will not work across multiple databases.  Does StrataFrame have a way of handling this scenario?  Will I need to write my own code based on my own solution, or can StrataFrame handle this out of the box?

Sam

I appreciate the time it took to give me these ideas.  Are you aware of anybody actually using a transaction across multiple databases with StrataFrame?  I am only a beginner and it will take me some time to absorb everything you suggested, but based on the little bit I know now, I am not sure yet if your ideas will work.  If I ever figure it out, I will let you know what I found.

Sam

One of our technical writers forwarded a feedback e-mail you sent referencing this post. Rather than responding via e-mail, I thought a response in the thread would be best to keep everyone on the same page.

The simplest approach here is basically the one Edhy outlined in his original post. Strataframe has in-built transaction functions to create, roll-back, or commit transactions on a per-datasource basis.  Each individual transaction will be (in essence) limited to a single connection string, which will inherently be limited to a single database. However, you can start multiple transactions on multiple datasources at once, and work with those multiple transactions in parallel. 

When you do the test to determine a roll-back (i.e. checking business rules, or catching an exception) you would simply roll-back all of your transactions rather than just the one. In regards to concurrency, the in-built concurrency checking within the BOs should still function while a transaction is under way. As such, you can start your transactions, do your work, save all of your BOs (to all of their different databases), and if you get through cleanly, commit your transactions.  If you get hit with a concurrency exception in the middle of any of the saves, you can handle it as desired then (including rolling back your transactions, if that is what you prefer.)  The key is to always rollback or commit all of your transactions at once, rather than committing/rolling back one and then continuing on with the others further down the road.  Otherwise you could get some of your data committed and some rolled back.

So, in short, this does require some manual work (managing multiple transactions), but it is a relatively trivial amount.  In fact, you could always add some shared methods to a base class to StartTransactionOnAllDataSources(), RollBackTransactionsOnAllDataSources(), or CommitTransactionsOnAllDataSources(), which removes even that small bit of manual work from then on.

Most of the intricacies of dealing with transactions, concurrency, multiple databases, etc. can get complicated in theory, but becomes more clear when you start to work through the problems and see the framework's capabilities first hand. In that vein, I would echo Edhy's suggestion to create a sample project to test some of these theories out to your satisfaction.