I am using an open source tool called NServicebus which uses Nhibernate internally. Using the Nservicebus tool, I am publishing a lot of messages in a loop to an MSMQ queue. For every message published, NHibernate does a look up against an Oracle database.

The problem is that Nhibernate obtains a connection within a distributed transaction but does not close it. Since I am publishing a lot of messages, over a period of time a lot of connections opened by Nhibernate and nothing was closed. After acquiring say 50+ connections, any further requests are timing out. Any ideas?

Here are the traces

.ctor - [session-id=e078deae-f502-4228-9882-ca609fa1e108] opened session at timestamp:633996628995
2010-01-21 09:28:19,591 DEBUG EnlistInDistributedTransactionIfNeeded - enlisted into DTC transaction: Serializable

2010-01-21 09:28:19,763 DEBUG ToSqlString - The initial capacity was set too low at: 8 for the SelectSqlBuilder that needed a capacity of: 14 for the table "Subscription" this_
2010-01-21 09:28:20,029 DEBUG LogOpenPreparedCommand - Opened new IDbCommand, open IDbCommands: 1
2010-01-21 09:28:20,029 DEBUG Generate - Building an IDbCommand object for the SqlString: SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (?, ?)
2010-01-21 09:28:20,029 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [0]
2010-01-21 09:28:20,029 DEBUG NullSafeSet - binding 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 0
2010-01-21 09:28:20,060 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [1]
2010-01-21 09:28:20,060 DEBUG NullSafeSet - binding 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 1
2010-01-21 09:28:20,060 INFO GetResultSet - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1)
2010-01-21 09:28:20,076 DEBUG LogCommand - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1);:p0 = 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null', :p1 = 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null'

--------Here obtains a connection-------------------
2010-01-21 09:28:20,091 DEBUG GetConnection - Obtaining IDbConnection from Driver
2010-01-21 09:28:20,545 DEBUG ExecuteReader - ExecuteReader took 90 ms
2010-01-21 09:28:20,545 DEBUG LogOpenReader - Opened IDataReader, open IDataReaders: 1
2010-01-21 09:28:20,560 DEBUG DoQuery - processing result set
2010-01-21 09:28:20,560 DEBUG DoQuery - result set row: 0
2010-01-21 09:28:20,560 DEBUG GetRow - result row:
2010-01-21 09:28:20,591 DEBUG NullSafeGet - returning 'distributordatabus.translationservice@xxx' as column: y0_
2010-01-21 09:28:20,591 DEBUG DoQuery - done processing result set (1 rows)
2010-01-21 09:28:20,607 DEBUG LogCloseReader - Closed IDataReader, open IDataReaders :0
2010-01-21 09:28:20,607 DEBUG CloseReader - DataReader was closed after 152 ms
2010-01-21 09:28:20,607 DEBUG LogClosePreparedCommand - Closed IDbCommand, open IDbCommands: 0
2010-01-21 09:28:20,623 DEBUG InitializeNonLazyCollections - initializing non-lazy collections
2010-01-21 09:28:20,638 DEBUG TransformList - transformed: 1 rows to: 1 distinct results
2010-01-21 09:28:20,638 DEBUG Dispose - [session-id=e078deae-f502-4228-9882-ca609fa1e108] running ISession.Dispose()

-----------Message sent to another queue --------------------
2010-01-21 09:28:20,654 DEBUG SendMessage - Sending message Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null with ID d187f93b-f1e6-4630-93c1-703b213371f4\760964 to destination distributordatabus.translationservice@xxx.
2010-01-21 09:28:21,029 DEBUG .ctor - [session-id=a752758d-2511-43ca-86ab-98c7109ade00] opened session at timestamp:633996629010
2010-01-21 09:28:21,029 DEBUG EnlistInDistributedTransactionIfNeeded - enlisted into DTC transaction: Serializable
2010-01-21 09:28:21,045 DEBUG ToSqlString - The initial capacity was set too low at: 8 for the SelectSqlBuilder that needed a capacity of: 14 for the table "Subscription" this_
2010-01-21 09:28:21,060 DEBUG LogOpenPreparedCommand - Opened new IDbCommand, open IDbCommands: 1
2010-01-21 09:28:21,060 DEBUG Generate - Building an IDbCommand object for the SqlString: SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (?, ?)
2010-01-21 09:28:21,060 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [0]
2010-01-21 09:28:21,076 DEBUG NullSafeSet - binding 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 0
2010-01-21 09:28:21,076 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [1]
2010-01-21 09:28:21,076 DEBUG NullSafeSet - binding 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 1
2010-01-21 09:28:21,076 INFO GetResultSet - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1)
2010-01-21 09:28:21,076 DEBUG LogCommand - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1);:p0 = 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null', :p1 = 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null'

-------Obtain another connection. The previous connection not closed yet.----------------
2010-01-21 09:28:21,076 DEBUG GetConnection - Obtaining IDbConnection from Driver
2010-01-21 09:28:21,310 DEBUG ExecuteReader - ExecuteReader took 5 ms
2010-01-21 09:28:21,310 DEBUG LogOpenReader - Opened IDataReader, open IDataReaders: 1
2010-01-21 09:28:21,310 DEBUG DoQuery - processing result set
2010-01-21 09:28:21,310 DEBUG DoQuery - result set row: 0
2010-01-21 09:28:21,310 DEBUG GetRow - result row:
2010-01-21 09:28:21,310 DEBUG NullSafeGet - returning 'distributordatabus.translationservice@xxx' as column: y0_
2010-01-21 09:28:21,326 DEBUG DoQuery - done processing result set (1 rows)
2010-01-21 09:28:21,326 DEBUG LogCloseReader - Closed IDataReader, open IDataReaders :0
2010-01-21 09:28:21,326 DEBUG CloseReader - DataReader was closed after 21 ms
2010-01-21 09:28:21,326 DEBUG LogClosePreparedCommand - Closed IDbCommand, open IDbCommands: 0
2010-01-21 09:28:21,326 DEBUG InitializeNonLazyCollections - initializing non-lazy collections
2010-01-21 09:28:21,326 DEBUG TransformList - transformed: 1 rows to: 1 distinct results
2010-01-21 09:28:21,326 DEBUG Dispose - [session-id=a752758d-2511-43ca-86ab-98c7109ade00] running ISession.Dispose()

-----------Message Sent to another queue-----------
2010-01-21 09:28:21,341 DEBUG SendMessage - Sending message Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null with ID d187f93b-f1e6-4630-93c1-703b213371f4\760965 to destination distributordatabus.translationservice@xxx.
2010-01-21 09:28:22,466 DEBUG .ctor - [session-id=6916171d-f596-4659-8428-92c2fbddfd0c] opened session at timestamp:633996629024
2010-01-21 09:28:22,482 DEBUG EnlistInDistributedTransactionIfNeeded - enlisted into DTC transaction: Serializable
2010-01-21 09:28:22,482 DEBUG ToSqlString - The initial capacity was set too low at: 8 for the SelectSqlBuilder that needed a capacity of: 14 for the table "Subscription" this_
2010-01-21 09:28:22,498 DEBUG LogOpenPreparedCommand - Opened new IDbCommand, open IDbCommands: 1
2010-01-21 09:28:22,498 DEBUG Generate - Building an IDbCommand object for the SqlString: SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (?, ?)
2010-01-21 09:28:22,498 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [0]
2010-01-21 09:28:22,498 DEBUG NullSafeSet - binding 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 0
2010-01-21 09:28:22,498 DEBUG BindParameters - BindParameters(Named:NHibernate.Type.StringType) Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null -> [1]
2010-01-21 09:28:22,513 DEBUG NullSafeSet - binding 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null' to parameter: 1
2010-01-21 09:28:22,513 INFO GetResultSet - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1)
2010-01-21 09:28:22,513 DEBUG LogCommand - SELECT this_.SubscriberEndpoint as y0_ FROM "Subscription" this_ WHERE this_.MessageType in (:p0, :p1);:p0 = 'Company.DataContract.Message.InfoDownloaded, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null', :p1 = 'Company.DataContract.Message.IServiceBusMessage, Company.DataContract, Version=5.5.5.5, Culture=neutral, PublicKeyToken=null'

-------Obtain another connection. This goes on and on and on without closing previous connections. Ultimatley the connection requests times out-------------
2010-01-21 09:28:22,513 DEBUG GetConnection - Obtaining IDbConnection from Driver

I have the same problem with user connections not closing, but in a different context.

My .NET application communicates with a SQL Server Express 2005 database, and uses ISession sessions while producing some reports. Using PerfMon.exe to watch the "General Statistics" object's "User Connections" counter, I can see the number of connections climbing but never being released. I eventually run out of connections when I hit 100, and then I get the timeout exception:



I have tried waiting several minutes to see if NHibernate releases the connections after a minute (something I read elsewhere), but I can only free the connections by closing the application. I have checked my ISession use and I am always calling "<ISession_instance>.Close()" in the finally block of a try-catch-finally statement.

Is there a way round this? Alternatively, what do I need to do to get NHibernate to explicitly release some/all of the connections?

I have seen reference to the following being used when using Hibernate from a Java application:



"In the JAVA persistance with hibernate book, c3p0 configuration options are explained:

•hibernate.c3p0.min_size - This is the minimum number of JDBC connections that C3P0 keeps ready at all times
•hibernate.c3p0.max_size - This is the maximum number of connections in the pool. An exception is thrown at runtime if this number is exhausted.
•hibernate.c3p0.timeout - You specify the timeout period (in this case, 300 seconds) after which an idle connection is removed from the pool).
•hibernate.c3p0.max_statements - Maximum Number of statements that will be cached. Caching of prepared statements is essential for best performance with Hibernate.
•hibernate.c3p0.idle_test_periods - This is the iddle time in seconds before a connection is automatically validated."

By the above description, the "timeout" property might be what we need to tweak. What is the NHibernate equivalent? My "App.config" file currently contains: (among other things):