Hello

We are using LLBLGen Pro to connect to a SQL Server 2005 database. We are using System.transactions to manage our transactions.

Since we are only using a single SQL Server 2005 database, we would prefere to use the Lightweight Transaction Manager (LTM), however we have see cases where our transactions have been promoted to a distributed transaction using the OleTx Transaction Manager.

Is there any way that we can prevent LLBLGen from promoting our transactions?

As far as I can read, there are two ways our transaction can be promoted.

1) We open a second database connection. 2) We serialize the transaction object across app domains.

We only use transactions on the server, and we don't serialize the LLBLGen entities between client and server, so I don't think 2) is the case.

Regarding 1) Opening two or more connections to the same database in the same transaction will promote the transaction. Is there any way that we can make sure that LLBLGen does not open more than one database connection in a single transaction? We are using the DataAccessAdapter to connect to the database.

Thank you for your help.

Regards Anders K. Olsen

how do you start your transactions? Using a using statement with TransactionScope?

We do it like this:

using(TransactionScope scope = new TransactionScope()) using (DataAccessAdapter adapter = new DataAccessAdapter(true)) { // Do the processing

scope.Complete(); }

I have looked a little more at the code where we have detected the transaction promotion.

It looks like we are using two DataAccessAdapters like this:

public void FirstMethod()
{
using(TransactionScope scope = new TransactionScope())
using (DataAccessAdapter adapter = new DataAccessAdapter(true)) 
{
  // Use adapter to fetch EntityCollection from database.

  // Call another class that also accesses the database.
  MyObject obj = MyMethod();

  scope.Complete();
}
}

// Some method in another object.
public MyObject MyMethod() 
{
  // In this case, no transaction is started, but of course it will continue to 
  // use the existing transaction.
  using (DataAccessAdapter adapter = new DataAccessAdapter(true)) 
  {
     // Fetch EntityCollection from database using this adapter
  }
}

I have a stacktrace of where the promption occurs (promotion fails on our test-server):

Message: HandlingInstanceID: e470d4d5-5ac3-4c97-a55b-e64dbacc7333
An exception of type 'System.Transactions.TransactionManagerCommunicationException' occurred and was caught.
------------------------------------------------------------------------------------------------------------
01/24/2007 16:44:17
Type : System.Transactions.TransactionManagerCommunicationException, System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.
Source : System.Transactions
Help link : 
Data : System.Collections.ListDictionaryInternal
TargetSite : Void ProxyException(System.Runtime.InteropServices.COMException)
Stack Trace :   at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
   at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.OpenConnection()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath)
   at Energinet.DPSApp.Server.Business.Common.TreeStructureManager.ConstructDayTree(DateTime date)
   at Energinet.DPSApp.Server.Business.Common.TreeStructureManager.GetDayTree(DateTime date)
   at Energinet.DPSApp.Server.Business.Common.TreeStructureManager.GetTimeSeriesType(DateTime date, Int32 id)
   at Energinet.DPSApp.Server.Business.Translators.TimeSeriesHeaderTranslator.FillTimeSeriesHeader(TimeSeriesHeader to, TimeSeriesEntity from)
   at Energinet.DPSApp.Server.Business.Translators.TimeSeriesHeaderTranslator.ToBusiness(TimeSeriesEntity from)
   at Energinet.DPSApp.Server.Business.ActorPlan.TradePlanManager.GetConsumptionPlans(DataAccessAdapter adapter, Actor actor, DateTime date)
   at Energinet.DPSApp.Server.Business.ActorPlan.TradePlanManager.GetConsumptionPlans(Actor actor, DateTime date)
   at Energinet.DPSApp.Server.ClientServices.ActorPlan.ActorPlanService.GetConsumptionPlans(ActorDTO actorDTO, DateTime date)

In the stacktrace, the method "GetConsumptionPlans" corresponds to "FirstMethod()" - this is the method where the transaction scope is created and the first DataAccessAdapter is created.

The method "ConstructDayTree" corresponds to "MyMethod". This is the method where the second DataAccessAdapter is created.

Is it possible for these two DataAccessAdapters to share the same database connection? A lot of processing happends inbetween, so it would not be easy for us to pass the a reference to the DataAccessAdapter between the two methods.

Thank you for your help.

Regards Anders

Would these threads help: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6575 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4670

I have taken a look at the threads, and I'm not sure they help me in this case.

I'm starting a System.Transactions transaction, so LLBLGen should use this transaction instead of starting its own. That part seems to work just fine.

I'm creating the DataAccessAdapter using the "new DataAccessAdapter(true)" constructor. The documentation says that the DataAccessAdapter will not close an opened connection, which is probably what I want.

I want it to open one database connection and continue to use this connection. Will DataAccessAdapter.KeepConnectionOpen=true help me achieve this? Even between different DataAccessAdapter instances?

Is there any way I can trace/log/debug when LLBLGen opens and closes a database connection? Then I can try to see if the problem is in our code.

Regards Anders

Will DataAccessAdapter.KeepConnectionOpen=true help me achieve this? Even between different DataAccessAdapter instances?

KeepConnectionOpen will keep the connection opened of the DataAccessAdapter instance inhand. But if another DataAccessAdapter is sharing the same connection, it might close the connection for you, so you need to set its property too.

Is there any way I can trace/log/debug when LLBLGen opens and closes a database connection? Then I can try to see if the problem is in our code.

You can use the SQL Profiler to trace database connections.

The connection isn't shared among DataAccessAdapter instances. One connection per DataAccessAdapter instance, though the keepconnectionopen flag forces the instance to keep open the connection between actions on the adapter, as Walaa said.

So if you want to have 1 connection for ALL your unittests (i don't see why, but let's say you want it), you should use 1 DataAccessAdapter instance for all tests. Though I'd use one per test.

So it is not possible to share the same connection between two DataAccessAdapters.

What I would like is a thread-local database connection. Perhaps with some reference counting, so that when the last DataAccessAdapter is disposed, the connection is disposed.

The problem occur in the business layer on the server. In this concrete example, we are loading entities from the database. We need to enrich these objects with some other data from a memory cache. However the first time that the cached data is requested, we need to populate the cache. The population of the cache requires a database access, and then we have the problem.

This is not a unittest issue. I agree with you, that we should not need to share DataAccessAdapters between unittests. The problem was just discovered during testing, because for some reason distributed transactions failed on our test-server. I haven't investigated that issue yet, because I would prefere to avoid distributed transactions when it is not necessary, and since we are only using one database, it should not be necessary.

I guess I will have to look into creating a Thread-local DataAccessAdapter that we can use.

Regards Anders

You could share a connection between adapters, when you derive a class from DataAccessAdapter and override CreateNewPhysicalConnection which then for example instead of creating a new one, returns the connection you passed in via a property to the object. Be careful with this though.

I guess I will have to workout something along those lines.

I will have to make the connection local to each thread however. Otherwise I could run into some serious concurrency issues.

Perhaps I could use the base CreateNewPhysicalConection to create the connection the first time a DataAccessAdapter is created. Then I can do some reference counting, and when the last DataAccessAdapter is disposed, I can close the connection.

I don't think it will be difficult to work something out. I will look into it during next week.

Thank you for your answers.

Regards Anders

This seems to do the trick:

DataAccessAdapter factory:

    public class DPSDataAccessAdapterFactory
    {
        internal const string ThreadSlotName =
            "Energinet.DPSApp.Server.Utility.DataAccess.DPSDataAccessAdapter.ThreadSlot";

        public static DPSDataAccessAdapter GetDataAccessAdapter()
        {
            LocalDataStoreSlot slot = Thread.GetNamedDataSlot(ThreadSlotName);
            DPSDataAccessAdapter adapter = Thread.GetData(slot) as DPSDataAccessAdapter;
            if (adapter == null)
            {
                adapter = new DPSDataAccessAdapter(true);
                Thread.SetData(slot,adapter);
            }
            adapter.AddReference();
            return adapter;
        }
    }

DataAccessAdapter subclass:

    public class DPSDataAccessAdapter : DataAccessAdapter
    {
        private int refCount = 0;
        private bool isDisposed = false;

        internal DPSDataAccessAdapter(bool keepAlive) : base(keepAlive)
        {
        }

        internal void AddReference()
        {
            if (isDisposed)
            {
                throw new DPSServerException("Unable to add reference to a disposed DPSDataAccessAdapter");
            }
            refCount++;
        }

        protected override void Dispose(bool isDisposing)
        {
            refCount--;
            if (refCount == 0)
            {
                LocalDataStoreSlot slot = Thread.GetNamedDataSlot(DPSDataAccessAdapterFactory.ThreadSlotName);
                Thread.SetData(slot,null);
                isDisposed = true;
                base.Dispose(isDisposing);
            }
        }
    }

We now us the DataAccessAdapter like this:

using(TransactionScope scope = new TransactionScope()) 
using (DataAccessAdapter adapter = DPSDataAccessAdapterFactory.GetDataAccessAdapter()) 
{
  // Do transaction processing
  scope.Complete(); // commit
}

Should another DataAccessAdapter be created using the factory during the "Do transaction processing", it will get the thread-specific instance of the DPSDataAccessAdapter.

Regards Anders

Thanks for the feedback and code snippet!