title: NHibernate and ADO.NET Connection Pooling
body: <p>It seems that NHibernate does not pool ADO.NET database connections. Connections are only closed when the transaction is committed or rolled back. A review of the source code shows that there is no way to configure NHibernate so that it is closing connections when the ISession is disposed. </p>
  
  <p>What was the intent of this behaviour? ADO.NET has connection pooling itself. There's no need to hold them open all the time within the transaction. With this behaviour are also unneccessaryly distributed transactions created. A possible workaround described in <a href="http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/" rel="nofollow noreferrer">http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/</a> therefore does not work (at least not with NHibernate 3.1.0). I am using Informix. The same problem seems to exisit for every other database (<a href="https://stackoverflow.com/questions/6686993/nhibernate-connection-pooling">NHibernate Connection Pooling</a>).</p>
  
  <p>Is there any other workaround or advice avoiding this problem?</p>
  
  <p>Here's a unit test reproducing the problem:</p>
  
  <pre><code>  [Test]
    public void DoesNotCloseConnection()
    {
       using (SessionFactoryCache sessionFactoryCache = new SessionFactoryCache())
       {
          using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(10) }))
          {
             fixture.Setup(); // Creates test data
  
             System.Data.IDbConnection connectionOne;
             System.Data.IDbConnection connectionTwo;
  
             using (ISessionFactory sessionFactory = sessionFactoryCache.CreateFactory(GetType(), new TestNHibernateConfigurator()))
             {
                using (ISession session = sessionFactory.OpenSession())
                {
                   var result = session.QueryOver&lt;Library&gt;().List&lt;Library&gt;();
                   connectionOne = session.Connection;
                }
             }
  
             // At this point the first IDbConnection used internally by NHibernate should be closed
  
             using (ISessionFactory sessionFactory = sessionFactoryCache.CreateFactory(GetType(), new TestNHibernateConfigurator()))
             {
                using (ISession session = sessionFactory.OpenSession())
                {
                   var result = session.QueryOver&lt;Library&gt;().List&lt;Library&gt;();
                   connectionTwo = session.Connection;
                }
             }
  
             // At this point the second IDbConnection used internally by NHibernate should be closed
  
             // Now two connections are open because the transaction is still running
             Assert.That(connectionOne.State, Is.EqualTo(System.Data.ConnectionState.Closed)); // Fails because State is still 'Open'
             Assert.That(connectionTwo.State, Is.EqualTo(System.Data.ConnectionState.Closed)); // Fails because State is still 'Open'
          }
       }
    }
  </code></pre>
  
  <p>The disposing of the NHibernate-Session does nothing since we are still in a transaction</p>
  
  <p>SessionImpl.cs:</p>
  
  <pre><code>public void Dispose()
      {
          using (new SessionIdLoggingContext(SessionId))
          {
              log.Debug(string.Format("[session-id={0}] running ISession.Dispose()", SessionId));
              if (TransactionContext!=null)
              {
                  TransactionContext.ShouldCloseSessionOnDistributedTransactionCompleted = true;
                  return;
              }
              Dispose(true);
          }
      }
  </code></pre>
  
  <p>Injecting a custom ConnectionProvider will also not work since the ConnectionManager calling the ConnectionProvider has several preconditions checking that closing a connection within a transaction is not allowed.</p>
  
  <p>ConnectionManager.cs:</p>
  
  <pre><code>public IDbConnection Disconnect() {
          if (IsInActiveTransaction)
              throw  new InvalidOperationException("Disconnect cannot be called while a transaction is in progress.");
  
          try
          {
              if (!ownConnection)
              {
                  return DisconnectSuppliedConnection();
              }
              else
              {
                  DisconnectOwnConnection();
                  ownConnection = false;
                  return null;
              }
          }
          finally
          {
              // Ensure that AfterTransactionCompletion gets called since
              // it takes care of the locks and cache.
              if (!IsInActiveTransaction)
              {
                  // We don't know the state of the transaction
                  session.AfterTransactionCompletion(false, null);
              }
          }
      }
  </code></pre>