Avoiding Concurrency using Begin Commit Rollback Return statement and UPDLOCK, HOLDLOCK

Question

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

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.

Answer 1

WHy are you setting the locking hints in the SELECT statement within IF EXISTS? Shouldnt it be on actual UPDATE statements if at all you want to use them?

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 2

Visakh,

Few people recommended the below blog and I followed the same.

http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

"The UPDLOCK hint instructs SQL Server to use an update lock instead of the shared lock that would normally be acquired for the SELECT.  HOLDLOCK is needed in the default READ COMMITTED isolation level to ensure that the lock is held until the end of the transaction.  This more restrictive update lock will prevent simultaneous queries from either selecting or changing the locked resource.  If the row exists during the SELECT, the locked resource is the existing row.  If no row exists with the specified key, a range lock on the primary key is acquired to prevent inserts of the same key until the lock is released. "

Answer 3

Hi,

I dont think any good use of Locking hints here. Also instead of

IF EXISTS(SELECT * FROM DepartmentTable WITH (UPDLOCK, HOLDLOCK) WHERE DeptID = @DeptID and Isdeleted=0)

You can use

IF EXISTS(SELECT 1 FROM DepartmentTable WITH (UPDLOCK, HOLDLOCK) WHERE DeptID = @DeptID and Isdeleted=0)

and remove locking hint assuming proper index is there on DeptID col and Isdeleted col

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles

Answer 4

Hi,

Thanks for the advice. DeptID is the primary key so its already indexed,but there is no index on IsDeleted column.Sorry edited the ques ,we need to check for the department name duplicacy.

If I remove locking how can it help with the concurrency as when 10 users access the SP at the same time and try to insert the same departmentname,there is a chance of duplicacy.

Correct me If I am wrong and I am not confortable using locks,looking for an alternative approach which is simple and yet effective.

Answer 5

Hi,

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 TOP 1 DepartmentName 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

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.

Answer 6

You can have a unique constraint/index on department name to avoid duplicates

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 7

I need to place the unique constraint on Deptname,isdeleted and lastupdated columns.

I have posted an answer from my end and tested it.Can you please suggest improvements on the same.

Answer 8

I see problem here

1. I don't think its a good idea to have the lastupdated column in the unique index.

2. If Isdeleted is part of unique index, then you will be able to add a duplicate department name once, assume you have already one with isdeleted=1 and you are adding one new with Isdeleted=0. You will never be able to delete (mark isdeleted=1) the newer entry,because departmentname,isdeleted combination already exists.

 

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 9

Yes that is  the reason i am not using any unique index.So i have written the below SP and tested using SQLQUERYSTRESS tool and didnt find any duplicates.

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

Answer 10

Okay, do you have any other issues? The SP looks okay to me now

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 11

Only duplicates part was the issue.Now all ok.

Thanks mate.

Prathap.