NHibernate SQL Server 挂起进程
遇到了一个问题，我希望有人可以帮助给我一些指导。 几年前正在开发一个基于 .NET 3.5 和 NHibernate 1.2 的应用程序。 过去一周遇到问题，交易似乎随机锁定。我注意到 NH 的 command_timeout 为 100000。我现在已将其降至 30，因为 100000 看起来很疯狂！这些问题仍然存在。
当问题发生时，SQL Server 活动监视器显示阻塞事务，通常处于 SUSPENDED 状态。如果我终止阻塞进程，那些被阻塞的进程就会关闭，系统就正常了。
对阻塞进程进行检查，我可以看到正在执行的大约 SQL 语句导致了问题，但它是通过系统的大部分执行的语句，检索大多数页面上的通用数据。
我认为是什么导致了这种情况？没有什么明显的...我们确实发布了一些小更改，但它们都不应该具有这种效果。最近大约在同一时间还安装了 Windows 更新/服务包。 SQL/.NET SP 更新可能是此问题的原因吗？
我知道这里有很多东西，但我会尽我所能去奉献。
新信息： 现在 command_tinmeout 已更新为 30 秒，超过此执行时间的查询将被自动终止 - 至少我在活动监视器中看到。然而，似乎还发生了其他事情，因为重复查询也会因超时而失败。但是，如果我停止并启动应用程序池，一切都会开始正常工作，至少在一段时间内如此。
Have run into an issue that i am hoping somebody can help give me some direction on.
Working on an application that is several years on, on .NET 3.5 and NHibernate 1.2.
Have been having a problem over the past week where transaction seem to lock up randomly. I noticed NH had a command_timeout of 100000. I have now brought this down to 30 as 100000 seemed crazy! The issues are still occurring.
SQL Server Activity Monitor shows a blocking transaction when the issue occurs, normally in the SUSPENDED state. If i kill the blocking process, those processes being blocked fire off and the system is fine.
Running a check on the blocking process, i can see the approx SQL statement being executed that is causing the issue, but it's a statement that is executed through much of the system, retrieving common data across most pages.
What do I think could have caused this? Nothing obvious... we did release a couple of small changes, but none of them should have this effect. Windows updates/service packs were recently installed as well about the same time. Could SQL/.NET SP updates be the cause of this issue??
I know there is a lot here, but trying to give as much as i have at the moment.
NEW INFO:
Now that the command_tinmeout has been updated to 30 seconds, queries that exceed this execution time are killed off automatically - or so i see in Activity Monitor. However, it seems as somthing else is going on, as repeat queries fail with timeout as well. However, if i stop and start the application pool, everything begins to work as per normal, at least for a while.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当您说这是一个非常常见的查询，您将其视为阻塞查询时，很可能是因为它经常执行，因此很可能是等待数据库中的锁的查询。
数据库运行的隔离级别是什么，如果是开箱即用的设置，则在大多数情况下将其切换到快照隔离以避免读取器等待写入器对于性能来说是非常可行的。 快照隔离的详细信息
有关 造成这种情况的原因可能是您引入了一些缓慢的写入查询、您的数据变大或者您的数据突然出现大量碎片。
When you say that it is a very common query that you see as the blocking one it is most likely because it is executed commonly and therefore is most likely to be the one waiting for a lock in the database.
What isolation level is the database running in, if its out of the box setting it is in most cases very feasible for performance to switch it to snapshot isolation to avoid readers waiting for writers. More info on Snapshot isolation
Considering what caused this, maybe you introduced some slow write queries, your data grew big or you have a lot of fragmentation in your data suddenly.