Connection Pool Connections consumed in TransactionScope

Can someone explain why we are experiencing overall connection pool consumption when making requests inside a TransactionScope, resulting in

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

      

I've reduced our problem to the following:

SomeNonTransactionalCode()
{
    // This code will execute with just one connection to the database
    // and complete without exception

   List<BusinessEntity> beList;

   for (int i = 0; i < 101; i++)
   {
       BusinessEntityRepository beRepo = new BusinessEntityRepository();
       beList = beRepo.ReadAll().ToList();
   }
}


SomeTransactionalCode()
{
    // This code will cause the connections to the database to increment
    // with every iteration eventually timing out after 100 connections

    using (TransactionScope transactionScope = new TransactionScope())
    {
        List<BusinessEntity> beList;

        for (int i = 0; i < 101; i++)
        {
            BusinessEntityRepository beRepo = new BusinessEntityRepository();
            beList = beRepo.ReadAll().ToList();
        }

        transactionScope.Complete();
    }
}

      

EDIT

Following Omer's answer below, I think the problem is better explained:

SomeNonTransactionalCode()
{
    // This code will execute with just one connection to the database

   List<BusinessEntity1> be1List;
   BusinessEntity1Repository be1Repo = new BusinessEntity1Repository();
   be1List = be1Repo .ReadAll().ToList();

   List<BusinessEntity2> be2List;
   BusinessEntity2Repository be2Repo = new BusinessEntity2Repository();
   be2List = be2Repo .ReadAll().ToList();

   List<BusinessEntity3> be3List;
   BusinessEntity3Repository be3Repo = new BusinessEntity3Repository();
   be3List = be3Repo.ReadAll().ToList();

}


SomeTransactionalCode()
{
    // This code will cause three seperate connections to the database

    using (TransactionScope transactionScope = new TransactionScope())
    {
        // note this is simplified - the code below could be in unknown nested
        // methods make creating of the repos prior to calling not possible

        List<BusinessEntity1> be1List;
        BusinessEntity1Repository beRepo1 = new BusinessEntity1Repository();
        be1List = be1Repo.ReadAll().ToList();

        List<BusinessEntity2> be2List;
        BusinessEntity2Repository beRepo2 = new BusinessEntity2Repository();
        be2List = be2Repo.ReadAll().ToList();

        List<BusinessEntity3> be3List;
        BusinessEntity3Repository beRepo3 = new BusinessEntity3Repository();
        be3List = be3Repo.ReadAll().ToList();

        transactionScope.Complete();
    }
}

      

Isn't this the expected behavior? I haven't read anything that explains why this might be happening. I can only assume it has something to do with how we implemented our repositories. Hopefully the following will provide a good enough description of the implementation.

public class BusinessEntityRepository
{
    private BusinessEntityDal Dal { get; set; }

    public BusinessEntityRepository()
    {
        this.Dal = new BusinessEntityDal ();
    }

    public IQueryable<BusinessEntity> ReadAll()
    {
        IQueryable<BusinessEntity> query = null;
        if (Dal != null)
        {
            query = Dal.ReadAll();
        }

        //-Return
        return query;
    }
}

public class BusinessEntityDal : BaseDal 
{
    public IQueryable<BusinessEntity> ReadAll()
    {
        var result = from de in this.DC.BusinessEntityTable
                         select new BusinessEntity
                         {
                             Property1 = Column1,
                             Property2 = Column2,
                             // etc... 
                         };
        //-Return
        return (result);
    }
}

public abstract class BaseDal
{
    protected OurDataContext DC;

    public BaseDal()
    {
        // create a DataContext
        this.DC = new OurDataContext();
    }
}

public class OurDataContext : System.Data.Linq.DataContext
{       
    private static readonly string _cn = // some static connection string taken from web.config
    public OurDataContext()
        : base(OurDataContext._cn)
    {
    }
}

      

Our connection string is pretty arbitrary and leaves the number of connections in the pool at the default 100 (hence 101 iterations checks the problem in my code above).

+3


source to share


1 answer


You are creating new DataContext references inside the for loop.

for (int i = 0; i < 101; i++)
    {
        BusinessEntityRepository beRepo = new BusinessEntityRepository();
        beList = beRepo.ReadAll().ToList();
    }

      



It stores all this in different transactions. If you just put your repo init code outside of the for loop and do all the operations in the same context, that's fine.

using (TransactionScope transactionScope = new TransactionScope())
{
    List<BusinessEntity> beList;
    BusinessEntityRepository beRepo = new BusinessEntityRepository();
    for (int i = 0; i < 101; i++)
    {

        beList = beRepo.ReadAll().ToList();
    }
    //do some other things with same context

    transactionScope.Complete();
}

      

0


source







All Articles