禁用触发器挂起？ [英] Disable trigger hangs?

问题描述

我需要通过ASP.NET Web应用程序执行此操作：

I need to do this from an ASP.NET web app:

Alter Table Contacts Disable Trigger All 
-- Do some stuff
Alter Table Contacts Enable Trigger All

在某些情况下禁用触发器语句挂起。我应该从哪里开始寻找导致此问题的原因？如果我重新启动SQL Server，它将恢复为正常运行。

In some situations the Disable Trigger statement hangs. Where should I start looking to figure out what's causing this? If I restart SQL server it goes back to behaving normally.

推荐答案

从SSMS中查看活动监视器以查看为什么它会阻止。或者您可以查看 blocking_session_id 列是 sys.dm_exec_requests 。

Look into the Activity Monitor from SSMS to see why it blocks. Or you can look into blocking_session_id column is sys.dm_exec_requests.

我的猜测：模式更改需要锁定模式修改桌子。任何操作（如SELECT，UPDATE等）都将在表上放置模式稳定性锁，从而阻止任何ALTER，直到SELECT完成。因此，Disable Trigger ALTER会被所有未决的表访问（SELECT）语句所掩盖。

My guess: schema changes require a schema modification lock on the table. Any operation (like SELECT, UPDATE etc) will place a schema stability lock on the table, blocking any ALTER until the SELECT completes. So the Disable Trigger ALTER is blcoked by all the pending table access (SELECT) statement.

这篇关于禁用触发器挂起？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！