StrataFrame Forum

Transactions covering multiple databases?

http://forum.strataframe.net/Topic27701.aspx

By Sam Tenney - 7/20/2010

I am trying to learn what the StrataFrame Application Framework can accomplish in a rather complicated proposed application.  I am confused about transactions.  The application will have multiple SQL tables in Multiple databases but they are all housed in a single SQL server.  I will be using SQL Server 2008.

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

By Edhy Rijo - 7/20/2010

Hi Sam,



The way SF handles the Transactions is very simple and friendly to the developers. Let me give you some points:



1st:

You can have multiple tables in multiple databases and even multiple servers. All this is handle by SF using a DataSourceKey parameter in the connection and then using this DataSourceKey value in each Business Object (BO). So you can create connections to your databases and then when you create a Business Object, you use the DatasourceKey for the connection you want the BO to use.



For example, using VB, in your AppMain.vb SetDataSource() method you could have something like this:



'-- Visual Fox Pro

DataLayer.DataSources.Add(New VfpDataSourceItem("VFP", My.Settings.VFPConnectionString))



'-- MySql Datasource

DataLayer.DataSources.Add(New Business.Data.MySqlDataSourceItem("MySQL", My.Settings.MySqlConnectionString))



The above code creates 2 connections, one to a Visual FoxPro database using the DataSourceKey "VFP" and another for a MySQL database, using the DataSourceKey "MySQL". In my respective Business Objects I have either "VFP" or "MySQL" in the DataSourceKey property.



2nd:

Using Transactions is a matter of using the DataSourceKey and a Transaction Key



Private Sub SaveWithTransactions()

Try

'-- Main Transaction

MicroFour.StrataFrame.Business.BusinessLayer.TransactionBegin(Me.BizCustomers1.DataSourceKey, "MainTransactionKey", Data.IsolationLevel.ReadUncommitted)

Me.BizCustomers1.Save(True, "MaintransactionKey")



'-- Now try with a child BO

Try

MicroFour.StrataFrame.Business.BusinessLayer.TransactionBegin(Me.BizVehicles1.DataSourceKey, "ChildTransactionKey", Data.IsolationLevel.ReadUncommitted)

Me.BizVehicles1.Save(True, "ChildTransactionKey")



'-- Commit the Child BO Transaction

MicroFour.StrataFrame.Business.BusinessLayer.TransactionCommit(Me.BizVehicles1.DataSourceKey, "ChildTransactionKey")

Catch ex As Exception

'-- Rollback the child transaction

MicroFour.StrataFrame.Business.BusinessLayer.TransactionRollback(Me.BizVehicles1.DataSourceKey, "ChildTransactionKey")



'-- Here you need to find a way to cause the main TRY to error so the main transaction will also be rollback

End Try



'-- Commit the Main Transaction

MicroFour.StrataFrame.Business.BusinessLayer.TransactionCommit(Me.BizCustomers1.DataSourceKey, "MainTransactionKey")

Catch ex As Exception

'-- Rollback the main transaction

MicroFour.StrataFrame.Business.BusinessLayer.TransactionRollback(Me.BizCustomers1.DataSourceKey, "MainTransactionKey")

End Try

End Sub





Disclaimer:

I have not tested the above. You will need to test in your environment and made needed adjustments. This is just a pseudo code to give you an idea on how this could be accomplish.
By Sam Tenney - 7/20/2010

Thanks Edhy.

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

By Edhy Rijo - 7/20/2010

You are welcome Sam!



Please do not get intimidated by all this, after you print it out and review it with a simple sample project you will see it is really easy.



I have not worked with SF transactions working with multiple databases. I have used them with a single database, but based on the parameters used by the transactions methods, you need to pass the DataSourceKey which will internally identify which connection the BO will use.



I suggest you create a simple form with 2 tables using 2 databases and test your scenario. I currently don't have time to get into building that sample, but the whole experience will help you understand SF and what I have found working with SF is that most of the SF approach to solve developers problems are much simpler than what it looks like, of course in your case being new to SF it may take you longer, but the faster you start and post back any block road, the faster any of us will help you out. Hehe
By Greg McGuffey - 7/20/2010

I'm checking with the SF developers on this. Hopefully I get an answer tomorrow.
By Dustin Taylor - 7/22/2010

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.