title: Timeout expired when querying SQL Server via NHibernate
body: <p>I have an ASP.Net MVC application that uses SQL Server 2005 via NHibernate. I am getting getting the following error message sporadically:</p>
  
  <blockquote>
    <p>"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()<br>
    at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)<br>
    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)<br>
    at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
    QueryParameters queryParameters)"</p>
  </blockquote>
  
  <p>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:</p>
  
  <pre><code>"SELECT TOP 10 d.Id, d.Name FROM Documents d INNER JOIN Users u ON..."
  </code></pre>
  
  <p>is being blocked by:</p>
  
  <pre><code>"SELECT TOP 10 Id, Name FROM Users"
  </code></pre>
  
  <p>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).</p>
  
  <p>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.</p>