Avoiding Concurrency using Begin Commit Rollback statement and UPDLOCK, HOLDLOCK

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

SQL-Server

, +

Hi,

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

SQL

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.

Posted 5-Jul-14 2:37am

Prathap Gangireddy

Updated 6-Jul-14 0:13am

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prathap Gangireddy · Answer 1 · 2014-07-05T20:42:00

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

SQL

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

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.