I've got an issue related to pooled connections and the handling of the nhibernate sessions. I have read loads of documentation about these issues and no solution have helped me out so far, so I'll try and explain the whole problem in detail.
The symptoms are that the website I'm testing gives two different types of errors:
NHibernate.ADOException: could not load an entity: [...][...] ---> System.IndexOutOfRangeException: xx at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String names, ISessionImplementor session, Object owner) at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.Hydrate(IDataReader rs, Object id, Object obj, ILoadable persister, ISessionImplementor session, String suffixedPropertyColumns)
and less frequently :
NHibernate.ADOException: could not execute query [... ] ---> System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
The site has been running for quite a while without these issues, but the whole problem started when we moved the site from a one server with both web server and database on it, to a three server load balanced setup, where we have two web servers and the database server behind.
From what I've been able to read it seems that these errors are due to pooled connections being mixed up at some level, not receiving a reset connection at the beginning or taking over a connection that belonged to another thread.
We are using the Open Session in View pattern, where the nhibernate session lives during the lifetime of the request. We found out that storing the session in the callcontext is not threadsafe, so we changed that to httpcontext.current.items, but it didn't help.
What does help is the two following things:
- at the beginning we had the database server identified by it' public ip, and changing that to the LAN ip minimized the errors drastically.
- if we add two worker processes per application pool the errors end completely.
This is however not a long term solution, it might work with the load the site has right now, but for futures sake we need to get to the bottom of this.
I'll be happy to post how we handle the sessions and other concrete code samples, but I wanted to give you a general overview of the whole issue first and see if anybody could see anything principally wrong with our setup or conclusions.
We're using NHibernate 1.2.1, ASP.NET 2.0, MS
MS SQL SERVER 2005 Standard but with the ms2000 dialect.
I hope you can help me out.