Distributed Transactions and Connection Manager

amir.gheibi posted on Tuesday, December 15, 2009

Hi,
I have a very interesting scenario to deal with.
Here are the classes I have.
- A CommandBase class called "MyEngine".
I'm using a remote server data portal. And the DataPortal_Execute is marked with "TransactionScope".
- A read only collection called "ActivitySourceList"
- An editable collection called "Activities"

When the "MyEngine" class runs on the server, in DataPortal_Execute "ActivitySourceList" is loaded from DB, a few new child objects is added to the "Activities" and "Activities" is Saved.

[Transactional(TransactionalTypes.TransactionScope)]
protected override void DataPortal_Execute()
{
using (ConnectionManager ctx = ConnectionManager.GetManager("MYDB"))
{
ActivitySourceList srcList = ActivitySourceList.GetData();
Activities acts = Activities.NewCollection();
this.AddNewActs(acts);
acts.Save();
}
}

I'm using an Oracle DB and I'm not writing any DB related code inside my Business Objects. I've created separate Data Layer classes which I have no intention to make it a generic one. I'll always use Oracle.

Loading the "ActivitySourceList" must use the same Transaction as saving the "Activities". That's because I have to keep the ActivitySource rows locked (to control concurrency) in the database while I do the rest of my processing back in the code. (I achieve this using Oracle Ref Cursors. The returned Ref Cursor becomes a DataReader in the code and in this case creates locks on those records it's returning) And as soon as the same Transaction is committed, the lock is automatically released.

The challenge is that "ActivitySourceList" and "Activities" use different Data Access classes and in each one I use the "using (ConnectionManager..." to get the connection and do the job. Since those "using" blocks become internal to the main "using" block in the MyEngine class, the whole thing should use only one connection and consequently one transaction. (right?) But as soon as a DataReader is passed from the ActivitySourceList's data layer to ActivitySourceList, I get an error from Oracle which simply means "A commit has happened". (which automatically disposes the Ref Cursor and I can't read anything from DataReader) But where? The execution hasn't even reached the end of the outermost "using (ConnectionManager..." block (in the MyEngine class). Is this a right approach?
Also, when I trace the retrieval of the ActivitySourceList, I realize it uses the SimpleDataPortal. Shouldn't it use TransactionalDataPortal?

RockfordLhotka replied on Tuesday, December 15, 2009

I don't think Oracle supports TransactionScope transactions do they? If they don't (and I'm pretty sure they don't) then TransactionScope falls back to using DTC transactions.

You'll probably have to either use the DTC, or use ADO.NET transaction objects manually. Tell CSLA to use manual transactions and create your own OracleTransaction object (or whatever it is called).

Fortunately there's a Csla.Data.TransactionManager class that should make it pretty easy to share that transaction object (and its associated connection object) throughout your app.