deadlock

why i m getting this error...

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

i think this is usually on data contention, try to check your sp if it updates another table e.g. manually increments a value of a record number

can you post your sp?
|||

Because another process (user) is accessing the same resource (data) and has a lock on the table.

Refer to Books OnLine, Topics:

Deadlocking Detecting and Ending Deadlocks|||i think this is usually a contention problem. does your sp updates another table during execution? e.g. manually creating/incrementing a record number?

can you post your sp?
|||

Because some two processes are trying to access the same resource and sql server has failed one of them. What you can do is run SQLDiag to catch the processes so you can fix it. Here is an article I wrote on how to do this: http://articles.techrepublic.com.com/5100-9592_11-6116287.html Hope this helps.

Tim

|||

hi below is my procedure where i m getting deadlock issues..

ALTER proc Usp_CAMSUpdateSchemeGroup
(
@.Cams_Upload_Master_Id numeric = null,
@.Maker numeric =null
)
as
declare @.scheme_camscode varchar(50),@.scheme_Group varchar(50)

declare UpdateGroup_Cursor cursor for
select scheme_camscode,scheme_group from Tbl_SchemeMst

open UpdateGroup_Cursor

fetch next from UpdateGroup_Cursor
into @.scheme_camscode,@.scheme_Group

while @.@.fetch_status = 0
begin
update tbl_cams_uploaddetails set
cams_schemegroup =@.scheme_Group Where
scheme_Code=@.scheme_camscode

fetch next from UpdateGroup_Cursor
into @.scheme_camscode,@.scheme_Group

end
close UpdateGroup_Cursor
deallocate UpdateGroup_Cursor

-Setting status pending and maker for fresh entry
update tbl_cams_uploaddetails set
Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending'
Where
CAMS_Upload_Master_ID=@.Cams_Upload_Master_Id

|||You really want to consider not using a cursor for this situation. It looks like your query could be rewritten like this:

UPDATE dd
SET schemegroup = mst.scheme_canscode
FROM
tbl_cams_uploaddetails dd
join tbl_schememst mst on dd.schemegroup = scheme_group and dd.scheme_code = mst.scheme_camscode|||

The only concern that I have about this process is wondering why it is necessary to UPDATE the Scheme_Code in tbl_Cams_UploadDetails for ALL rows in the table. That causes a 'table lock' and is contributing to the deadlock issue.

Isn't it more likely that there are a small number of rows in tbl_Cams_UploadDetails that need to be updated -perhaps the same row(s) that are being updated using the input variable @.Cams_Upload_Master_Id?

Maybe both UPDATE statements can be done together...

As Tim indicated, you can completely replace the CURSOR. Here is a 'revised' suggestion:

Code Snippet

ALTER PROCEDURE Usp_CAMSUpdateSchemeGroup
( @.Cams_Upload_Master_Id numeric = null,
@.Maker numeric = null
)
AS
BEGIN
UPDATE d
SET d.Cams_SchemeGroup = m.Scheme_Group
FROM tbl_Cams_UploadDetails d
JOIN tbl_SchemeMst m
ON d.Scheme_Code = m.Scheme_CamsCode

-Setting status pending and maker for fresh entry
UPDATE tbl_Cams_UploadDetails
SET
Maker = @.Maker,
Make_Date = getdate(),
AuthStatus = 2,
Optype = 0,
Compare_Status = 'Pending'
WHERE CAMS_Upload_Master_ID = @.Cams_Upload_Master_Id
END
GO