Store proc DeadLock

[deejayAr]

I have store proc giving me deadlock issue
looks like I need to work with lock hinds
if some on can look at and help me to solve the problem


ALTER PROCEDURE [dbo].[sp_CNVRT_GetNextNewJob]
@nJob_ID AS BIGINT OUTPUT,
@sSrcFileSpec AS NVARCHAR(260) OUTPUT,
@sDestFileSpec AS NVARCHAR(260) OUTPUT,
@nJob_OutputFileType AS SMALLINT OUTPUT,
@sJob_OutputFileType_Description AS VARCHAR(50) OUTPUT,
@nJob_ConversionSettings as INT OUTPUT


AS

DECLARE @nCount AS INT
DECLARE @sUID AS UNIQUEIDENTIFIER
DECLARE @nExpireMinutes AS INT

SET @nCount = 0
SET @sUID = newid()
SET @nExpireMinutes = 10

SET @nJob_ID = -1
SET @sSrcFileSpec = ''
SET @sDestFileSpec = ''
SET @nJob_OutputFileType = 0
SET @sJob_OutputFileType_Description = ''

BEGIN
-- Reset any jobs that were marked for pickup by the conversion service but have not made any progress in a predetermined period (@nExpireMinutes)
UPDATE tblConversionJobs
SET Job_StatusCode = 0,
Job_StatusMsg = 'New',
Job_UID = NULL,
Job_ProcessingDateTime = Job_StatusUpdate_DateTime
WHERE Job_Finished=0 AND Job_StatusCode = 1 AND DATEDIFF(mi,Job_ProcessingDateTime, GETDATE()) > @nExpireMinutes


UPDATE tblConversionJobs
SET Job_StatusCode = 1,
Job_StatusMsg = 'Ready to process',
Job_UID = @sUID,
Job_ProcessingDateTime = GETDATE()
WHERE Job_Finished=0 AND Job_StatusCode = 0 AND
Job_ID IN (select TOP 1 Job_ID FROM tblConversionJobs WHERE Job_StatusCode = 0 AND ISNULL(Job_UID,@sUID) = @sUID ORDER BY Job_ID)

SET @nCount = @@ROWCOUNT

IF @nCount > 0
BEGIN
SELECT TOP 1 @nJob_ID = Job_ID,
@sSrcFileSpec = Job_InputFilespec,
@sDestFileSpec = Job_OutputFilespec,
@nJob_OutputFileType = Job_OutputFileType,
@sJob_OutputFileType_Description = FileType_Description,
@nJob_ConversionSettings = Job_ConversionSettingID
FROM tblConversionJobs INNER JOIN tblFileTypes
ON tblConversionJobs.Job_OutputFileType = tblFileTypes.FileType_ID
WHERE Job_StatusCode = 1 AND Job_UID = @sUID
ORDER BY Job_ID

--Free up any jobs that we did not take
UPDATE tblConversionJobs
SET Job_StatusCode = 0,
Job_StatusMsg = 'New',
Job_UID = NULL,
Job_ProcessingDateTime = Job_StatusUpdate_DateTime
WHERE Job_StatusCode = 1 AND Job_UID = @sUID AND Job_ID <> @nJob_ID
END
END

 

[gmmastros]

In my opinion, the best way to fix a deadlock problem is to fix the performance problems of the query. To the end, can you please post the output of this query.

sp_helpindex 'tblConversionJobs'

This will show the indexes that exist on your table. I'm thinking that there may be an index that could be added to the table to fix this problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[deejayAr]

I index Job_UID and it is working so far a while and then I through error again