deadlock on a single SQL Server table

Question

Hello everyone,

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)  

thanks in advance, George

Answer 1

I do not think you would get a deadlock (this is not my field of expertise), but an explicit transaction would probably be a better choice here. A scenario that comes to mind with this code is the following

Two concurrent calls to the procedure execute with Param1 value of 5, both delete and then both insert, so now you have two records with Param1 value of 5. Depending on your data consistency requirements this might or might not be a concern to you.

An alternative for you might be to actually perform an Update and if no rows are affected (check @@rowcount) then do an Insert all in a transaction of course. Or better yet, take a look at Merge to perform Insert/Update operation in a single statement.

Answer 2

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.

EDIT

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.