How to prevent deadlock and when we using transaction update

Question

Hi ,

We are using attached store procedure to sometime we are facing deadlock issues
We have proper indexing on the table Please advice if we need change any .
How to prevent deadlock and when we using transaction update

Create PROCEDURE [dbo].[Txn_upd_ybldmr]
-- Add the parameters for the stored procedure here
@yblstringinfo varchar(8000)
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--txnid~ybltxnid~resonseDT~responsecode~statusdesc~rrn~BeneName~BeneID#

DECLARE @DH_txnid VARCHAR(80),@YBL_txnid VARCHAR(80),@ResponseDt varchar(100),@responsecode char(4),@responsecodedesc varchar(100),@RRN varchar(80),@benename varchar(80),@beneid int,@ModeType VARCHAR(10)
DECLARE @Acct_verify_status_T BIT ,@BeneName_T VARCHAR(70)
DECLARE @TXN_STATUS_T CHAR(4),@TRN _BankRemarks_T VARCHAR(100)
DECLARE @CURDATE INT, @Txn_Type VARCHAR(10)

Begin Transaction YblUpdate

While Len(@yblstringinfo)>0
Begin
Select @DH_txnid=SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @YBL_txnid=SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @ResponseDt=SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @responsecode=SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @responsecodedesc =SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @RRN =SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @benename =SUBSTRING(@yblstringinfo, 0, CHARINDEX('~', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('~', @yblstringinfo)+1), LEN(@yblstringinfo))

Select @beneid=SUBSTRING(@yblstringinfo, 0, CHARINDEX('#', @yblstringinfo))
Select @yblstringinfo =SUBSTRING(@yblstringinfo,(CHARINDEX('#', @yblstringinfo)+1), LEN(@yblstringinfo))

select @TXN_STATUS_T=Txn_Status, @TRN _BankRemarks_T =Trn_BankRemarks,@CURDATE=Txn_DateKey ,@Txn_Type=Txn_Type,
@ModeType=RIGHT(User_Trackno,3)
from yblmaster where DH_txnid=@DH_txnid

IF @benename IN ('NA','UNVERIFED')
BEGIN
SET @benename =''

END

IF @TXN_STATUS_T <> @responsecode and @TXN_STATUS_T <> '05'
BEGIN
update yblmaster set prv_status=@TXN_STATUS_T , Last_modified_ddate=GETDATE(),prv_Trn_BankRemarks=@TRN _BankRemarks_T where DH_txnid=@DH_txnid
END

IF @TXN_STATUS_T <> '05'
BEGIN
update yblmaster set Ybl_txnid=@YBL_txnid , Last_modified_ddate=GETDATE(),ReQuery_datetime=getdate(),
Txn_Status=@responsecode,Trn_BankRemarks=@responsecodedesc,
Rrrno= CASE WHEN @RRN <> '' THEN @RRN ELSE Rrrno END ,
BeneName= CASE WHEN @benename <> '' THEN @benename ELSE BeneName end where DH_txnid=@DH_txnid

END

If @@Error <> 0 And @@ROWCOUNT = 0
Begin
Select '0' AS 'Status' ,'Zero Records Updated' AS 'StatusDesc'
RollBack Transaction YblUpdate
Return
END

select @Acct_verify_status_T=Acct_verify_status,@BeneName_T=BeneName from yblbene where BeneID=@beneid

IF( @Acct_verify_status_T=0 ) and @responsecode ='00'
BEGIN
UPDATE yblbene SET BeneName = CASE WHEN @benename <> '' THEN @benename ELSE BeneName end,
Acct_verify_status =1,Modified_Date=GETDATE(),Modified_Remarks='BENE NAME UPDATE' WHERE BeneID=@beneid

END

If @@Error <> 0 And @@ROWCOUNT = 0
Begin
Select '0' AS 'Status' ,'Zero Records Updated' AS 'StatusDesc'
RollBack Transaction YblUpdate
Return
END

IF( @BeneName_T <> @benename ) and @responsecode ='00'
BEGIN

UPDATE yblbene SET BeneName = CASE WHEN @benename <> '' THEN @benename ELSE BeneName end ,Acct_verify_status =1,Modified_Date=GETDATE(),Modified_Remarks='BENE NAME NOT MATCHING' WHERE BeneID=@beneid

END

If @@Error <> 0 And @@ROWCOUNT = 0
Begin

     Select '0' AS 'Status' ,'Zero Records Updated' AS 'StatusDesc'        
     RollBack Transaction YblUpdate          

Return
END

IF @CURDATE=CONVERT(VARCHAR,GETDATE(),112)
BEGIN

UPDATE ybltrack SET statusid =1 ,ResponseDateTime=GETDATE(),Response=ISNULL(@responsecode,'')+'-'+@responsecodedesc,
Txn_Type=@Txn_Type,Txn_yblid=@YBL_txnid,ModeType=@ModeType
WHERE DH_txnid=@DH_txnid

END

If @@Error <> 0 And @@ROWCOUNT = 0
Begin

     Select '0' AS 'Status' ,'Zero Records Updated' AS 'StatusDesc'        
     RollBack Transaction YblUpdate          

Return
END

End -- LOOP END

Select '1' As 'Status' ,'Updated Successfully' AS 'StatusDesc'
Commit Transaction YblUpdate

SET NOCOUNT OFF;

--END OF THE SP

Answer 1

The deadlock includes two processes running the procedure above. As Tom pointed out, it is very procedural, and it is not really surprising that if it executes in parallel that there will be clashes. As Tom pointed out, it can be beneficiary to rewrite it to be set-based, although I should hasten to add that it is not a trivial exercise.

The deadlock involves two locks. One is a page lock. Page locks are normally not taken, but SQL Server can opt to take them under some circumstances when it thinks that row-level locks would be too many. Often this is a token of fully adequate indexing. It seems that this statement is going for the page lock:

if exists(select Tbl_txn_type from   Txn_ybl_Master where (DH_txnid =@txnid OR Ybl_txnid=@txnid ) and Tbl_txn_type='B' AND Txn_Status <> '05'

Possibly an indexes on (DH_txnid, Tbl_txn_type) INCLUDE (Txn_Status) and (Ybl_txnid, Tbl_txn_type) INCLUDE (Txn_Status) could help. However, the other lock is on an index key, where one process has updated a row, and the other process also wants to update that row. So I would say the prospects are bleak.

I think the best way to avoid the deadlock until you have rewritten the procedure - which I think you need to do in the long run - is to serialise access with application locks. Directly after BEGIN TRANSACTION, you would put this line:

EXEC sp_getapplock 'YblUpdate', 'Exclusive'

Now the process that comes in second will be blocked on this statement until the first process has completed.