title: Prevent concurrent access to stored procedure in sql server
body: <p>i have sequences table that consists of three columns:</p>
  
  <p><strong>Number,Year,Type</strong></p>
  
  <p>and for each new year a three new records gets created and updated all along this year.</p>
  
  <p>my stored procedure for generating the sequence is used inside other stored procedures, and my issue is that i want to block concurrent access to this stored procedure and make the access as queue so if concurrent access occur one has to wait for another to finish so that two users don't get same sequence number, the code is as follows:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[GETSEQUENECENO]
  @p_hijricYear                             INT ,
  @p_typeId                                 INT ,
  @return_val                               INT OUTPUT 
  AS 
  BEGIN
  DECLARE @newSequence                              INT 
  
  BEGIN TRY
  SELECT @return_val  = 0
  SELECT @newSequence  =  ISNULL( max(correspondencenumber) ,0 )
  FROM  io_sequencenumbers with (XLOCK)  
  WHERE
  hijricyear  = @p_hijricyear
  AND
  typeid  = @p_typeid
  END TRY
  
  BEGIN CATCH
  SELECT @newSequence  = -1 
  END CATCH
  
  IF @newSequence != -1
  BEGIN
  
  
  IF @newSequence = 0 
  BEGIN 
  SELECT @newSequence  = 1 
  INSERT INTO  io_sequencenumbers    
  VALUES 
  ( @newSequence , 
  @p_hijricYear , 
  @p_typeId )  
  END
  
  ELSE
  BEGIN 
  SELECT @newSequence  = @newSequence + 1 
  UPDATE  io_sequencenumbers   
  SET
  correspondencenumber = @newSequence 
  WHERE  hijricyear  = @p_hijricyear
  AND
  typeid  = @p_typeid 
  END
  
  END -- end of @newSequence!= -1 --
  
  SELECT @return_val = @newSequence
  END
  </code></pre>
  
  <p>i read that setting isolation level to serializable may solve it, is that enough or i have to use also begin and end transaction in stored procedure and manually handling rollback and commit ?</p>