I am using SQL Server 2008 Enterprise. And using ADO.Net + C # + .Net 3.5 + ASP.Net as a client to access the database. When I access SQL Server 2008 tables, I always call the stored procedure from C # + ADO.Net code.
I have 3 operations on a FooTable table. And several connections will execute them at the same time in a sequence, that is, perform the deletion, insert the execution, and then perform the selection. Each statement (delete / insert / select) has a separate separate transaction in the single store procedure.
My question is, is it possible that a deadlock will occur in the delete statement? My guess: is it possible that a deadlock occurs if several connections work with the same value of the Param1 parameter?
BTW: for the instructions below, Param1 is a column of the FooTable table, Param1 is the foreign key of another table (refers to another column of the column with primary key clustering in another table). The index of the Param1 parameter for the FooTable table is missing. FooTable has another column that is used as a cluster primary key, but not a 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's what the activity monitoring 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