TransactionScope Transactions not commiting
- From: Jim <Jim@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Aug 2007 11:06:03 -0700
I'm trying to implement something here that seems like it aught to work, but
is not. I've done some extensive searching around the web, and have not
found anything directly relavant. I am trying to solve two basic issues here:
1 - I want to use System.Transactions.TransactionScope to manage single and
distributed Transactions;
2 - Transactions may be nested, and some nested Transactions may (or may not
be) using the same SQL Connection.
The idea here is to support a "Dynamic connection string" solution, where
the data for a given object lies on an undetermined server. This works by
having the DAO function of my object get its SQL Connection from a static
"Connection Manager", that takes a type name and looks it up in a database
table, and dtermines the Connection String to use to create the connection to
that object's data, like this:
public class MyClass
{
public static void Insert( MyClass obj)
{
// Get an IDbConnection
IDbConnection cn = ConnectionManager.GetSqlConnection(obj.GetType());
... // Do the DAO work
}
So it seems then I could do something like this:
using(TransactionScope ts = new TransactionScope())
{
MyClass obj = new MyClass();
... // Populate data members
MyClass.Insert( obj);
ts.Complete();
}
Nothing apparently wrong here, but I've run into some problems, the most
distressing of which is the fact that if you comment out the ts.Complete()
line, the transaction still commits! Under the covers, here is what goes on:
1 - ConnectionManager gets a request for an IDbConnection for a specific
Data Type. It will look this up in a dataase we'll call DevSql2. So, it
creates a connection to DevSql2 to get that connection string. It does this
inside of a USING block with a new TransactionScope with it's creation option
set to "Suppressed", so that the DB call to look up the Connection String is
not involved in a Transaction.
When the Conenction String is retreived, a new IDbConnection is created for
that connection string, and returend to the caller (The MyClass::Insert()
finction, in this case), where it's used.
Now, it just so happens that in this particular case, the Connection Strings
table and the table I'm inserting into both live on the same database, and
therefore have the same Conenction String. Either TransactionScope or the
DTC doesn't seem to like this, as it throws up an exception when I try to
open the 2nd connection to DevSql2 (throwing back an error saying that the
Transaction has already been eithe rimplicitly or explicitly committed or
aborted.) I worked around this issue by recycling the connections. That is,
my Connection Manager doesn't actually instantiate a new Connection if one is
already open for that Connection String.
The code runs with no errors, but if I try to make an Insert not commit (by
failing to call Complete() inisde the TransactionScope), the thing still
commits, and I don't understand why. Here's an oversimplified example of
what I'm doing:
void main()
{
// Create a root TransactionScope
using( TransactionScope tr1 = new TransactionScope())
{
// Get an existing record
MyClass obj = MyClass.GetById( 1);
Console.WriteLine( obj.ToString());
// Create a nested Transaction
using( TransactionScope ts2 = new TransactionScope())
{
// Create and save a new object
MyClass obj2 = new MyClass();
int id = MyClass.Insert(obj2);
Console.WriteLine( "Inserted new record {0}", id);
// Should be able to retrieve the uncommitted record within the
Transaction
obj = MyClass.GetById( id);
Console.WriteLine( obj.ToString());
// Don't call Complete() - Implicit failure
}
// Should NOT be able to retrieve the object now...
obj = MyClass.GetById( obj.ID);
Console.WriteLine( obj.ToString()); // Sure enough, it's there!?!?!
ts.Complete(); // Throws an exception indicating that the Transaction
was aborted.
}
}
The implicit transaction (tr2) should have been rolled back when it's USING
block ended. It apparently was, as the Complete() call on ts fails. Isn't
there a way to inspect ts prior to calling Complete() to see if it will
complete? It seems kind of dirty to only find out when an exception gets
thrown.
When I query the database after runnign this, sure enough, that reocrd
created in ts2 is there. It seems like it should not be. How do I get that
to roll back?
Any help on this would be greatly appreciated. FYI, I'm trying to
incorporate busienss objects with DAO into the CSLA framework. There are
some articles otu there on CSLA and some issues with MSDTC on SQL Server
2000, but those are stricly related to performance. Godo performance is
nice, but at the moment, I'd just be glad to ahve the thing WORK, period.
Thanks!
Jim
.
- Prev by Date: RE: OleDBException : DB_E_OBJECTOPEN
- Next by Date: OleDb DataReader Close is hanging
- Previous by thread: RE: OleDBException : DB_E_OBJECTOPEN
- Next by thread: OleDb DataReader Close is hanging
- Index(es):
Relevant Pages
|
