Issue with Locks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Issue with Locks

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-16 : 10:34:47

Hi Guys,

I've an issue with a procedure which i tried to update.Actually this procedure is invoked by a SQL job which runs every minute.
While trying to update the procedure i encountered this error:

Transaction (Process ID 102) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

There is a delete statement in the proc with rowlock option.

Can anyone suggest something on this?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-16 : 10:42:11

Are there any triggers on the table you are deleting data from? Any triggers that insert / delete from other tables?

EDIT: Also what are the indexes on the table? DO you have a proper index that suits the condition with which you delete?

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-16 : 11:09:11

No triggers dear..

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-16 : 11:17:41

In the procedure i am deleting record based on primary key defined..

Please refer to the code below...

ALTER PROCEDURE [dbo].[RunJobs]
AS

BEGIN
SET NoCount ON

SET ANSI_Nulls OFF

DECLARE @CompanyUid INT
, @JobId TINYINT
, @JobUid INT
, @AssetUid INT
, @ActionUid INT
, @ErrorStatus VARCHAR(50)
, @GroupName VARCHAR(15)
, @ModuleMsn INT
, @UserId VARCHAR(50)
, @StartTime DATETIME
, @Buffer VARCHAR(100)
, @Record_count INT
, @MaxSeconds2run INT
, @BatchSize INT
, @lastUpdatedRec INT
, @nextUpdatedRec INT
, @MinJobuid INT
, @currentJobuid INT

SELECT @StartTime = Getdate()
, @buffer = Convert(VARCHAR,@StartTime,21)

EXEC Ai_set_parameter -- setting start time of the job
'RunJobs' ,
'StartTime' ,
@buffer

EXEC Ai_set_parameter
'RunJobs' ,
'Count' ,
''

EXEC Ai_set_parameter
'RunJobs' ,
'ENDTime' ,
''

SELECT @MaxSeconds2run = Isnull(Ai_get_parameter('RunJobs','MaxSeconds2run'),
600)
, @BatchSize = Isnull(Ai_get_parameter('RunJobs','BatchSize'),
100)

SELECT @MinJobuid = Min(jobsuid)
FROM jobs -- To find the least jobsuid value

SET @lastUpdatedRec = Isnull(Ai_get_parameter('RunJobs','LastUpdateRec'),
@MinJobuid)

SELECT @currentJobuid = Max(jobsuid)
FROM jobs

IF (@currentJobuid - @lastUpdatedRec) > = 100 -- Checking if there are 100 records to be processed
BEGIN
SET @nextUpdatedRec = @lastUpdatedRec + 100 -- Fixing the number of rows to be processed
END
ELSE
BEGIN
SET @nextUpdatedRec = Isnull(@currentJobuid,@lastUpdatedRec)
END

EXEC Ai_set_parameter -- Sets the record uptil which the records will be processed
'RunJobs' ,
'LastUpdateRec' ,
@nextUpdatedRec

SET @record_count = 0

DECLARE @RunJobsData CURSOR

SET @RunJobsData = CURSOR FOR SELECT JobsUid
, JobId
, AssetUid
, ActionUid
, GroupName
, ModuleMsn
, UserId
FROM dbo.Jobs WITH (NoLock)
WHERE JobsUid BETWEEN @lastUpdatedRec AND @nextUpdatedRec -- Allows multiple instances of the job
ORDER BY JobsUid

OPEN @RunJobsData

FETCH NEXT FROM @RunJobsData
INTO @JobUid,
@JobId,
@AssetUid,
@ActionUid,
@GroupName,
@ModuleMsn,
@UserId

SELECT @buffer = Convert(VARCHAR,Getdate(),21)

EXEC Ai_set_parameter
'Runjobs' ,
'FirstRecord' ,
@buffer

WHILE @@FETCH_STATUS = 0
AND Datediff(ss,@StartTime,Getdate()) < @MaxSeconds2run
AND @record_count < @BatchSize
BEGIN
SET @record_count = @record_count + 1

IF @JobId = 1
Exec test_proc
END

IF @@ERROR = 0
BEGIN
DELETE Jobs WITH (NOLOCK)
WHERE JobsUid = @JobUid
END

FETCH NEXT FROM @RunJobsData
INTO @JobUid,
@JobId,
@AssetUid,
@ActionUid,
@GroupName,
@ModuleMsn,
@UserId
END

CLOSE @RunJobsData

DEALLOCATE @RunJobsData

SET @buffer = Convert(VARCHAR,Getdate(),21)

EXEC Ai_set_parameter -- setting end time of the Job
'RunJobs' ,
'EndTime' ,
@buffer

SET @buffer = Convert(VARCHAR,@record_count)

EXEC Ai_set_parameter -- setting rows processed by the job
'RunJobs' ,
'Count' ,
@Record_count

RETURN
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources