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? Any tools or sample codes which could be used to reproduce this scenario?
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]
DELETE FooTable WHERE Param1 = @Param1
INSERT INTO FooTable
View Complete Post