为什么单个SQL delete语句会导致死锁？ [英] Why single SQL delete statement will cause deadlock?

问题描述

我正在使用SQL Server 2008 Enterprise。我想知道为什么即使同时由多个线程执行此存储过程的单个delete语句也会导致死锁？

I am using SQL Server 2008 Enterprise. I am wondering why even a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time?

对于delete语句，Param1是一列在表FooTable中，Param1是另一个表的外键（是指另一个表的另一个主键聚集索引列）。表FooTable的Param1本身没有索引。 FooTable还有另一列用作群集主键，但不用作Param1列。

For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column.

create PROCEDURE [dbo].[FooProc]    
(  
 @Param1 int 
 ,@Param2 int  
 ,@Param3 int  
)    
AS    

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 
 END  

提前感谢，
乔治

thanks in advance, George

推荐答案

通常的答案：取决于！ ：-）

The usual answer: it depends! :-)

主要取决于系统上的流量以及所使用的事务隔离级别。

Mostly on how much traffic you have on your system, and what transaction isolation level you're using.

隔离级别控制您如何获取数据以及进行了多少锁定。
如果您从未听说过事务隔离级别，则可能使用的是默认值-READ COMMITTED，选择它应该不会太糟糕。

The isolation level controls how you're getting your data, and how much locking is going on. If you've never heard of transaction isolation levels, you're probably using the default - READ COMMITTED, which shouldn't be too bad a choice.

但是，如果出于任何原因使用诸如 SERIALIZABLE 之类的东西，则可能不会遇到死锁，而是会出现延迟。该表可能会锁定一段时间，直到您的一个事务完成。如果所有操作都按此顺序进行（先删除，然后插入，然后选择），我真的看不到应该如何遇到任何死锁。

However, if you'd use something like SERIALIZABLE for any reasons, you might experience not deadlocks - but delays. The table might be locked for a period of time until your one transaction completes. If all operations work in this order (first delete, then insert, then select), I don't see how you should encounter any deadlocks, really.

请继续阅读 www.sql-server-performance.com 。

这篇关于为什么单个SQL delete语句会导致死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！