sql-server-2008 - 在TransactionScope中消耗的连接池连接

Question

有人可以解释为什么在TransactionScope内执行查询时导致连接池的总消耗量为什么会导致

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.

我将问题简化为以下几点：

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();
    }
}

编辑

在下面Omer的回答之后，我认为问题可以像这样更好地解释：

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();
    }
}

当然这不是预期的行为吗？我什么都没读，无法解释为什么会发生这种情况。我只能假设这与我们实现存储库的方式有关。希望以下内容将对实现进行足够好的描述。

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)
    {
    }
}

我们的连接字符串相当传统，并且将池中的连接数保留为默认100（因此，需要进行101次迭代来测试上述代码中的问题）。

Answer 1

您正在for循环内创建新的DataContext引用。

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

它将所有这些交易保留在不同的交易中。如果您只将repo init代码放在for循环之外，并在一个上下文中执行所有操作，那就没问题了。

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();
}