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

  • 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.



    Saturday, July 5, 2014 11:45 AM

Answers

  • 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.

    Sunday, July 6, 2014 6:38 AM

All replies

  • 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

    Saturday, July 5, 2014 12:37 PM
  • 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. "

    Saturday, July 5, 2014 1:01 PM
  • 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

    Saturday, July 5, 2014 3:51 PM
    Moderator
  • 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.



    Sunday, July 6, 2014 4:16 AM
  • 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.

    Sunday, July 6, 2014 6:38 AM
  • You can have a unique constraint/index on department name to avoid duplicates

    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, July 7, 2014 4:32 AM
  • 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.

    Monday, July 7, 2014 6:32 AM
  • 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



    Monday, July 7, 2014 6:39 AM
  • 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

    Monday, July 7, 2014 9:21 AM
  • Okay, do you have any other issues? The SP looks okay to me now

    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, July 7, 2014 9:28 AM
  • Only duplicates part was the issue.Now all ok.

    Thanks mate.

    Prathap.

    Monday, July 7, 2014 9:41 AM