again dead lock

hi i m getting below error.

why i m getting this error...

Transaction(Process ID 61) was deadlockeds on lock resources with another process and has been
chosen as the deadlock victim.Return the transaction.82

below is procedure

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1

) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

--Added By Gopal For HDFC Bounce cases 24-Feb-07
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='83' and drcr ='D' and amount>0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in('82','83') and rtrim(ltrim(drcr)) ='C' and amount<0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='82' and rtrim(ltrim(drcr)) ='B' and amount>0 and compare_status='Pending'
--Added by Nachiket for failure

Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in ('89','91','86') and amount>0 and compare_status='Pending'

Update Tbl_CMS_UploadDetails Set amount =abs(amount) where format_id in ('85','88','90') and amount<0 and compare_status='Pending'
Declare @.recCount int
select @.recCount=count(*) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set rec_count =@.recCount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id
Declare @.amount decimal
select @.amount=SUM(Amount) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set Amount =@.amount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Deadlocking occurs because 2 processes are competing for the same resources like this

Process 1 is holding Table A & is waiting for Table B to complete.

Process 2 is holding Table B & is waiting for Table A to complete.

Neither will let go off the resource it has. SQL Server detects this dead-locking and will randomly choose one to kill.

DBCC TRACEON (3650, 1205, -1) will write the details of the 2 processes of the deadlocking into the errorlog each times that it occurs but the error log can grow very fast.

An easy way to avoid deadlocking is to ensure the all processes access tables in the same order (e.g. Customer, Accounts, Transactions or maybe in alphabetical order if this works for you)

regards,

Niall.

|||hey thanx for reply...but how to solve my problem?|||

You need to run the traceon to find out what the other process (involved in the deadlocking) is doing. Then you can figure out which objects are used by both procedures/routines and how the order of access to these objects might differ.

Once you have that, it can be fixed.

Niall.