单个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代码调用存储过程。 
 
 我在表FooTable上有3个操作。 并且多个连接将按顺序同时执行它们，即执行delete，execute insert然后执行select。 每个语句（删除/插入/选择）都是单存储过程中的单独的单个事务。 
 
 我的问题是删除语句是否可能发生死锁？ 我的猜测是，如果多个连接在相同的Param1值上运行，是否可能发生死锁？ 
 
 BTW：对于下面的语句，Param1是表FooTable的列，Param1是另一个表的外键（指另一个表的另一个主键聚簇索引列）。 对于表FooTable，Param1本身没有索引。 FooTable有另一列用作聚簇主键，但不是Param1列。 
 
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
 
 以下是活动监视器表的外观， 
 
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)  

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.
 
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.
 
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: 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)  

原文：https://stackoverflow.com/questions/3075885