Avoiding Leaking Connections With NHibernate And TransactionScope
Posted by Davy Brion on May 6th, 2010
One of the applications we’re currently working on wasn’t responding anymore on the development server. The logfile showed the following message:
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.
We’ve been using NHibernate in almost all of our projects for about 2 years now and we’ve never had this issue before. The thing is though, we never used System.Transactions’ TransactionScope class before and always used NHibernate’s transactions directly. In this project, we’re using TransactionScope for the first time (mostly because we need distributed transactions occasionally) so i immediately suspected that we were either doing something wrong with it, or that there was a bug somewhere.
After a while i managed to reproduce the problem with the following simple test fixture:
[TestFixture]
public class ConnectionLeakTest
{
private static ISessionFactory sessionFactory;
static ConnectionLeakTest()
{
sessionFactory = new Configuration()
.Configure()
.AddAssembly("MyAssembly")
.BuildSessionFactory();
}
[Test]
public void LeaksConnection()
{
var counter = 11;
for (int i = 0; i < counter; i++)
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var session = sessionFactory.OpenSession())
{
var blah = session.CreateCriteria<User>().List<User>();
}
}
}
}
}
If you assign counter a value that is greater than your ‘Max Pool Size’ value of your connection string, then you will get the following exception once you’re going through the loop for the ‘Max Pool Size’ + 1 time:
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.
Bingo!
In normal situations, you would obviously call the Complete method of the TransactionScope instance before it is disposed to commit the current database transaction. But in a situation where you can’t call the Complete method (as in: when an exception has occurred and you want to rollback the transaction), your database connection will actually leak until you’ve used up all of the connections in your connection pool.
So the question is: How can we use a TransactionScope with NHibernate without leaking connections when we throw an exception (which should result in an automatic rollback of the current transaction)?
I was always under the impression that merely opening an NHibernate session within a transaction scope was actually OK and that the session’s connection would make use of the current ambient transaction (the one created by the outer TransactionScope). Pretty much every session-related action you perform in NHibernate results in calling the CheckAndUpdateSessionStatus method of the SessionImpl class, which in turn calls the EnlistInAmbientTransactionIfNeeded method. That (as you can probably guess) will automatically enlist the current NHibernate session in the ambient transaction, which is the one that was created by the TransactionScope. So, as i understand it, it should indeed be sufficient to open an NHibernate session within a TransactionScope to have every action performed by that session being enlisted in the current ambient transaction. And it actually behaves as transactional as you’d expect it to be. Your transaction is properly committed if you call the Complete method of the TransactionScope and it is indeed rolled back if you do not call the Complete method. The only problem (and it’s a major one obviously) is that you’re connection will leak when the transaction is rolled back.
Interestingly enough, if we change the test code to this:
[Test]
public void LeaksConnection()
{
var counter = 11;
for (int i = 0; i < counter; i++)
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var session = sessionFactory.OpenSession())
using (var transaction = session.BeginTransaction())
{
var blah = session.CreateCriteria<User>().List<User>();
transaction.Rollback();
}
}
}
}
Then the problem goes away. Transactions are correctly rolled back, and none of the connections leak anymore. It’s too bad that we still need to use NHibernate’s transactions only for the sake of being able to rollback in case of failure without leaking the connection, because again, when you’re not using an NHibernate transaction but are within a TransactionScope, your Session’s connection will indeed be enlisted in the transaction being governed by the TransactionScope. So there certainly appears to be some kind of bug in NHibernate when it comes to cleaning up the connection of a session that has been enlisted in a TransactionScope’s transaction which is rolled back instead of committed.
Now, since i frequently see people on the intarweb showing examples of using an NHibernate session together with a TransactionScope yet without using an NHibernate transaction, i’d might as well post the way that i believe is the safest, and that shouldn’t leak any connections.
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var session = sessionFactory.OpenSession())
using (var transaction = session.BeginTransaction())
{
// do what you need to do with the session
transaction.Commit();
}
scope.Complete();
}
If you need to rollback, simply throw an exception within the using block of the NHibernate transaction (before the transaction.Commit() call obviously) and everything takes care of itself.
May 6th, 2010 at 1:24 pm
[...] Avoiding Leaking Connections With NHibernate And TransactionScope (Davy Brion) [...]
May 6th, 2010 at 7:43 pm
That got to be a bug in NHibernate?
Have you posted this on the NH group/SO?
May 6th, 2010 at 7:47 pm
Have you tried to fiddle with the session release mode?
http://knol.google.com/k/fabio-maulo/nhibernate-chapter-10/1nr4enxv3dpeq/13#10(2E)7(2E)(C2)(A0)Connection_Release_Modes
May 7th, 2010 at 3:35 am
I agree, there is something going on with distributed transactions and NH. In my case, on top of connection leaking, I see connection null reference exceptions when I have more than 1 transaction scope per session. I know I should try to stick to one per session, but I have a combination of factors (WCF, Oracle and optimistic locking) that force me to issue a refresh outside of the original txn scope in order to return the up to date “version” of an entity. It turns out that with a connection release mode of “auto” (default), the connection should be closed after the txn commits/rollback, but with txn scope, the commit phase occurs in a callback thread making the connection, depending on timing, etc, show up as valid to the next txn scope which then reuses it to later fail, because the commit occur in another thread, and effectively closed the connection. I’ve been working aruond this by changing the conn. release mode to on_close, but it is not the best solution.
Anyways, there is already a JIRA ticket for my issue, but I believe, if I use Davy’s pattern, everything should work with no side effects. We should just keep in mind, no more than 1 ado txn should be wrapped in a txn scope, otherwise you will find some surprises.
May 7th, 2010 at 9:19 am
@Andreas
yes, it’s a bug, and yes, i’ve contacted them
haven’t tried to change the release mode, since it should either just work the way i expected it to work, or not be supported at all
May 7th, 2010 at 9:32 am
[...] Avoiding Leaking Connections With NHibernate And TransactionScope – Davy Brion looks at using TransactionScope with NHibernate, and reproduces a problem where all the database connections were consumed due to not closing things off correctly when rolling back the TransactionScope transaction. [...]
May 7th, 2010 at 10:09 am
I was able to reproduce this with SQL Server but not with another database (I tried DB2). Don’t know, but maybe this is worth mentioning?
May 8th, 2010 at 10:56 pm
Do you think it’s related to your earlier post about distributed transactions between NH & NSB?
http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhibernate/
May 9th, 2010 at 9:49 am
@Awkward Coder
no i don’t think so… i managed to reproduce the DTC problem without using NHibernate. I could trigger the exact same problem by simply trying to open 2 SqlConnections within the same transaction scope.
May 11th, 2010 at 3:33 pm
@Davy, I found out why I was leaking connections (bug in my code). My pattern is a little different, forced by other factors.
If I do the following, it seems to prevent the leak and returns the connection to the pool after an exception:
…
ISession session = sessionFactory.OpenSession();
try
{
using(var ts = new TransactionScope())
{
}
finally
{
session.Dispose();
}
I do have another issue, form the logs
WARN: set a nonnull IDbCommand.Transaction to null because the Session had no Transaction
I need to dig this up, but from the logs too, I can see proper enlistment with the ambient transaction… more homework.
May 11th, 2010 at 8:54 pm
My IDbCommand Warning seems to be related to Oracle with ODP.NET only. As far as I can tell, it should be safe to ignore if you are using distributed transactions. FYI.
May 12th, 2010 at 7:59 am
@Juan
ah, i once had an issue where Disposing the session caused problems in combination with TransactionScope. But that was when the TransactionScope was created before the Session was. In that situation, i just had to close the session instead of disposing it
May 13th, 2010 at 4:57 am
Hi
is the leaking happening with both transaction factory classes AdoNetTransactionfactory and AdoNetWithDistributedTransactionFactory
May 14th, 2010 at 9:26 am
@Peter
haven’t tried it with AdoNetTransactionFactory yet, since that’s not even used by default. Even when you’re not using TransactionScope, NH will use AdoNetWithDistributedTransactionFactory
June 3rd, 2010 at 5:51 am
Hi Davy,
I hope I can shed some light. The project I am on uses the same pattern as your test case. Create a transaction scope, open session, execute sql. However we use ADONetTransactionfacory. Doing the same test we are not seeing connections leaking. I believe this is because the ADO connection automatically enlists in the ambient transaction. So when there is no transactionscope.commit or exception is raised the impact of the session using is to rollback transaction and close connection. In other words we are using NHibernate to create the connection, execute the sql and perform the ORM stuff and rely on transactionscope to manage the distributed transactions.
I wonder one of the issues with AdoNetWithDistributedTransactionFactory class is related to the connection automatically enlisting in the ambient transaction when the code is assuming it is managing the enlistment. I am concerned when I see this in the code
public void AfterTransaction()
{
if (IsAfterTransactionRelease)
{
AggressiveRelease();
}
else if (IsAggressiveRelease && batcher.HasOpenResources)
{
log.Info(“forcing batcher resource cleanup on transaction completion; forgot to close ScrollableResults/Enumerable?”);
batcher.CloseCommands();
AggressiveRelease();
}
else if (IsOnCloseRelease)
{
// log a message about potential connection leaks
log.Debug(
“transaction completed on session with on_close connection release mode; be sure to close the session to release ADO.Net resources!”);
}
transaction = null;
}
cheers
peter
June 3rd, 2010 at 7:01 am
@Davy
I’ve been running some tests within our solution framework to see if we are vulnerable to this problem since our pattern for using TransactionScope is the same as your test case. The difference with our’s is that we use the AdoNetTransactionFactory. From my investigations so far, I’ve found that connections are not leaked when this class is used instead of the default AdoNetWithDistrubtedTransactionFactory. So I decided to switch to the AdoNetWithDistrubtedTransactionFactory to make sure that I could replicate your results. On the first iteration of the loop, my test ran into the following exception.
System.InvalidOperationException: Disconnect cannot be called while a transaction is in progress.
After a little investigation, I found that this is the exception NHibernate throws if ConnectionManager.Disconnect() is called while there is still an active transaction. This method is called when a session is Disposed, which in this case is happening at the end of the session using block within the TransactionScope using block. So I had to ask: why does this not happen in your code?!
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
using (var session = sessionFactory.OpenSession())
{
var blah = session.CreateCriteria().List();
} // Disconnect called here, but transaction is still active, so exception is thrown.
}
If we change the code to be as follows, then I get the same result as you (i.e. max pool size reached exception).
using (var session = sessionFactory.OpenSession())
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
var blah = session.CreateCriteria().List();
}
}
This code isn’t particularly useful within our solution, so we will stick to the AdoNetTransactionFactory for now, as this is working well for us. I believe this is because of what Peter has said above, the actual Ado Connection is enlisting in the transaction even if NHibernate is ignoring it. I’m just worried because the distributed transaction factory class SHOULD work for this! I’ve seen several posts from people having the same problem as I found, but so far no one has suggested a reason for it. Have you got any ideas about it? Have you come across it before?
We are using 2.1.2 and I have posted our config below (using the distributed transaction factory instead)
Thanks!
Sam
NHibernate.Connection.DriverConnectionProvider
NHibernate.Dialect.MsSql2005Dialect
NHibernate.Driver.SqlClientDriver
ReadCommitted
20
NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu
NHibernate.Transaction.AdoNetWithDistrubtedTransactionFactory, NHibernate
June 4th, 2010 at 12:46 am
I have a solution for my post above, and I actually feel like a bit of a numpty now for posting it in the first place!
We are using our own session class that implements ISession and passes nearly all calls directly through to a local instance of NHibernate’s SessionImpl class. As it turns out, our Dispose() does a little more work than just passing through. In fact it is calling the Close() and the Disconnect() methods on the SessionImpl instance directly. Take these calls out of our custom session (so it just basically passes the call through) and I have the same leaking connection result that I expected to see!
June 4th, 2010 at 10:09 am
@Peter
interesting… i reran the test with AdoNetTransactionFactory and you’re right… it doesn’t have the leaking connection problem and the connection indeed appears to enlist properly in the distributed transaction
i’d have to investigate further, but this quick test does make me think that AdoNetTransactionFactory is more suitable for dealing with Distributed Transactions than the AdoNetWithDistributedTransactionFactory
June 17th, 2010 at 4:50 am
[...] Jest to jednak zamiana jednego problemu na inny. Do niedawna bowiem pojawiał się w NServiceBus wyciek pamięci, ponieważ zachowanie NHibernate w wypadku współpracy z System.Transactions jest bardzo słabo [...]
August 12th, 2010 at 11:37 am
Only working solution that I could find is to wrap NHibernate session and pass it connection while opening it, on dispose method I call NHibernate dispose (this doesn’t close connection since it isn’t owner, and after that I close connection)
AdoNetTransactionFactory also doesn’t work very well, based on what I see it opens calls connection pool too many times, and if I put 10 sinc threads to do some work I have \to many connections\ exception.