Why single SQL delete statement will cause deadloc

Question

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?

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

Answer 1

If a large table BarTable has a foreign key constraint referencing FooTable, then the deletion of one single row in FooTable needs to check all of BarTables rows for references to that row.

Not only is this prohibitively slow, it gets worse: while doing this check, two concurrent deletions of two single rows in FooTable can get deadlocked on the primary key of BarTable(!)

If you create an index on BarTable for the column(s) that reference FooTable, the deletion can use that index to dramatically speed things up.

So:

Make sure that your foreign key constraints are backed up by indices.

Answer 2

I don't have experience with concurrency but there are 2 things in your procedure that I would change (and maybe fixing your deadlock):

• Wrap your whole procedure in a transaction. This is to prevent a scenario like FooProc 1 gets called and is about to execute the SELECT statement while FooProc 2 has just executed the DELETE statement having both the same @Param1.
• Don't use @@Identity, use SCOPE_IDENTITY instead.

Interesting link about @@Identity vs SCOPE_IDENTITY() vs IDENT_CURRENT()

Answer 3

The usual answer: it depends! :-)

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

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.

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.

Read up on SQL Transaction Isolation Levels here on www.sql-server-performance.com.