通过NHibernate查询SQL Server时超时 [英] Timeout expired when querying SQL Server via NHibernate

问题描述

我有一个通过NHibernate使用SQL Server 2005的ASP.Net MVC应用程序.我偶尔会收到以下错误消息:

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):超时已过期. 在操作完成或操作完成之前已过超时时间 服务器没有响应.在 System.Data.SqlClient.SqlConnection.OnError(SqlException异常， 布尔值breakConnection)在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException 例外，布尔值breakConnection)位于 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior， SqlCommand cmdHandler，SqlDataReader dataStream， BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj)在 System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData，布尔值moreInfo)位于 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior， SqlCommand cmdHandler，SqlDataReader dataStream， BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj)，位于System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 在System.Data.SqlClient.SqlDataReader.get_MetaData()在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds， RunBehavior runBehavior，字符串resetOptionsString)在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior，RunBehavior，runBehavior，布尔returnStream，布尔 异步) System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior，RunBehavior，runBehavior，布尔值returnStream，字符串 方法，DbAsyncResult结果)位于 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior，RunBehavior，runBehavior，布尔值returnStream，字符串 方法) System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior 行为，String方法)在 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior 行为) System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
在NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
在NHibernate.Loader.Loader.GetResultSet(IDbCommand st，Boolean autoDiscoverTypes，布尔型可调用，RowSelection选择， ISessionImplementor会话)位于 NHibernate.Loader.Loader.DoQuery(ISessionImplementor会话， QueryParameters queryParameters，Boolean returnProxies)位于 NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor 会话，QueryParameters queryParameters，布尔值returnProxies)
在NHibernate.Loader.Loader.DoList(ISessionImplementor会话， QueryParameters queryParameters)"

"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..."

被以下人员阻止:

"SELECT TOP 10 Id, Name FROM Users"

查看阻塞语句所持有的锁，与用户表无关的各种表上有大约十个互斥(X)和(IX)页面以及键锁(但这是被阻塞查询的一部分) ).

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).

这里不涉及任何更新，插入或删除-那么为什么一个只读查询会阻塞另一个查询，以及为什么对Users表的简单查询会导致许多其他表的锁定.

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.

推荐答案

这将导致问题:

begin transaction 

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

select top 10 Id, Name from Users

commit

在更新过程中在文档表上创建的锁直到您提交事务后才会释放.这意味着连接可以将select语句作为其最近的语句，但仍保持先前更新的锁.

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.

一些想法:

1. 确保您要提交/回滚交易
2. 尝试减少交易的时间(例如，在上面，在运行select之前提交是安全的吗?).
3. 通过确保外键已建立索引来优化更新联接，也可以加快事务处理.

请注意，选择本身只会生成共享锁.

Note that a select itself will only generate shared locks.

最后要提防(nolock)锁定提示的想法.读取未提交的数据很少是一个好主意.

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

这篇关于通过NHibernate查询SQL Server时超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！