Drop 过程语句出现死锁

发布于 2024-11-04 02:05:23 字数 3798 浏览 4 评论 0原文

我一直致力于通过多线程创建对象来提高数据库（包含数千个对象）的安装速度。这导致了 DROP PROCEDURE 语句出现死锁的不幸行为。

单线程部署花费了很长时间（因为我们正在讨论很多数据库对象。扭转模式并不是一件容易的事，因为模式安装在几百个客户端上。）。缓慢的部署阻碍了我们的开发/发布周期。

该脚本包含以下代码

IF OBJECT_ID(N'myProc') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc
  END
GO
CREATE PROC....

与第二个脚本包含的

IF OBJECT_ID(N'myProc2') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc2
  END
GO
CREATE PROC....

这些程序完全无关。没有任何依赖性。

死锁图如下所示：

<deadlock-list>
 <deadlock victim="process6c3dc8">
  <process-list>
   <process id="process6c3dc8" taskpriority="0" logused="884" waitresource="OBJECT: 25:1949249999:0 " waittime="3834" ownerId="3008593" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xa882b950" lockMode="Sch-S" schedulerid="3" kpid="2588" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPc" hostpid="7296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008593" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="264" stmtend="352" sqlhandle="0x0200000092ebe0126e0f90268e2a5bf1eaba70a098515cd9">
DROP PROCEDURE myProc2     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N&apos;myProc2&apos;) is not null
BEGIN
  PRINT N&apos;Dropping procedure myProc2 ...&apos;
  DROP PROCEDURE myProc2
END    </inputbuf>
   </process>
   <process id="processaa4242c8" taskpriority="0" logused="5800" waitresource="OBJECT: 25:1965250056:0 " waittime="3834" ownerId="3008596" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xab493950" lockMode="Sch-S" schedulerid="2" kpid="5768" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPC" hostpid="8296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008596" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="276" stmtend="370" sqlhandle="0x02000000f019293427b8052cc3d5d18be886f958c4b750a1">
DROP PROCEDURE myProc     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N&apos;myProc&apos;) is not null
BEGIN
  PRINT N&apos;Dropping procedure myProc ...&apos;
  DROP PROCEDURE myProc
END    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1949249999" subresource="FULL" dbid="25" objectname="1949249999" id="lock87308e00" mode="Sch-M" associatedObjectId="1949249999">
    <owner-list>
     <owner id="processaa4242c8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c3dc8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1965250056" subresource="FULL" dbid="25" objectname="myDatabase.dbo.myProc2" id="lock878d9e80" mode="Sch-M" associatedObjectId="1965250056">
    <owner-list>
     <owner id="process6c3dc8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="processaa4242c8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

原文

I have been working on improving the installation speed of our database (with thousands of objects) by multi threading the creation of the objects. This has caused the unfortunate behavior of causing deadlocks on the DROP PROCEDURE statements.

Single threaded the deployment was taking a very long time (Since we are talking about a lot of database objects. Turning around the schema is not something to do lightly as the schema is installed at a few hundred clients.). The slow deployment is holding back our development/release cycle.

The scripts contain the following code

IF OBJECT_ID(N'myProc') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc
  END
GO
CREATE PROC....

And the second script contains

IF OBJECT_ID(N'myProc2') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc2
  END
GO
CREATE PROC....

These procedures are completely unrelated. No dependencies what so every.

The deadlock graph can be seen below:

<deadlock-list>
 <deadlock victim="process6c3dc8">
  <process-list>
   <process id="process6c3dc8" taskpriority="0" logused="884" waitresource="OBJECT: 25:1949249999:0 " waittime="3834" ownerId="3008593" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xa882b950" lockMode="Sch-S" schedulerid="3" kpid="2588" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPc" hostpid="7296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008593" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="264" stmtend="352" sqlhandle="0x0200000092ebe0126e0f90268e2a5bf1eaba70a098515cd9">
DROP PROCEDURE myProc2     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N'myProc2') is not null
BEGIN
  PRINT N'Dropping procedure myProc2 ...'
  DROP PROCEDURE myProc2
END    </inputbuf>
   </process>
   <process id="processaa4242c8" taskpriority="0" logused="5800" waitresource="OBJECT: 25:1965250056:0 " waittime="3834" ownerId="3008596" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xab493950" lockMode="Sch-S" schedulerid="2" kpid="5768" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPC" hostpid="8296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008596" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="276" stmtend="370" sqlhandle="0x02000000f019293427b8052cc3d5d18be886f958c4b750a1">
DROP PROCEDURE myProc     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N'myProc') is not null
BEGIN
  PRINT N'Dropping procedure myProc ...'
  DROP PROCEDURE myProc
END    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1949249999" subresource="FULL" dbid="25" objectname="1949249999" id="lock87308e00" mode="Sch-M" associatedObjectId="1949249999">
    <owner-list>
     <owner id="processaa4242c8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c3dc8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1965250056" subresource="FULL" dbid="25" objectname="myDatabase.dbo.myProc2" id="lock878d9e80" mode="Sch-M" associatedObjectId="1965250056">
    <owner-list>
     <owner id="process6c3dc8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="processaa4242c8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

追星践月 2024-11-11 02:05:23

通过检查 DROP、CREATE 或 ALTER 过程的不同命令取出的锁，我相信您可以通过更改您使用的模式来解决您的特定问题：

IF OBJECT_ID(N'myProc') IS NULL
    EXEC sp_executesql N'Create Proc myProc as RETURN 0'
GO
ALTER PROC myProc 
AS ...

当我查看 DROP PROC 取出的锁时，我发现：

在resource_type =“METADATA.AUDIT_ACTIONS”和resource_type =“METADATA.PERMISSIONS”上取出Sch-M锁
在存储过程引用的表上取出Sch-S锁系统对象上的所有
其他锁都是 X 或 IX

当我查看 CREATE PROC 所采用的锁时，我看到：

在过程本身上取出了 Sch-M 锁
在表上短暂取出了 Sch-S 锁sproc 引用（并释放 <-- 更正）
所有其他锁在系统对象上都是 X 或 IX

当我查看 ALTER PROC 所获取的锁时，我发现：

在该过程上取出了一个 Sch-M 锁本身
An Sch-S 锁在存储过程的先前编译版本所引用的表上被取出（如果仅在新版本中将被短暂取出），
所有其他锁都是系统对象上的 X 或 IX

所以我相信您当前的死锁有与访问元数据资源有关，这可以通过切换到 ALTER 模式来缓解。

然而，Sch-M 和 Sch-S 锁仍然会发挥作用，只是以不同的方式——因此，如果您确实有其他相互引用的存储过程，则仍然可能出现不同的死锁。

附加评论：我很想知道为什么对象创建通常需要这么长时间。除了死锁之外，是否真的创建了需要时间的存储过程？我的猜测是，问题与表创建和填充有关，我想确保您配置了即时文件初始化、正确配置了数据文件增长设置、设置了恢复模型和/或日志备份，并且当脚本运行时，不仅仅是等待文件增长和清零。

From examining the locks taken out by different commands that DROP, CREATE, or ALTER procedures, I believe you could resolve your particular issue by changing the pattern you use to:

IF OBJECT_ID(N'myProc') IS NULL
    EXEC sp_executesql N'Create Proc myProc as RETURN 0'
GO
ALTER PROC myProc 
AS ...

When I look at the locks taken out by a DROP PROC, I see that:

Sch-M locks are taken out on resource_type="METADATA.AUDIT_ACTIONS" and resource_type="METADATA.PERMISSIONS"
Sch-S lock is taken out on a table the sproc refers to
all other locks are X or IX on system objects

When I look at the locks taken a CREATE PROC, I see that:

An Sch-M lock is taken out on the procedure itself
An Sch-S lock is taken out briefly on a table the sproc refers to (and released <-- corrected)
all other locks are X or IX on system objects

When I look at the locks taken by an ALTER PROC, I see that:

An Sch-M lock is taken out on the procedure itself
An Sch-S lock is taken out on a table the previously compiled version of the sproc referred to (and if only in the new version will be taken out briefly)
all other locks are X or IX on system objects

So I believe your current deadlocks have to do with access to the MetaData resources, and that could be alleviated by switching to the ALTER pattern.

However, Sch-M and Sch-S locks will still be in play, just in a different way-- so different deadlocks could still be possible if you do have other sprocs which refer to each other.

Additional comment: I would be curious to know why object creation is taking so long in general. Aside from the deadlocks-- is it actually creating stored procedures that's taking the time? My guess would be that the problem has to do with table creation and population, and I'd want to make sure you have Instant File Initialization configured, data file growth settings configured properly, recovery model and/or log backups set up, and aren't just waiting for files to grow and zero out when your scripts are running.

回复收藏 0 原文

红焚 2024-11-11 02:05:23

防止死锁是一个可以填满书架的主题，但作为起点：创建一个将对象与其依赖项相关联的多字典。如果您使用 C# 作为部署应用程序，它可能会启动如下内容：

var  dependencies = new Dictionary<string, HashSet<string>>();  // I recommend that you write a MultiDictionary class to cover situations like this, I've found it very useful

book OKToCreateSproc(string sprocName)
  {
  foreach (string dependency in dependencies[sprocName])
    if (createdObjects.Contains(dependency) == false)
      return false;
  return true;
  }

请注意，您需要一个线程安全的集合，并且我不相信普通的通用字典是安全的。看起来这个问题已经在这里得到了很好的解决：实现线程安全字典的最佳方法是什么？。

如果您觉得自己很聪明，可以通过抓取 DDL 脚本以编程方式填充依赖项，但这可能有点过头了，除非您有一个非常复杂的数据库。

哦，是的，您也可以捕获死锁，将有问题的存储过程推到队列末尾，然后再试一次。粗暴，但有效！

Preventing deadlocks is a topic to fill a bookshelf, but as a starting point: create a multi-dictionary which relates objects to their dependencies. If you're using C# for your deployment app, it might start something like this:

var  dependencies = new Dictionary<string, HashSet<string>>();  // I recommend that you write a MultiDictionary class to cover situations like this, I've found it very useful

book OKToCreateSproc(string sprocName)
  {
  foreach (string dependency in dependencies[sprocName])
    if (createdObjects.Contains(dependency) == false)
      return false;
  return true;
  }

Note that you'd need a thread-safe collection, and I don't believe the vanilla generic Dictionary is safe. It looks like this has been well addressed here: What's the best way of implementing a thread-safe Dictionary?.

If you're feeling clever, you can programmatically populate dependencies by scraping your DDL scripts, but that's probably overkill unless you have a very complex database.

Oh yeah, you could also just catch the deadlocks, push the problem sproc to the end of the queue, and try it again later. Crude, but effective!

回复收藏 0 原文