CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012database-locking
Mahmoud Saleh
Mahmoud Saleh

Reputation: 33605

Prevent concurrent access to stored procedure in sql server

i have sequences table that consists of three columns:

Number,Year,Type

and for each new year a three new records gets created and updated all along this year.

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:

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

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 ?

Upvotes: 1

Views: 1650

Answers (2)

Mahmoud Saleh
Mahmoud Saleh

Reputation: 33605

I was able to optimize the sequence generation this way:

ALTER PROCEDURE [dbo].[GETSEQUENECENO]
@p_hijricYear                             INT ,
@p_typeId                                 INT ,
@return_val                               INT OUTPUT 
AS 
BEGIN
DECLARE @newSequence                              numeric(18,0) 





BEGIN TRY
UPDATE  IO_SEQUENCENUMBERS WITH (READCOMMITTEDLOCK)
SET     @newSequence = correspondencenumber = correspondencenumber + 1
WHERE
hijricyear  = @p_hijricyear
AND
typeid  = @p_typeid
END TRY

BEGIN CATCH
SELECT @newSequence  = -1 
END CATCH

SELECT @return_val = @newSequence

END

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5508

One approach could be the use of SQL Server application locks, see sp_getapplock and sp_releaseapplock. This will let you serialise your sequence generation through the SP without the need for serialisable transactions but won't prevent access to the io_sequecenumbers table by other code so you'll need to be sure that this SP is the only place that updates this table.

Upvotes: 1

Related Questions