Timeout expired when querying SQL Server via NHibernate

Question

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.

Answer 1

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:

<session-factory>
    ...
    <add key="hibernate.connection.isolation" value="ReadCommitted" />
    ...
</session-factory>

Answer 2

This would cause the issue:

begin transaction 

update Documents
set SomeField = 'SomeValue'
where SomeCondition = 'XXXX'

select top 10 Id, Name from Users

commit

The locks created on the document table during the update won't be released until you commit the transaction. This would mean that a connection could have the select statement as its most recent statement, but still be holding locks from the previous update.

Some ideas:

1. Make sure you are committing/rolling back your transactions
2. Try to reduce the length of your transactions (e.g. in the above, would it be safe to commit before running the select?).
3. Optimizing the join on the update by making sure the foreign key is indexed might also speed up the transaction.

Note that a select itself will only generate shared locks.

Finally beware of the (nolock) lock hint idea. Reading uncommitted data is rarely a good idea.