Welcome to ciysys blog

Running the same stored procedure sequentially

Published on: 26th Oct 2018

Updated on: 20th May 2021

Overview

MSSQL allows concurrent access the data which is a great benefit in a multi-user environment. As a result, the process of maintaining a serial number for documents has become challenging. For example, the system is assigning a unique serial number for bill and purchase order.

Our last ID table

We keep track all last number in the following table.

create table tb_last_id (
    tb_name nvarchar(255) not null
    , last_id bigint not null
    , modified_on datetime not null
);

Where

So, the question is how to avoid two users from getting the same number?

The system stored procedure that works like a mutex

Since MSSQL allows concurrent access to the data, we must find a way make the access become sequentially or synchronously. To make the process runs synchronously, you need to call the following system stored procedure which works like a mutex in C#. It blocks the access to the given resource until it has released it.

sp_getapplock

Upon completing the process, you must call the following system stored procedure so that the next process allows it to access the resource.

sp_releaseapplock

For more details about the sp_getapplock, please refers to the following page,

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15

Notes on sp_getapplock

For more details about lock timeout, please refers to the following pages,

https://docs.microsoft.com/en-us/sql/t-sql/functions/lock-timeout-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql?view=sql-server-ver15

The stored procedure that generate new unique serial number

This is the stored procedure that we have implemented that will guarantee assigning a new unique serial number for the document.

create proc pr_sys_gen_new_id (
    @tb_name nvarchar(255)
    , @last_id bigint output     
    , @is_debug int = 0
)
as
begin    
    declare  
        @init_local_trans int
        , @lock nvarchar(50)
        , @lock_h int
        , @wait_ms nvarchar(50)

    set nocount on    
    if @is_debug = 1 print 'pr_sys_gen_new_id - start'

    if @@trancount = 0
    begin
        if @is_debug = 1 print 'pr_sys_gen_new_id - begin tran'   
        set @init_local_trans = 1
        begin tran
    end

    --<<====
    --this guaranteed only 1 process is reading the last_id value.
    set @lock = 'lock-' + @tb_name

    set @lock_h = -99
    while @lock_h < 0
    begin

RETRY_LOCK:    
        --try to acq the lock and wait for @@lock_timeout
        exec @lock_h = sp_getapplock  
                            @Resource = @lock
                            , @LockMode = 'Exclusive'
                            , @LockOwner = 'Transaction'

        if @is_debug = 1 print 'pr_sys_gen_new_id - acq lock..' + cast(@lock_h as nvarchar)

        -- exit the loop if manage to acquire the lock for the record        
        if @lock_h >= 0
        begin
            if @is_debug = 1 print 'pr_sys_gen_new_id - got the lock now..' + isnull(cast(error_number() as nvarchar), '?')    
            break
        end

        -- random wait time.
        set @wait_ms = '00:00:00.' + cast(cast(rand()*100 as int) as nvarchar)

        if @is_debug = 1 print 'pr_sys_gen_new_id - waiting..' + @wait_ms

        --wait for the lock
        waitfor delay @wait_ms    
    end
    --<<====      
        
    -- -------------------------------
    begin try    
        if not exists(
            select *
            from tb_last_id
            where
                tb_name = @tb_name
        )
        begin    
            -- if the record does not exist, append a new record.
            insert into tb_last_id (tb_name, modified_on, last_id)
            values (@tb_name, getdate(), '1')

            set @last_id = 1    
        end
        else
        begin
            -- if the record already exists, update it.
            update tb_last_id
            set  
                last_id = cast(cast(last_id as nvarchar) as bigint) + 1,
                modified_on = getdate()
            where
                tb_name = @tb_name

            select  
                @last_id = cast(last_id as bigint)  
            from tb_last_id
            where
                tb_name = @tb_name    
        end

        -- -------------------------------
        --release it immediately after used
        if @lock_h >= 0
        begin
            exec sp_releaseapplock  
                    @Resource = @lock
                    , @LockOwner = 'Transaction'
        end
        
        if @is_debug = 1 print 'pr_sys_gen_new_id - process done'

        -- -------------------------------
        if @init_local_trans = 1
        begin
            commit    
            if @is_debug = 1 print 'pr_sys_gen_new_id - commit'
        end    
    end try
    begin catch
        -- -------------------------------
        --this err will be raised if the @@lock_timeout is other than -1.
        if error_number() = 1222--Lock request time out period exceeded.
        begin
            if @is_debug = 1  print 'ERROR=>' + error_message()
            goto RETRY_LOCK
        end

        if @init_local_trans = 1
        begin
            rollback
            if @is_debug = 1 print 'pr_sys_gen_new_id - rollback'
        end

        if @is_debug = 1  
        begin
            print 'ERR # => ' + isnull(cast(error_number() as nvarchar), '?')
            print 'ERROR=>' + error_message()            
        end    
    end catch
    -- -------------------------------
    set nocount off     
end

How does it work

  1. It creates a lock name with the following code.

    set @lock = 'lock-' + @tb_name

  2. After that, it will call sp_getapplock to try locking this resource exclusively. It retries and waits at random time until it acquires a lock.

  3. Once it has acquired the lock, it generates a new value to be kept in @last_id.

  4. Upon completion, it calls sp_releaseapplock to release this resource.

  5. Finally, returning the new value through the OUTPUT parameter.

What is the reason to use sp_getapplock

The main reason is that if not exists(..) cannot avoid or block the concurrent reading to the tb_last_id table. There is a chance that the table was accessed concurrently and the processes issue INSERT INTO that will hit the UNIQUE constraint on the tb_last_id.tb_name.

...
if not exists(
    select *
    from tb_last_id
    where
        tb_name = @tb_name
)
begin
    insert into tb_last_id (tb_name, modified_on, last_id)
    values (@tb_name, getdate(), '1')
    set @last_id = 1
end
...

To overcome this issue, we have to manually INSERT the record before we can use that serial number safely and this is troublesome. We might forget and cause an operation issue.

How to use this stored procedure

To generate new new bill number,

begin trans
...
declare @id bigint
 exec pr_sys_gen_new_id
     'tb_sales',
     @id output
 select @id

... (save the bill)...
commit

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.