I have an ASP.Net MVC application that uses SQL Server 2005 via NHibernate. I am getting getting the following error message sporadically:
"System.Data.SqlClient.SqlException (0x80131904): 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.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj) at
System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet
metaData, Boolean moreInfo) 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.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, 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.DoList(ISessionImplementor session,
QueryParameters queryParameters)"
Most of the time the system works fine and is very responsive. I have looked into the locks being held on the system and what I typically find is there is one query being blocked by another query:
"SELECT TOP 10 d.Id, d.Name FROM Documents d INNER JOIN Users u ON..."
is being blocked by:
"SELECT TOP 10 Id, Name FROM Users"
Looking at the locks being held by the blocking statement there are around a dozen exclusive (X) and (IX) page and key locks on various tables unrelated to the Users table (but that are part of the query that is being blocked).
There are no UPDATES, INSERTS or DELETES involved here – so why would one read-only query block the other and why would a simple query on the Users table cause locks on lots of other tables.
Best Answer
A SQL Server gotcha is a non-default "isolation-level" setting on the database. SQL Server has a default isolation-level (locking strategy) of ReadCommitted which means that read locks are released as soon as possible but write locks are not released until a transaction is committed. In Andy's answer, the default isolation-level will cause the select to be blocked. However if the database is set to either RepeatableRead or Serializable, then selects will always block each other for the duration of a transaction. This setting can be controlled in NHibernate with this hibernate.cfg.xml config: