Hello, In our project we use: LLBL 2.5 SQL Server 2005 .NET 2.0

I believe we have a "Self-Service" type of LLBL project. We started using System.Transactions.TransactionScope but we noticed that all transactions are being promoted to Distributed Transactions. We would like to keep the transactions Light Weight since this should bring better performance and prevent configuration problems on the deployment.

I there a known way to prevent the promotion of the transaction to DTC in this situation?

Thanks, Paulo Santos

I did a small console app that loads an entity changes it and saves it inside the TransactionScope. I noticed with the Sql Profiler that 2 commands ran with different SPID's. And I believe that with Sql 2005 the transaction is promoted when 2 different connections are used.

Please make sure you run the latest service packs of the .net version you're using, I do recall there's a bug related to this in .net which was fixed in a service pack.

Also, make sure you're not debugging the application and accidentally are looking at a variable which triggers for example lazy loading. This creates a different connection and thus creates a distributed transaction.

If you want 1 connection, simply start a Transaction using a Transaction object, add the object to fetch to it, then fetch it and save it in the same Transaction, then commit it. This will not promote it.

I do wonder why you use transactionscope if you don't want transactions to be promoted. Why not use Transaction instead?

I have .NET 2.0 SP2, I tried running it from the exe and had the same result.

From MSDN (http://msdn.microsoft.com/en-us/library/system.transactions.transaction.aspx): "The System.Transactions namespace provides both an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class, in which transactions are automatically managed by the infrastructure. It is highly recommended that you use the easier implicit model for development."

We have used LLBL Transaction class, but we want to change the model to use TransactionScope so that we don't need to pass around transaction objects through several methods, just need to set the using(TransactionScope) on the top level and call all the necessary business layer methods without thinking of the transaction object anymore. It simplifies a lot the code. Is there an alternative to this class that allows for this programming style?

And I wonder if there is any configuration option on LLBL to enforce it to use the same connection on the same thread?

And finally, would upgrading to LLBL.2.6 help solve the problem?

Thanks

transactionscope is for grouping transactions as 1, not for an alternative transaction mechanism. So you still have to use a normal transaction, that can't be replaced with a transactionscope

I am surprised to read this. Are you saying TransactionScope doesn't allow implicit transactions?

I have a running example of how I can use TransactionScope to implicitly combine all self-service table accesses into one transaction. If I include the method call transactionScope.Complete() it does the changes on the database and if I do not include it, changes are rolled back.

And nowhere in the code I needed to define a Transaction object and associate to any entity object.

 using (TransactionScope transactionScope = new TransactionScope())
 {
                    AAAEntity aa = new AAAEntity(4);
                    aa.Name = aa.Name + ".";
                    aa.Save();

                    //transactionScope.Complete();
 }

Is there another class that we can use that achieves this same coding style of defining the transaction to be used in all DB accesses in one place only?

LLBLGen Pro will under the hood start a transaction, if necessary. If it doesn't, it leaves it to the RDBMS (atomic insert statement for example). All connections are governed by the transaction scope, so all transactions over those connections are also governed, but transactionscope doesn't start a transaction manually.

You have to take into account that when an entity rolls back due to a transaction, it also rolls back its fields (e.g. synced fk fields are rolled back). What you're doing won't do that. So you have to write code like you always would, i.e. with transactions and then wrap them in transactionscope if you want to group them as a single transaction.

I can see that you don't advice us to use TransactionScope the way we do. Until now we didn't find any problems related to the transaction behaviour, we just had the Distributed Transaction dependency problem.

But is there a possibility to have a coding solution where we can define a scope for a transaction and not worry about adding entities to the transaction inside every method , just like TransactionScope allows with implicit transactions? Maybe by creating our own class and adding custom code to the LLBL generated classes?

Thanks.

pauloya wrote:

I can see that you don't advice us to use TransactionScope the way we do. Until now we didn't find any problems related to the transaction behaviour, we just had the Distributed Transaction dependency problem.

But is there a possibility to have a coding solution where we can define a scope for a transaction and not worry about adding entities to the transaction inside every method , just like TransactionScope allows with implicit transactions?

The TransactionScope doesn't really do anything, it just sits there collecting votes what to do with the transactions it controls. So all transaction started inside a scope are governed by it, but that leads to a distributed transaction if you have multiple ado.net transactions. To avoid that, you have to pass around the 'Transaction' instance (the instance of the generated Transaction class) and add the entities to persist to that. When you're done, commit that transaction.

That's making sure you use 1 transaction and don't get distributed transactions. You then also don't need the scope.

Maybe by creating our own class and adding custom code to the LLBL generated classes? Thanks.

You could use a static variable somewhere marked with [ThreadStatic] (to avoid multi-threaded usage) which is the transaction object to use and use that in all your code, but that's IMHO leading to less maintainable code, as you rely on a static global variable for transaction management, which could lead to problems if that static parameter got changed. But on the other hand it might suit your needs.

But in sort, what you are after, an indirect way to do things in some hidden transaction, that's not possible/present, you always have to actively add the entities to the transaction like you should, so things get executed in the active transaction.

You could create a scope like class for this though, which contains the static variable (marked with TreadStatic) which creates a scope for Transaction. In your code, simply obtain the static variable from the scope class, if it's not there, create a local Transaction object, otherwise use that ThreadStatic marked one. This way you don't have to pass around the Transaction instance and you can control with a scope class where which transaction is used.

If you need help with this, please let us know.

Thanks for the tips. I think a custom class is a good solution for us, also replacing TransactionScope by it should be easy if has the same methods as transactionScope. In my point of view having an implicit scope simplifies a lot the code.

I'm thinking of a class named TrasactionImplicit, which needs to have some kind of a singleton per thread. The class constructor should set the instance and the dispose should clear it, then we can use "using()". Also the commit rollback should be handled with the Complete() method just as TransactionScope does.

I'm just not sure where to hook the code that for every Entity (and SP call), behind the scenes, checks if TrasactionImplicit is on and invokes the Transaction.Add(). For entities, should I use the custom code area at the contructor for CommonEntityBase? What about stored procedure calls?

Thanks for the help.

pauloya wrote:

Thanks for the tips. I think a custom class is a good solution for us, also replacing TransactionScope by it should be easy if has the same methods as transactionScope. In my point of view having an implicit scope simplifies a lot the code.

I'm thinking of a class named TrasactionImplicit, which needs to have some kind of a singleton per thread. The class constructor should set the instance and the dispose should clear it, then we can use "using()". Also the commit rollback should be handled with the Complete() method just as TransactionScope does.

I'm just not sure where to hook the code that for every Entity (and SP call), behind the scenes, checks if TrasactionImplicit is on and invokes the Transaction.Add().

There are a couple of caveats: - if you nest scopes, you have to make sure that a nested scope isn't disposing the already created transaction, but the outer one does - you still have to add entities manually to the transaction.

You also have to manually check. The problem is that if you DONT have a global (thread local) transaction, you want to create one locally and use that during the scope. If you ask the global class to give you a new one if there's no live global transaction, you'll create a new one every time. You can solve this by obtaining the transaction once locally.

var transactionToUse = TheGlobalClass.GetLiveTransaction(); // transactionToUse is a new one if there wasn't a global one, otherwise the global one

// do normal transaction work here

TheGlobalClass.Commit(transactionToUse); // this commits the transaction if there's no global one, or checks whether this was the outer scope. if not, it ignores it, otherwise it commits.

For entities, should I use the custom code area at the contructor for CommonEntityBase? What about stored procedure calls?

Thanks for the help.

Nothing changes, you have to do transaction work as you'd do normally. The only thing that does change is the way you create and dispose transactions.

Nothing changes, you have to do transaction work as you'd do normally. The only thing that does change is the way you create and dispose transactions.

Our main objective is avoid writing the code that creates and assigns transactions to entities and Stored Procedures. If we aren't able to create a class that does that for us, we will probably keep using TransactionScope. Even with the added trouble of ensuring MSDTC is enabled on our deployment scripts.

From our tests we couldn't find a scenario where using TransactionScope without creating LLBL Transactions explicitly didn't work as expected. We tried with entities and with SP calling.

You have to take into account that when an entity rolls back due to a transaction, it also rolls back its fields (e.g. synced fk fields are rolled back).

I don't think this is a problem for us, all entities used are declared inside the using(TransactionScope){} scope so once the transaction is rolled back (which happens by exiting that scope without invoking the complete method) the entities are already out of scope and not usable.

Thanks for all the help!