死锁时，SQL Server事务日志不断增加？ [英] deadlock when SQL Server transaction log ever-increase?

问题描述

我使用SQL Server 2008企业版。我已经试过了，如果我设置SQL Server事务日志，以不断增加对相关的数据库（没有备份设置），然后由多个线程在同一时间，如果执行此存储过程的一个DELETE语句会导致死锁？任何想法，为什么？

I am using SQL Server 2008 Enterprise. I have tried that if I set SQL Server transaction log to ever increase for the related database (with no backup settings), then a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time? Any ideas why?

有关delete语句，参数1是表FooTable的一列，参数1是另一个表的外键（指的是其他表的另一主键聚集索引列）。有关于参数1本身的表FooTable没有索引。 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

推荐答案

作为一个经验法则，你应该总是创建具有FOREIGN KEY约束的列的索引。否则，僵局很可能发生（因为服务器已经锁定全依赖表，以确保约束）

As a rule of thumb, you should always create an index on the column that has FOREIGN KEY constraint. Otherwise, deadlock is very likely to happen (because the server has to lock the whole dependent table to ensure that constraint)

这篇关于死锁时，SQL Server事务日志不断增加？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！