Sequential numbers in distributed word

Question

We are building order processing system. We have a cluster of processing servers. We need to assign readable numbers to the orders (e.g. ORD-000001, ORD-000002).

Main problem that this is hard for us to implement system wide lock. I am thinking about schemas with lock expiration. But everything comes in mind still have bottlenecks.

We are approaching DDD, so direct access to database is hard. We are using NHibernate. And we use UnitOfWork.

Pls, help with some ideas. Every idea will be valuable. Any links to something to read on topic?

UPDATE: I want to stress, that I need sequential numbers. And so cannot use hi/low algorithms. At this time I am ivestigating scenario when

1. I assign "probably good number";
2. Push it to database;
3. If fail, try to assign another "probably good number";
4. If success, commit;

But I cannot find goot technology for it.

Answer 1

Are you trying to make sure all the orders have unique numbers, but without having a central location that coordinates the number distribution?

Here are two options for you.

1. Let's say you expect to have, at most, ten servers in any realistic period of time. Have each server hand out sequential numbers of the form ORD-XXXXXN. N is the number of the server. So server 0 hands out ORD-000000, ORD-000010, ORD-000020, etc. Server 6 hands out ORD-000006, ORD-000016, ORD-000026, etc.

2. Hand out blocks of numbers, 10000 at a time. Each server uses up all numbers in a block before retrieving more from a little server running in the background somewhere. The little server just runs through all the blocks, one by one, passing them out one at a time. The first block to be handed out is 0-9999, the second is 10000-19999, the third is 20000-29999, etc.

Answer 2

Just in case, if you really use single database, why can't you just

CREATE TABLE OrderNumbers (ID INT IDENTITY(1,1), Dummy VARCHAR(1))
INSERT INTO OrderNumbers (Dummy) SELECT ''
SELECT 'ORDER_N' + CONVERT(VARCHAR(50), @@IDENTITY) AS NewOrderNumber

UPDATE: Even if you have different users (multi tenancy), the PK will work if your sequential number doesn't have to be +1 - i.e. you can have 1, 4, 10, not only 1, 2, 3.

UPDATE: or, as jprete suggested, do something like:

CREATE TABLE CustomerOrderNumber (ID INT, CustomerID INT)
CREATE FUNCTION GetMaxId ( @mycustomerid INT ) RETURNS INT
AS BEGIN
 DECLARE @maxid INT
 SET @maxid = SELECT ID FROM CustomerOrderNumber WHERE CustomerID = @mycustomerid
 SET @maxid = @maxid + 1
 UPDATE CustomerOrderNumber SET ID = @maxid
 RETURN @maxid
END