Auto Increment Order Number (several companies in one table)

Question

I have a primary key OrderHeaderID, which automatically increases, but I also need to automatically increase OrderNumber based on which company the order is placed on.

Current strategy: (In my insert statement on the table)

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

Problem: as soon as I started testing some volume, I started getting duplicate key errors:

OrderHeader table:

 OrderHeaderID CompanyID OrderNumber 1 1 10000 2 1 10001 3 1 10002 4 2 10000 5 2 10001 6 2 10002 

Answer 1

To solve the concurrency problem, you need to increase the isolation level of your SELECT , and then perform an INSERT inside the same transaction.

The specific syntax of this parameter will be different for each RBDMS, but here is an example of using Sql Server:

 BEGIN TRANSACTION DECLARE @OrderNumber INT SELECT @OrderNumber = MAX(OrderNumber) + 1 FROM OrderHeader WITH (XLOCK) WHERE CompanyID = @CompanyID INSERT... (@OrderNumber) COMMIT 

This puts an exclusive lock on the rows read during the SELECT , which will prevent another instance of this procedure from starting at the same time. Instead, the second instance will be locked until the original process executes INSERT and COMMIT , after which the second instance will proceed to read and lock the newly created value.

Answer 2

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

For your purposes, you could define your company table as follows:

 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 like this:

 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 configure the “add order” request this way:

 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 conditions (race): this "blocked update" will take care of this. In addition, you did not denormalize the database design (the 1st normal form requires that each row and column intersection be atomic / indecomposable: it contains exactly one value from the applicable domain and nothing else. Suitable identifiers like yours are verboten.)

Easy!

Answer 3

I would use a function to automatically increase the number of OrderNumber for each identifier.

This is what I collected in 2 minutes. He does what you ask.

 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