Auto Increment Order Number (multiple companies in one table)

Question

I have a primary key OrderHeaderID that auto increments, but i also need to auto increment the OrderNumber based what company the order is placed for.

Current Strategy: (On my insert statement to the table)

(SELECT MAX(OrderNumber) + 1 FROM  OrderHeader WHERE CompanyID = @CompanyID)

Issue: once i started testing with some volume, i started getting duplicate key errors:

Table OrderHeader:

OrderHeaderID CompanyID OrderNumber
1             1         10000
2             1         10001
3             1         10002

4             2         10000
5             2         10001
6             2         10002

what rdbms are you using? sql-server, mysql, oracle? why do you want the order number to be dependent on the companyid? — Taryn, Sep 13 '12 at 20:35
I assume from the variable names he's using SQL Server ... re-tagged. — David Faber, Sep 13 '12 at 21:37

score 3 · Accepted Answer · answered Sep 13 '12 at 20:42

To solve your concurrency issue, you'll have to raise the isolation level of your SELECT statement, and then subsequently perform the INSERT inside of the same transaction.

The specific syntax of this will vary for each RBDMS, but here's an example using Sql Server:

BEGIN TRANSACTION
    DECLARE @OrderNumber INT

    SELECT @OrderNumber = MAX(OrderNumber) + 1 
    FROM OrderHeader WITH (XLOCK)
    WHERE CompanyID = @CompanyID

    INSERT... (@OrderNumber)
COMMIT

This places an exclusive lock on the rows read during the SELECT statement, which will prevent another instance of this routine from running concurrently. Instead, the second instance will be blocked until the original process performs the INSERT and COMMIT, at which point the second instance will then proceed to read and lock the newly generated value.

Thanks, very simple without any schema changes!! – bbrian51 Sep 13 '12 at 21:59 — bbrian51, Sep 13 '12 at 21:59

score 1 · Answer 2 · edited May 23 '17 at 12:27

take a look at my answer here: sql server: generate primary key based on counter and another column value

For your purposes, you could define your company table thus:

create table dbo.company
(
  id   int         not null primary key ,
  name varchar(32) not null unique      ,
  order_counter    not null default(0)  ,
  ...
)

and your order table thus:

create table dbo.order
(
  company_id   int not null foreign key references dbo.company( id ) ,
  id           int not null ,
  order_number as 100000*company_id + id ,
  ...
  constraint order_AK01 unique nonclustere    ( order_number    ) ,
  constraint order_PK01 primary key clustered ( company_id , id ) ,
)

And set up your "add order" query thus:

declare @new_order_number int

update dbo.company
set @new_order_number = dbo.company.order_counter + 1 ,
    order_counter     = dbo.company.order_counter + 1
where dbo.company.id = @some_company_id

insert dbo.order ( company_id , id ) value ( @some_company_id , @new_order_number )

You have no concurrency (race) conditions: the "interlocked update" takes care of that. Further, you haven't denormalized the database design (1st normal form requires that every row and column intersection be atomic/non-decomposable: that it contain exactly one value from the applicable domain and nothing else. Compound identifers like yours are verboten.)

Easy!

score 0 · Answer 3 · answered Sep 13 '12 at 21:56

0

I would use a function to auto-increment the OrderNumber per a given ID.

Here is what I put together in 2 minutes. It does what you are asking.

create table dbo.OrderHeader (
    OrderHeader int identity(1,1) primary key
    ,CompanyID  int
    ,OrderNumber int
    )
go

create function dbo.NextOrderNumber (
    @CompanyID int
    )
returns int
as
begin
    declare @result int;

    select  @result = OrderNumber + 1
    from    OrderHeader
    where   CompanyID = @CompanyID 

    if @result is null
        set @result = 10000

    return  @result;
end
go  

insert into OrderHeader select 1, dbo.NextOrderNumber(1)

insert into OrderHeader select 2, dbo.NextOrderNumber(1)
insert into OrderHeader select 2, dbo.NextOrderNumber(1)

insert into OrderHeader select 1, dbo.NextOrderNumber(1)

select *
from OrderHeader

answered Sep 13 '12 at 21:56

NicVerAZ

409
1
4
10

The problem with this solution is that it has a built-in _race condition_ (http://en.wikipedia.org/wiki/Race_condition#Computing). In between your UDF's computation of an order number and that value getting inserted into the table, another process can (**and will**, at some point) come along, compute the *same* order number and insert it into the table before your process gets to complete its insert. And you'll be left trying to figure out why you get seemingly random duplicate key errors (or worse, two different orders get merged into one). – Nicholas Carey Sep 14 '12 at 17:12
Oh I am very well aware of that and was as well and that's half of the answer in this case. Transaction and the proper isolation level would be the rest of the answer, it is obviously an overlook from me. – NicVerAZ Sep 17 '12 at 15:38

Auto Increment Order Number (multiple companies in one table)

3 Answers3