ADO.NET Connection Pooling

[Antineutrino]

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 (http://stackoverflow.com/questions/6686993/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);
}
}
}

[Jason Meckley]

how long are you keeping the TX open that scoping the connection to the TX is a problem? you can configure connection management by setting connection.release_mode

[Antineutrino]

The problem is that configuring the connection.release_mode does not
work. As soon as you have a transaction context, the closing of the
session is done in the Transaction_Completed event handler.
Dispose(true) (which closes the session and the connection) is never
called.

See my example from above:

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

TransactionContext is not null and therefore the connection won't be
close no matter what you've configured in the settings. The
ConnectionManager used internally by NHibernate also has a
precondition that prevents closing a session while being in a
transaction.

This can't be correct, right? Or maybe I still haven't understand how
should work.

On 27 Sep., 17:17, Jason Meckley <jasonmeck...@gmail.com> wrote:
> how long are you keeping the TX open that scoping the connection to the TX
> is a problem? you can configure connection management by setting

> connection.release_mode<http://nhforge.org/doc/nh/en/index.html#transactions-connection-release>

[Antineutrino]

.