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).

Answer 2

You can accomplish this by adding the following settings to your connection string.

Pooling=true; 
Min Pool Size=3;
Max Pool Size=25;
Connection Lifetime=7200;
Connection Timeout=15;
Incr Pool Size=3;
Decr Pool Size=5;

Pooling: enables pooling for your app

Min Pool: The minimum number of connections to keep open even when all sessions are closed.

Max Pool: The max number of connections the app will open to the DB. When the max is reached it will wait for the number of seconds specified by Connection Timeout and then throw an exception.

Connection Timeout: Maximum Time (in secs) to wait for a free connection from the pool

Connection Lifetime: When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.

Incr Pool Size: Controls the number of connections that are established when all the connections are used.

Decr Pool Size: Controls the number of connections that are closed when an excessive amount of established connections are unused.

http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance