DeadLock on Single table with mutiple Update statement from 3 different stored procedure

Deadlock Scenario is more likely:

Same records in same table (***_tab) is update with 3 different SP parallel, but the columns are different (col1,col2,col3).

Table:

CREATE TABLE ***_tab
(
member_id int not null,
state_desc char(2),
business_type varchar(5),
col1 char(1),
col2 char(1),
col3 char(1)
)

go

Index:
Create clustered index CL_index_***
on ***_tab (member_id, state_desc)
Go
This 3 SPs are running parllel to update the same records in same table, but different columns.

@ElligibleMember will have some record for processing in batch, say for example 100

INSERT INTO @ElligibleMember
SELECT top (@batchSize) sm.member_id FROM ***_tab sm WITH (NOLOCK)
WHERE col1 ='N' AND state_desc=@state

Proc1:

UPDATE em SET em.col2='Y'

FROM ***_tab em WITH (HOLDLOCK)
INNER JOIN @ElligibleMember ema ON ema.member_id=em.member_id

Proc2:
UPDATE em SET em.col1='Y'
FROM ***_tab em WITH (HOLDLOCK)
INNER JOIN @ElligibleMember emp ON emp.member_id=em.member_id

Proc3:
UPDATE em SET em.col3='Y'
FROM ***_tab em WITH (HOLDLOCK)
INNER JOIN @ElligibleMemberStatus ems ON ems.member_id=em.member_id

Deadlock happens when change to nonclusterd index

1)      P2 (Exclusive)locks the Rowid in Page id = 1743304

2)      P1(Update)locks the keylock(Identifies the key range within an index on which a lock is held or requested) on HOBT(Represents a heap or b-tree involved in a deadlock) in index “NDX_index_***”

3)      P2 is waiting for P1 locked resource

4)      P1 is waiting for P2 locked resource

 

Dead lock happens when change to record selection using NEWID() and HOLDLOCK,

First Dead lock
process6063dc8(P1) = SP1
processe23b708(P2) = SP2
      Resource(R1) ridlock fileid=1 pageid=1789383
            Owner = P1, waiter = P2
      Resource(R2) ridlock fileid=1 pageid=1789411
            Owner = P2, waiter = P1
Second Dead Lock         
processe26ddc8(P1) =  SP1
process12226988(P2) = SP2
      Resource(R1) ridlock fileid=1 pageid=1418983
            Owner = P1, waiter = P2
      Resource(R2) ridlock fileid=1 pageid=1418994
            Owner = P2, waiter = P1

 We have tried recommendation to eliminate the deadlock

1) Covered the Update statement with a transaction block and Join the Temp table variable in INNER JOIN which is not recommended but we tested to eliminated the deadlock

2) Tried removing non-clustered index and created Clustered index , added WITH(HOLDLOCK) in update statement.

3) And give NEWID(), in the select statement to picks the random record from selecting the different rows in other SP, so that 3 sp can update random records.

4) Eliminate join in Update, because the Temp table Variable records are not deleted, so planned to use Update (@batchSize) as select Statement

5) Place the With(UPDLOCK) in Select so that it can prevent the records being read by other SPs

6) Planned to Catch the deadlock in try..catch block and redo the process(Update statement), which is not recommended in medisys, we are trying to eliminate the deadlock.

7)Change the logical flow of 3 SP by sequential

 

Solution in our scenario got by 7^th option

                7)Change the logical flow of 3 SP to sequential

 

DDL statement(schema changes) will degrade Stored Procedure

The main use of stored procedure is:

• The written code in stored procedure is precompiled and it can be executed from Execution plan stored in system cached objects.
• The code with DDL statement(schema change) in a stored procedure will not be stored in Cache for reuse.

Note: It will not throw any error, but performance will degrade.

 Below  example will elaborate this type of scenario:

Create proc test1

as
begin
select 1 as no into #temp123
--Alter table #temp123 add no1 int
end

go

EXEC test1
go

Create proc test2

as
begin
select 1 as no into #temp123
Alter table #temp123 add no1 int
end

go
EXEC test2
go

select * from sys.syscacheobjects where sql like '%test%' and objtype='Proc'

go

Drop proc test1,test2

The different between the two SP is “Test2 has DDL statement in it”

When we run the above script we can get

We can find the SP using the below statement:

select object_name(id),* from syscomments where text like '%ALTER TABLE%'

Please let me know for any clarification.