tags:

views:

93

answers:

2
Q: 

Could not load an entity in NHibernate then Timeout Exception when Table is existing

I need help on this.

I got this error while inserting/updating a number of records

could not load an entity: 
[Star.CNPL_BusinessObjects.Entities.CNPL.CNPL_AgencyProduct#48][SQL: SELECT cnpl_agenc0_.Id as Id48_0_, cnpl_agenc0_.AgencyID as AgencyID48_0_, cnpl_agenc0_.ProductID as ProductID48_0_, cnpl_agenc0_.CreatedDate as CreatedD4_48_0_, cnpl_agenc0_.CreatedBy as CreatedBy48_0_, cnpl_agenc0_.UpdatedDate as UpdatedD6_48_0_, cnpl_agenc0_.UpdatedBy as UpdatedBy48_0_ FROM CNPL_AgencyProduct cnpl_agenc0_ WHERE cnpl_agenc0_.Id=?]"

Then, I get this inner exception

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 
   at System.Data.SqlClient.SqlDataReader.get_MetaData() 
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, 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) 
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) 
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) 
   at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, Type optionalEntityName, Object optionalIdentifier, IEntityPersister persister)

Need help on this guys. Ideas can help. :)

A: 

You might be experiencing a deadlock issue here. Are you doing a simple Get? or are you doing other operations in a transaction that might lock it?

Mark PM  2010-10-11 09:34:05
it's just a simple update on the table. I checked the mapping tables and they are just the same as in the physical tables in SQL.
Musikero31  2010-10-11 09:41:50
I suggest you run SQL Profiler (seems like you are using SQL server) and find the command that is causing the timeout.
Mark PM  2010-10-11 09:48:08
There's was this one command that has 1.6M seconds (I guess) under the read column and it's the proc_MSS_Crawl. I don't know if this is the one and I don't know if this counts.
Musikero31  2010-10-11 10:27:19
Which profiler template did you use? You should use TSQL
Mark PM  2010-10-11 11:41:32
i didn't use any template. but i saw an sql statement that, when i ran it in sql server, it was fast. was it because of the nhibernate? that sql statement was the agencyproduct
Musikero31  2010-10-12 02:18:28
It might be indexing issue as when you run it via SQL it will have different connection settings. Do you have an index on the table? Try updating the stats on the table.
Mark PM  2010-10-12 08:05:53
It has the same connection string with that of SQL. Is it possible that it could be because there's a missing value for the ID? This only happens upon inserting.
Musikero31  2010-10-13 07:19:07
Is your ID field an identity column? The connection will have different properties (eg nocount on etc...)
Mark PM  2010-10-13 15:38:46
My mistake, it happens when it updates. Yes, it is an identity column. But what's weird is that it should automatically update in nhibernate if there's an existing ID. however, it seems that there's something fishy with this.
Musikero31  2010-10-14 02:49:21
Another weird thing is that it points to the correct database with an existing table. It updates/adds correctly. However, when it is the 2nd to the last record to be inserted, then it gets a timeout. Is it possible that the table gets locked during the process? If so, how can I unlock it?
Musikero31  2010-10-14 07:43:22
That should be easy to check, simply run sp_who2 in SQL Server when the statement runs and check if it is blocked and by which process.
Mark PM  2010-10-14 08:57:18
I ran sp_who2 and it is not blocked. I wonder what would cause the "Could Not Load an Entity" and at the same time, throw an inner exception of "Timeout Expired"?
Musikero31  2010-10-14 10:43:35
You are getting "Couldn't load entity" because of the timeout.
Mark PM  2010-10-14 10:58:24
So what could be the solution here if it is not blocked?
Musikero31  2010-10-15 03:48:16
The only other explanation is that the indexes are fragmented and need to rebuild.
Mark PM  2010-10-15 17:01:54
How do I do rebuild that?
Musikero31  2010-10-20 05:59:46
Here is one way:http://technet.microsoft.com/en-us/library/ms187874.aspx
Mark PM  2010-10-20 14:15:10
A: 

We found the solution to the issue.

It seems that NHibernate is having a hard time when it changes from update to insert. The problem is that when there's an insert before an update in bulk transactions, this error happens. What we did was we separated the update and the insert transactions and it worked.

I'm wondering if this is because of the NHibernate dll. Any thoughts on this?

Musikero31  2010-10-21 02:50:59

related questions

Displaying Flash content in a C# WinForms application
How to get the value of built, encoded ViewState?
Unhandled Exception Handler in .NET 1.1
How do I connect to a database and loop over a recordset in C#?
How do I most elegantly express left join with aggregate SQL as LINQ query
Get a new object instance from a Type in C#
.NET Testing Framework Advice
Automatically update version number
What is the difference between an int and an Integer in Java/C#?
How to write to Web.Config in Medium Trust ?
WinForms ComboBox data binding gotcha
How do you sort a C# dictionary by value?
Adding Scripting functionality to .NET applications
Floating Point Number parsing: Is there a Catch All algorithm?
How do I print an HTML document from a web service?
Decoding T-SQL CAST in C#/VB.net
Anatomy of a "Memory Leak"
How do I get a distinct, ordered list of names from a DataTable using Linq
Reliable Timer in a Console Application
How do I fill a DataSet or a DataTable from a LINQ query resultset ?
What's the difference between Math.Floor() and Math.Truncate() in .NET?
How do I calculate relative time?
How do I calculate someone's age in C#?
Are there any conversion tools for porting Visual J# code to C#?
When setting a form's opacity should I use a decimal or double?