?
Solved

Threadsafe generation of sequence numbers with stored procedures

Posted on 2003-03-24
11
Medium Priority
?
1,103 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:alexwms
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 5

Expert Comment

by:russellshome
ID: 8201577
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'
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 300 total points
ID: 8201736
-- One operation, update locking
insert YourTable(SeqCol)
select right('0000000'+cast(cast(max(SeqCol) as int)+1 as varchar(7)),7)
from YourTable (UPDLOCK)

-- Returns new value, select+update locking
begin tran
 declare @NewVal char(7)
 select @NewVal=right('0000000'+cast(cast(max(SeqCol) as  int)+1 as varchar(7)),7)
 from YourTable (TABLOCKX)
 insert YourTable(SeqCol) values (@NewVal)
 select @NewVal
commit

The creation of SP is very simple now :)
0
 
LVL 12

Expert Comment

by:ill
ID: 8201844
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)

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 13

Expert Comment

by:ispaleny
ID: 8202122
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.
0
 
LVL 12

Expert Comment

by:ill
ID: 8202188
i live with gaps in my identity columns for several years without problems.
should i have some?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8202489
alexwms,
do you need {1,2,3,4} or {1,2,4,5} is satisfactory ?
0
 
LVL 1

Author Comment

by:alexwms
ID: 8203008
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
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8203302
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.
0
 
LVL 1

Author Comment

by:alexwms
ID: 8203351
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
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8203579
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
0
 
LVL 1

Author Comment

by:alexwms
ID: 8208018
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question