3

I have a long running application that uses NHibernate.ISessionFactory to connect to an Oracle database.

Occasionally the database goes offline (e.g. for weekend maintenance), but even once the database is back online, subsequent queries fail with the following exception (inner exceptions also shown):

NHibernate.Exceptions.GenericADOException: could not execute query
[ select .....]

  >> Oracle.ManagedDataAccess.Client.OracleException: ORA-03135: Connection lost contact

    >> OracleInternal.Network.NetworkException: ORA-03135: Connection lost contact

      >> System.Net.Sockets.SocketException: An established connection 
         was aborted by the software in your host machine

Restarting the application restores the functionality, but I would like the application to be able to automatically cope without a restart, by "resetting" the connection.

I have tried the following with my ISessionFactory when I hit this exception:

sf.EvictQueries();
sf.Close();
sf = null;
sf = <create new session factory>

but see the same exception after recreating the ISessionFactory. I assume this is because NHibernate is caching the underlying broken connection in some kind of connection pool?

How can I persuade NHibernate to create a genuinely new connection (or even just reset all state completely), and hence allow my application to fix the connection issue itself without an application restart?

EDIT: Following A_J's answer, note that I am already calling using (var session = _sessionFactory.OpenSession()) for each database request.

jlb83
  • 1,988
  • 1
  • 19
  • 30

1 Answers1

1

I suspect you are opening ISession (call to ISessionFactory.OpenSession()) at startup and closing it at application end. This is wrong approach for any long running application.

You should manage connection at lower level of time. In web application, this is generally handled per request. In your case, you should find what that should be. If yours is windows service that does some activity after specified time then Timer_Tick event is good place.

I cannot suggest what that location could be in your application; you need to find out on your own.

Edit 1

Looking at your edit and comment, I do not think this has anything to do with NHibernate. May be that the connection pool is returning a disconnected/stale connection to NHibernate.

Refer this and this accepted answer.

Community
  • 1
  • 1
Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • I should have mentioned in the question, I call `using (var session = _sessionFactory.OpenSession())` for every request. Therefore there are no long-running sessions (that I know of) - only a long-running SessionFactory. Thanks - I will update the question to clarify. – jlb83 Nov 11 '16 at 08:47
  • Thanks for the links. Took a few days to test as I only lose database connectivity overnight. I noticed my connection string had a `Min Pool Size` but no `Max Pool Size`. From reading around, I thought perhaps that if I set `Max Pool Size` to N, then perhaps with N (automated) retries the next morning, I'd get back to a working state. However, it seems that setting `Max Pool Size` to 10 has actually just solved the issue - without even needing any retries - queries "just work" the next morning. I don't understand this, so will keep an eye on it for the next few days...but it seems to work. – jlb83 Nov 18 '16 at 10:45