为什么单个SQL删除语句会导致死锁？

1 人关注

我正在使用SQL Server 2008企业版。我想知道为什么这个存储过程的一个删除语句如果被多个线程同时执行，也会导致死锁？

对于删除语句，Param1是表FooTable的一个列，Param1是另一个表的外键（指另一个表的主键聚类索引列）。对于表FooTable来说，Param1本身没有索引。FooTable有另一列被用作聚类主键，但不是Param1列。

create PROCEDURE [dbo].[FooProc]    
 @Param1 int 
 ,@Param2 int  
 ,@Param3 int  
DELETE FooTable WHERE  Param1 = @Param1
INSERT INTO FooTable    
 Param1  
 ,Param2  
 ,Param3  
 VALUES    
 @Param1  
 ,@Param2  
 ,@Param3  
DECLARE @ID bigint
SET @ID = ISNULL(@@Identity,-1)    
 IF @ID > 0
BEGIN
SELECT IdentityStr FROM FooTable WHERE ID = @ID
预先感谢。


           
            
             Remus Rusanu
            
            ：


            
             
              捕获死锁图并将其添加到你的帖子中。在那之前，我们都只是猜测。参见
              
               msdn.microsoft.com/en-us/library/ms190465.aspx
              
              了解如何捕获死锁图。


            
             
              嗨，Remus，如何触发死锁？有什么工具或示例代码可以做压力测试吗？


            
             
              通过查看你的程序，我注意到你删除了基于Param1的所有行，然后只插入了1行。你不能使用UPDATE语句吗？


            
             
              谢谢ZippyV!我正在维护一些遗留的代码，我想尽可能地减少代码的变化。有什么想法，为什么删除语句会出现死锁？


         
          sql-server-2008


         
          deadlock


          
           已采纳


           
            
             通常的答案是：看情况而定!:-)
            
            
             主要是看你的系统有多少流量，以及你使用的交易隔离级别。
            
            
             隔离级别控制你如何获得数据，以及有多少锁正在进行。
如果你从来没有听说过事务隔离级别，你可能正在使用默认的--READ COMMITTED，这应该不是一个太坏的选择。
            
            
             然而，如果你出于任何原因使用像
             
              
               SERIALIZABLE
              
             
             这样的东西，你可能会遇到不是死锁，而是延迟。该表可能会被锁定一段时间，直到你的一个事务完成。如果所有的操作都按这个顺序进行（首先是删除，然后是插入，然后是选择），我看不出你应该遇到任何死锁，真的。
            
            
             在这里阅读关于SQL事务隔离级别的内容，
             
              www.sql-server-performance.com。


           
            
             Martin Smith
            
            ：


            
             
              我在@George2之前的死锁线程中声称，如果两个并发的删除程序最终都有一个并行的执行计划，并且以 "正确 "的顺序分配页锁，就会导致死锁。然而，我刚刚在测试中试图让这种死锁发生，但没有做到。我没有时间进一步研究这个问题，如果我误导了你，请原谅 @George2!


            
             
              嗨，马克，有什么工具或示例代码可以用来重现死锁的情况吗？


            
             
              嗨，马丁，有什么想法，为什么在我的场景中出现了死锁？有什么工具或示例代码可以用来重现死锁的情况吗？


            
             
              有什么想法，为什么在我的情况下会出现死锁？有什么工具或示例代码可以用来重现死锁的情况吗？


           
            
             我没有并发方面的经验，但在你的程序中，有两点我会改变（也许还能修复你的死锁）。
            
            
             将你的整个过程包裹在一个事务中。这是为了防止出现这样的情况：FooProc 1被调用并准备执行SELECT语句，而FooProc 2刚刚执行了DELETE语句，两者都有相同的参数1。
并准备执行SELECT语句，而FooProc 2刚刚执行了DELETE语句，两者都有相同的@Param1。
            
            
             不要使用@@Identity，使用SCOPE_IDENTITY代替。
            
            
             关于 @@Identity vs SCOPE_IDENTITY() vs IDENT_CURRENT() 的有趣的
             
              链接


            
             
              谢谢ZippyV，有什么想法吗，为什么在我的场景中会出现死锁？有什么工具或示例代码可以用来重现死锁的情况吗？


            
             
              如果表FooTable很大，你认为我的方案会造成死锁吗？


            
             
              这取决于Param1。只要实施我建议的2个变化，看看你是否还能得到死锁问题。


            
             
              "这取决于Param1" -- 我对这一点感兴趣。你能更多地解释为什么是否存在死锁将取决于Param1（其值？）


            
             
              这是具有相同Param1值的行的数量，以及有多少程序在同一时间执行相同的Param1值。


           
            
             如果一个大表BarTable有一个引用FooTable的外键约束，那么删除FooTable中的一条记录时，需要检查BarTables的所有记录对该行的引用。
            
            
             这不仅慢得令人望而却步，而且情况更糟：在做这个检查时，FooTable中两行的并发删除会在BarTable的主键上出现死锁（！）。