Link to home
Start Free TrialLog in
Avatar of alexwms
alexwms

asked on

Threadsafe generation of sequence numbers with stored procedures

Hello,
I need to generate a sequence number for new entries in a table of Work Orders.
Each additional entry should get a unique sequence number.
The Sequence number should be a string with exactly seven characters.
Example: 0000001, 0000002, ... , 0001034, ...
The pocedure must be threadsafe,
so if multiple threads are calling the sql server at once it should still work reliably.
I would like to call the stored procedure from within vba.
It should work with MSSQL Server.
And it should be reasonably fast for large tables with about 100.000 entries.
It would be nice if the procedure could return the newly generated sequence number.
I think that's all.
Thanks for suggestions,
Alexander
Avatar of russellshome
russellshome

You can use an id column to generate a number, a function to convert that to a 7 character field and finally a computed column to hold the result.

CREATE FUNCTION dbo.prepend7zeroes (
     @id int
)
RETURNS char(7) AS  
BEGIN
     declare @result char(7)
     set @result = convert(char, @id)
     while len(@result) < 7
          set @result = '0' + @result
     return @result
END
go

CREATE TABLE workorders (
     id int IDENTITY (1, 1) NOT NULL
     , seqno as dbo.prepend7zeroes(id)
     , field1 varchar(50)
)
go

create procedure workorders_insert
     @field1 varchar(50)
as
insert into
     workorders (
          field1
     ) values (
          @field1
     )
select
     *
from
     workorders
where
     id = scope_identity()
go

-- Testing
exec workorders_insert 'test'
ASKER CERTIFIED SOLUTION
Avatar of ispaleny
ispaleny
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
can you use mssql build in threadsafe ?


CREATE TABLE test (
intId int IDENTITY (1, 1) NOT NULL ,
CharId AS (right(('0000000'+ convert(varchar(7),intId)),7)),
value char (10)  NULL
)


insert into test ( value) values ( 'first')

select right(('0000000'+ convert(varchar(7),IDENT_CURRENT('test'))),7)

Identity is a great feature, but cannot be used for generating numbers in sequence.
If you insert new record and an error occurs (constraint, timeout, deadlock,...), a transaction is rolled back, but an identity number is used !!! It will leave gaps.
i live with gaps in my identity columns for several years without problems.
should i have some?
alexwms,
do you need {1,2,3,4} or {1,2,4,5} is satisfactory ?
Avatar of alexwms

ASKER

Hello together,
thanks for the suggestions, identity was also my first thought but I prefered the style with a fixed number of characters.
So using identity will always leave me with this extra column and that just doesn't feel right. But maybe it's the way to go.

I like ispaleny's suggestion because it avoids this second column.

I'm not sure about the speed implications of using max() here. I guess the use of identity as a build in function is faster. Also it has the advantage of being nonblocking.

I'm also unsure about the use of two seperate statements inside of the commit statement. Will any looks of single statements inside a commit be global. Otherwise this does look dangerous.

About the gaps. Even ispaleny's suggestion will leave gaps if a thread requests a sequence number while another thread is running and rolled back later, right?
Fortunately it's not necessary to enforce a continous numeration in my case.

Is it possible to splitt points or award points directly?

Thanks for the ideas so far,
Alexander
To "Even ispaleny's suggestion will leave gaps..."
-----------------------------------------------------------
I must say no, used locks will prevent other users to insert records above currently being inserted ones.

To "I'm not sure about the speed implications of using max() here"
-----------------------------------------------------------
You can use "select top 1 SeqCol ... order by SeqCol DESC", but SeqCol is a primary key. The maximal value is taken from statistics.
Avatar of alexwms

ASKER

Hmmm.
So instead of gaps any request of a new batch number will fail while any transaction which uses a new batch number will take place?
Some of the transactions tied to a batch number take fairly long.
Or will it be like that that the transaction might fail but I will be left with an orphan seq. number?

begin transaction
@BatNbr= sp_getSeqNbr
call sp_other(@BatNbr)
commit transaction

Or should I handle this differently?
Regards,
Alexander
In MSSQL you can name nested transactions, but it is usable for named rolling back. If you need to use a high level lock in transaction, give it in the end to be as short as possible.

--Table is locked for all 10 s
begin tran  MegaOp
begin tran  MiniOp
declare @Max char(10)
select @Max=max(SeqCol) from YourTable (TABLOCKX)
WAITFOR DELAY '00:00:05'
commit tran MiniOp
WAITFOR DELAY '00:00:05'
commit tran MegaOp
Avatar of alexwms

ASKER

From what I understand the locks enable You to ensure a continous numeration. Of course the price is a serialization of all transactions that would require a seq. number.
There is always the possibility to seperate the creation of seq. numbers from the transaction. This would then lead to orphan seq. numbers in case of rolled back transactions. These orphan seq. numbers could be deleted later leaving the gaps in the numbering if there was another transaction in the meantime.

In my case I will just allow orphan seq. numbers and seperate the creation of seq. numbers from the transaction itself to minimize locking of the seq. number table.

Thanks for the insightfull answers,
Alexander