CODEWITHSUNDEEP
c#sqloracle-databasenhibernatedatabase-connection
jlb83
jlb83

Reputation: 2188

Restore NHibernate after lost Oracle database connection

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.

Upvotes: 3

Views: 948

Answers (1)

Amit Joshi
Amit Joshi

Reputation: 16389

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.

Upvotes: 1

Related Questions