使用Begin Commit Rollback语句和UPDLOCK，HOLDLOCK避免并发 [英] Avoiding Concurrency using Begin Commit Rollback statement and UPDLOCK, HOLDLOCK

问题描述

需要使用以下存储过程的建议，以避免并发问题..

Hi,

Need advice with the below stored procedure to avoid concurrency problems..

Create PROCEDURE [SD_SaveUpdate_DepartmentDetails]                          
@DeptID int,
@DepartmentName varchar(100),
@DepartmentID int out   

AS                            
BEGIN
Begin Try 

-------------------------Insert Update Department Details-----------------------    

Begin  

SET NOCOUNT ON
 
BEGIN TRAN Department
 
IF EXISTS(SELECT * FROM DepartmentTable WITH (UPDLOCK, HOLDLOCK) WHERE DepartmentName= @DepartmentName  and Isdeleted=0)
BEGIN
      UPDATE DepartmentTable SET DepartmentName= @DepartmentName   WHERE DeptID = @DeptID
      set @DepartmentID=@DeptID
END
ELSE
BEGIN
      INSERT INTO DepartmentTable (DepartmentName)  VALUES (@DepartmentName) 
      set @DepartmentID=@@IDentity
END
 
COMMIT Transaction Department
End   
-------------------------Insert Update Department Details-----------------------    
End Try
Begin Catch
set @DepartmentID=0
End Catch
End

我的上述方法是否使用（UPDLOCK，HOLDLOCK）正确。



我想设置@DepartmentID输出参数= -1如果部门没有为USER2保存，因为它是重复的。我需要在上面的SP中添加set @ DepartmentID = -1行。



在catch块中我设置@ DepartmentID = 0如果在保存时出现错误。但是我还想在重复发生时设置@ DepartmentID = -1。



你能帮我解决这个SP以及optimitic（成本效益），这样我就可以在所有存储过程中使用相同的逻辑。



谢谢，



Prathap。

Is my above approach using the (UPDLOCK,HOLDLOCK) correct.

I want to set the @DepartmentID output paramater = "-1" if the department is not saved for the USER2 because its a duplicate.Where do i need to add the set @DepartmentID= -1 line in the above SP.

In the catch block i am setting @DepartmentID=0 if there is some error while saving.But i also want to set @DepartmentID=-1 when duplicate occcurs.

Can you please help me with this SP as well as optimitic(cost effective) so that i can use the same kinda logic in all the stored procedures.

Thanks,

Prathap.

推荐答案

谢谢大家的时间。没有使用锁定概念（SET TRANSACTION ISOLATION LEVEL）或任何UPDLOCK，我尝试了以下SP

Thank you all for the time.Without using the Locking concept(SET TRANSACTION ISOLATION LEVEL) or any UPDLOCK, i tried the below SP

Create PROCEDURE [SD_SaveUpdate_DepartmentDetails]                          
@DeptID int,
@DepartmentName varchar(100),
@DepartmentID int out   


AS                            
BEGIN
Begin Try 
 
-------------------------Insert Update Department Details-----------------------    

Begin  
 
SET NOCOUNT ON
 
BEGIN TRAN Department
 
IF Not EXISTS(SELECT * FROM DepartmentTable  WHERE DepartmentName= @DepartmentName
  and Isdeleted=0 and DeptID <> @DeptID)
and @DeptID > 0 
BEGIN
      UPDATE DepartmentTable SET DepartmentName= @DepartmentName,lastupdated=getdate()   WHERE DeptID = @DeptID
      set @DepartmentID = @DeptID
END
ELSE IF NOT EXISTS(SELECT Top 1 DepartmentName FROM DepartmentTable  WHERE DepartmentName= @DepartmentName and IsDeleted=0)
and @DeptID=0
BEGIN
      INSERT INTO DepartmentTable (DepartmentName,isdeleted,lastupdated)  VALUES (@DepartmentName,0,getdate()) 
       set @DepartmentID=Scope_IDentity()
END
Else
Begin
set @DepartmentID = -1
End
 
COMMIT Transaction Department
End   
-------------------------Insert Update Department Details-----------------------    
End Try
Begin Catch
set @DepartmentID = 0
End Catch
End

为了确认是否有任何重复值，我使用SQLQUERYSTRESS工具测试了这个SP，用于多用户场景（并发）及其成功。没有重复值。



如有任何建议，请通知我。

To confirm if there is any duplicate value,I tested this SP using SQLQUERYSTRESS tool for multiple users scenario (concurrency) and its successful.No duplicate values.

Please let me know in case of any suggestions.

这篇关于使用Begin Commit Rollback语句和UPDLOCK，HOLDLOCK避免并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！