session and pooled connections problem

[givoni]

Hi guys,

I've got an issue related to pooled connections and the handling of
the nhibernate sessions and I tried posting it on the nhibernate
forums, but didn't get any responses after a week, so I'm "cross-
posting" here with the hope that some of you might be of help. 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:
More frequently this:

1. 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:

2. 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
setup 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's
public ip, and changing that to the LAN ip minimized the errors
drastically.
- if we add two worker processes per application pool the errors
disappear 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 SQL SERVER 2005 Standard
but with the ms2000 dialect.

I hope you can help me out.
Thanks,
David

[Ayende Rahien]

have you written your own connection provider?

[givoni]

No, I'm using the nhibernate driverconnection provider.
In general I don't think I'm doing anything out of the ordinary, but
well, I can't explain this.
On top of the two other errors I algo get a
System.InvalidOperationException: ExecuteReader requires an open and
available Connection. The connection's current state is closed
randomly as well.

Regards,
David

[Ayende Rahien]

Okay, that is strange.
Can you try to repro this error with just ADO.Net?

[givoni]

Ok, Ayende, I've tried to reproduce the error with ADO.NET, not using
NHibernate and I couldn't reproduce it with the test I made.
What I did was selecting 4 of the sql queries that nhibernate produced
and that gave errors sometimes and then randomly executing them with a
datareader when the page was accessed.
I ran the MS application stress tool for two hours sending on average
12.5 request per second and didn't get any errors. The test is
probably not conclusive since the page didn't contain the overhead of
the site in question, but at least it looks like it's something with
nhibernate or the session-per-request pattern.
I also tried my site again with connection pooling turned off and that
also didnt produce any errors.

I'm thinking of replacing the session-per-request with opening and
closing the session each time I need it instead, but as far as I
understand that will not be any different since the connection pooling
is handled at a lower level by ado.net, correct? Also I expect that
there will be a performance penalty by doing that, as well as there is
by not using connection pooling.

I'm really stuck here :(

Do you know if NHibernate 2 handles connections differently, so that
upgrading might have a positive effect?
Or do you have any other ideas or suggestions as to solve it or test
it further?

Thanks for trying to help,
David

On Jun 12, 11:58 am, "Ayende Rahien" <aye...@ayende.com> wrote:
> Okay, that is strange.
> Can you try to repro this error with just ADO.Net?
>

[Ayende Rahien]

There were some changes there, yes, upgrading may help, not sure.
What about transaction handling there?
Can you try creating a test case the repro this?

I have been using Session per Request with SQL 2005 for a few years, and I haven't seen this issue.

[givoni]

Hi again,

I have now upgraded to nhibernate 2, but the issue still exists.
I haven't produced a test case to reproduce it, and I suspect it would
be complicated since in my case it happens intermittently and only
when with a high amount of request and a high database - web site
latency.

I did find this long thread about the same problem, which seems to
confirm that it's not only NHibernate related:
http://forums.microsoft.com/msdn/showpost.aspx?pageindex=5&siteid=1&postid=137373&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

One of the solutions they suggest is this: "Microsoft recommends not
passing the SqlDataReader to another method and avoiding any static
methods usage, they are not sure this is the root cause to this
Exception.".
I haven't looked into the NHibernate code to see if this might be the
case here, but maybe you guys know if it could be true and if it would
be possible to change that?

Regards,
David

On Jun 15, 11:03 pm, "Ayende Rahien" <aye...@ayende.com> wrote:
> There were some changes there, yes, upgrading may help, not sure.
> What about transaction handling there?
> Can you try creating a test case the repro this?
>
> I have been using Session per Request with SQL 2005 for a few years, and I
> haven't seen this issue.
>

[givoni]

I believe I found a solution and would like your opinion about it, to
see if it is possible to integrate with the official NHibernate
release.

I have dived into the NHibernate 2.0.x code and found three instances
of an IDataReader object being passed to a static method:
- identifiergeneratorfactory.cs: public static object Get(IDataReader
rs, IType type, ISessionImplementor session)
- loader.cs: private static void ReadCollectionElement(object
optionalOwner, object optionalKey, ICollectionPersister persister,
ICollectionAliases descriptor, IDataReader rs, ISessionImplementor
session)
- loader.cs: internal static void Advance(IDataReader rs, RowSelection
selection)

I tried changing all of those to non-static methods. The first two
methods are not used by NHibernate itself, but the third "Advance" is
used in both multiqueryimpl.cs and multicriteriaimpl.cs.
I changed both instances to use an instantiated object inheriting from
the Loader class (can't remember the type right now). As far as I can
see that shouldn't change anything, but I would like your opinion on
that as well.

To test it before I made the changes I first made a simple stress-test
during an hour that did produce the errors a couple of times.
After changing it I made two tests, one during an hour and another
during 4 hours and in neither case did the error occur even once. (All
these tests were averaging a 5 requests per second varying between 10
different pages on the site).

So I'm pretty confident that this is the fix although I can't exactly
explain why changing the method from static to non-static would make a
difference in these cases.

I hope some of you would take a look and let me know if this sounds ok
to change.

Thank you,
David

On Aug 10, 2:39 pm, givoni <falikov...@gmail.com> wrote:
> Hi again,
>
> I have now upgraded to nhibernate 2, but the issue still exists.
> I haven't produced a test case to reproduce it, and I suspect it would
> be complicated since in my case it happens intermittently and only
> when with a high amount of request and a high database - web site
> latency.
>
> I did find this long thread about the same problem, which seems to

> confirm that it's not only NHibernate related:http://forums.microsoft.com/msdn/showpost.aspx?pageindex=5&siteid=1&p...

[Ayende Rahien]

Can you create a patch for this?

[givoni]

I certainly can, but I'm not sure of the procedure to do this. I've
never contributed to the nhibernate project before.

On Aug 11, 12:24 pm, "Ayende Rahien" <aye...@ayende.com> wrote:
> Can you create a patch for this?
>

[Yitzchok (adminjew)]

Check this out on how to create a patch
http://www.hibernate.org/428.html