concurrency issue with SP - 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 2008 Forums

Transact-SQL (2008)

concurrency issue with SP

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-11-03 : 09:01:58

Hello All,

Though Transaction is handled. got the following error

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. UPDATE RS_Identifier SET Modified_By = 4, Modified_On = 'Nov 3 2009 3:02PM' UPDATE RS_Identifier SET Modified_By = 4, Modified_On = 'Nov 3 2009 3:02PM' , IsCompleteQA = 'Y', PageTypeIDQA = 27, PageTypeID = 27, PageOtherQA = '', PageOther = '' WHERE IdentifierID = 55135

for stored procedure

ALTER PROCEDURE [dbo].[spRS_UpdateCategorizationDetails]
-- Add the parameters for the stored procedure here
@IdentifierID INT,
@FilePath VARCHAR(500),
@IsComplete CHAR,
@PageTypeID INT,
@PageOther VARCHAR(100),
@ModifiedBy INT,
@RoleID INT -- Used to differentiate the Indentifier and IdentifierQA

AS

BEGIN
DECLARE @ErrorMsgID INT,
@ErrorMsg VARCHAR(200),
@query VARCHAR(500),
@where VARCHAR(500),
@RecordID INT -- Used to execute the Stored Procedure: spRS_UpadeteRecordStageStatus
--Select @query = 'UPDATE RS_Identifier SET FilePath='''+ @FilePath +''', Modified_By = ' + CONVERT(varchar, @ModifiedBy) + ', Modified_On = ''' + CONVERT(varchar, GETDATE()) + '''' + char(13)
--select @where= ' WHERE IdentifierID = ' + CONVERT(varchar, @IdentifierID) + char(13)

Select @query = 'UPDATE RS_Identifier SET Modified_By = ' + CONVERT(varchar, @ModifiedBy) + ', Modified_On = ''' + CONVERT(varchar, GETDATE()) + '''' + char(13)
select @where= ' WHERE IdentifierID = ' + CONVERT(varchar, @IdentifierID) + char(13)
PRINT(@query)

BEGIN
IF(@RoleID) = 1
BEGIN
if @IsComplete IS NULL
SELECT @query = @query + ', IsComplete = NULL'
ELSE
SELECT @query = @query + ', IsComplete = ''' + CONVERT(varchar, @IsComplete) + ''''

IF @PageTypeID IS NULL
SELECT @query = @query + ', PageTypeID = NULL'
else
SELECT @query = @query + ', PageTypeID = ' + CONVERT(varchar, @PageTypeID)

IF @PageOther IS NULL
SELECT @query = @query + ', PageOther = NULL'
ELSE
SELECT @query = @query + ', PageOther = ''' + CONVERT(varchar, @PageOther) + ''''
END
IF @RoleID = 4
BEGIN
If @IsComplete IS NULL
SELECT @query = @query + ', IsCompleteQA = NULL'
ELSE
SELECT @query = @query + ', IsCompleteQA = ''' + CONVERT(varchar, @IsComplete) + ''''

IF @PageTypeID IS NULL
SELECT @query = @query + ', PageTypeIDQA = NULL'
else
SELECT @query = @query + ', PageTypeIDQA = ' + CONVERT(varchar, @PageTypeID)
SELECT @query = @query + ', PageTypeID = ' + CONVERT(varchar, @PageTypeID)

IF @PageOther IS NULL
SELECT @query = @query + ', PageOtherQA = NULL'
ELSE
SELECT @query = @query + ', PageOtherQA = ''' + CONVERT(varchar, @PageOther) + ''''
SELECT @query = @query + ', PageOther = ''' + CONVERT(varchar, @PageOther) + ''''
END

SELECT @query = @query + @where
END

BEGIN TRANSACTION TRNS
PRINT(@query)
EXECUTE(@query)

/**************Error Handling*************/

SET @ErrorMsgID=@@Error

IF @ErrorMsgID <> 0
BEGIN
SELECT @ErrorMsg = 'Error occurred IN Stored PROCEDURE ' + OBJECT_NAME(@@PROCID)
ROLLBACK TRANSACTION
RAISERROR(@ErrorMsg,16/*severity*/,1/*state*/)
RETURN (-1)
END

--Get the RecordID corresponding to IdentifierID
Select @RecordID= RecordID from RS_Identifier where IdentifierID = @IdentifierID

IF @RoleID = 1 -- Identifier
Exec spRS_UpadeteRecordStageStatus @RecordID, 44
ELSE
Exec spRS_UpadeteRecordStageStatus @RecordID, 45

COMMIT TRANSACTION
RETURN (0)
END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-11-03 : 10:26:48

Using a transaction is no guarantee that you will not get a deadlock, this is more to do with the ISOLATION LEVEL you choose. Transactions will just handle errors like this when it comes across them and rollback your data.

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-11-04 : 01:16:33

quote:
Originally posted by RickD

Using a transaction is no guarantee that you will not get a deadlock, this is more to do with the ISOLATION LEVEL you choose. Transactions will just handle errors like this when it comes across them and rollback your data.

Please explain how to set isolation level with above example

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources