Running the same stored procedure sequentially
Published on: 26th Oct 2018
Updated on: 19th 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
tb_name- this field identifies the record that is meant for which table.last_id- this is the last number that has been assigned to any document.modified_on- it keeps the last modification date/time.
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,
Notes on sp_getapplock
-
The above stored procedures must be running within a database transaction.
-
If @@lock_timeout is -1, then,
sp_getapplockwill be blocked until it has been released and commit/rollback. -
If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully acquires the app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.
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
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
-
It creates a lock name with the following code.
set @lock = 'lock-' + @tb_name -
After that, it will call
sp_getapplockto try locking this resource exclusively. It retries and waits at random time until it acquires a lock. -
Once it has acquired the lock, it generates a new value to be kept in
@last_id. -
Upon completion, it calls
sp_releaseapplockto release this resource. -
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, software developer.