单个SQL Server表上的死锁 [英] deadlock on a single SQL Server table

问题描述

我正在使用SQL Server 2008 Enterprise。并使用ADO.Net + C＃+ .Net 3.5 + ASP.Net作为客户端来访问数据库。当我访问SQL Server 2008表时，我总是从C＃+ ADO.Net代码中调用存储过程。

I am using SQL Server 2008 Enterprise. And using ADO.Net + C# + .Net 3.5 + ASP.Net as client to access database. When I access SQL Server 2008 tables, I always invoke stored procedure from my C# + ADO.Net code.

我在表FooTable上有3个操作。并且多个连接将按顺序同时执行它们，即执行删除，执行插入然后执行选择。 每个语句（删除/插入/选择）在单个存储过程中是一个单独的独立交易。

I have 3 operations on table FooTable. And Multiple connections will execute them at the same time in sequences, i.e. executes delete, the execute insert and then execute select. Each statement (delete/insert/select) is of a separate individual transaction in the single store procedure.

我的问题是是否有可能该死锁将发生在删除语句上？ 我的猜测是，如果多个连接在相同的Param1值上运行，是否有可能发生死锁？

My question is whether it is possible that deadlock will occur on delete statement? My guess is whether it is possible that deadlock occurs if multiple connections are operating on the same Param1 value?

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

BTW: For the statements below, 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

这里是活动监视器表的样子，

Here is what the activity monitor table looks like,

ProcessID System Process Login Database Status Opened transaction Command Application Wait Time Wait Type CPU 
52 No   Foo suspended 0 DELETE .Net SqlClient Data Provider 4882 LCK_M_U 0 
53 No George Foo suspended 2 DELETE .Net SqlClient Data Provider 12332 LCK_M_U 0 
54 No George Foo suspended 2 DELETE .Net SqlClient Data Provider 6505 LCK_M_U 0 
(a lot of rows like the row for process ID 54)  

推荐答案

我会在Param1上向FooTable添加索引；没有它，DELETE会进行全表扫描，这将导致死锁问题。

I would add an index on Param1 to FooTable; without it, the DELETE is doing full table scan, and that'll create problems with deadlocks.

编辑

根据您的活动详细信息，它看起来像您有死锁，有阻塞，在进行一次删除时，许多删除正在排队。同样，在Param1上建立索引可以减轻这种情况，如果没有索引，则每次删除都将进行全表扫描以查找要删除的记录，而这种情况下，其他删除必须等待。如果在Param1上有索引，它将更快地处理，并且您现在看不到阻塞。

Based on your activity details, it doesn't look like you have deadlocks, you have blocking, many deletes are queueing up while one delete takes place. Again, indexing on Param1 would alleviate this, without it, each delete is going to do a full table scan to find the records to delete, while that is happening, the other delete's have to wait. If you have an index on Param1, it'll process much quicker and you won't see the blocking you are now.

如果您有死锁，系统将终止其中一个涉及的过程，否则将永远不会进行任何处理；带有阻塞的东西会处理，但是如果表很大，就会很慢。

If you have deadlocks, the system will kill one of the involved processes, otherwise nothing would ever process; with blocking, things will process, but very slowly if the table is large.

这篇关于单个SQL Server表上的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！