NHibernate and ADO.NET Connection Pooling

Question

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.

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 http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/ 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 (NHibernate Connection Pooling).

Is there any other workaround or advice avoiding this problem?

Here's a unit test reproducing the problem:

  [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<Library>().List<Library>();
                 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<Library>().List<Library>();
                 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'
        }
     }
  }

The disposing of the NHibernate-Session does nothing since we are still in a transaction

SessionImpl.cs:

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

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.

ConnectionManager.cs:

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

Answer 1

NHibernate has two "modes".

• Either you open the connection in your application, then it is up to the application to manage it. This "mode" is used when passing a connection to sessionfactory.OpenSession(connection).
• Or the connection is created by NH. Then it is closed when the session is closed. This "mode" is used when not passing a connection to sessionfactory.OpenSession()

There is some support for TransactionScope. It is most probably using the first "mode". Probably the connection is not hold by NH, but by the transaction scope. I don't know exactly, I don't use environment transactions.

NH is using the ADO.NET connection pool by the way.

You can also disconnect the session using ISession.Disconnect() and reconnect using ISession.Reconnect().

In the documentation you find:

The method ISession.Disconnect() will disconnect the session from the ADO.NET connection and return the connection to the pool (unless you provided the connection).