How to best generate a unique user number without duplicates in a multi-tenant database

Question

Background:

This is a multi-tenant application, so that a normal identity column will not work. All tables have a unique client identifier Clients.id. So each client can have many customers. This column is not included below for simplicity.

We want to generate a unique customer number starting at 1000.

We store the current (last) generated number in a table called Master. Let's say Master.CustomerNumber. So numbers will go 1001, 1002 etc. and the last one is stored there.

So each time we add a customer, we have a query that looks up the current value, increment it, and insert it in Customer.Number.

NOTE: we are using SQL Server 2008. We have multiple servers in a cluster.

What is the best approach to insure that if two customers are added at the same time that each gets a unique customer number? Stored procedure, locking, CFLOCKING?

How do I insure that this process is 'single-threaded' and that the same number is not issued twice?

I do have a unique index on Customer.Number+Clients.id. I am interested in the implementation of how to guarantee uniqueness when generated.

Answer 1

You could use following solution:

CREATE TABLE dbo.[Master] (
    -- Foreign key to dbo.Tenant table ?
    -- Only one row for every tenant is allowed => PK on tenant identifier
    TenantNum   INT NOT NULL
        CONSTRAINT PK_Master PRIMARY KEY CLUSTERED (TenantNum),
    -- LastCustomerNum = last generated value for CustomerNum
    -- NULL means no value was generated
    LastCustomerNum INT NULL, 
    -- It will create one clustered unique index on these two columns
    InitialValue INT NOT NULL
        CONSTRAINT DF_Master_InitialValue DEFAULT (1),
    Step        INT NOT NULL
        CONSTRAINT DF_Master_Step DEFAULT (1)
);
GO

CREATE PROCEDURE dbo.p_GetNewCustomerNum
@TenantNum          INT,
@NewCustomerNum     INT OUTPUT,
@HowManyCustomerNum INT = 1 -- Ussualy, we want to generate only one CustomerNum 
AS
BEGIN
    BEGIN TRY
        IF @TenantNum IS NULL
            RAISERROR('Invalid value for @TenantNum: %d', 16, 1, @TenantNum);
        IF @HowManyCustomerNum IS NULL OR @HowManyCustomerNum < 1
            RAISERROR('Invalid value for @HowManyCustomerNum: %d', 16, 1, @HowManyCustomerNum)

        -- It updated the LastCustomerNum column and it assign the new value to @NewCustomerNum output parameter
        UPDATE  m
        SET     @NewCustomerNum 
                    = LastCustomerNum 
                    = CASE WHEN LastCustomerNum IS NULL THEN InitialValue - Step ELSE LastCustomerNum END 
                        + Step * @HowManyCustomerNum
        FROM    dbo.[Master] AS m
        WHERE   m.TenantNum = @TenantNum

        IF @@ROWCOUNT = 0
            RAISERROR('@TenantNum: %d doesn''t exist', 16, 1, @TenantNum);
    END TRY
    BEGIN CATCH
        -- ReThrow intercepted exception/error
        DECLARE @ExMessage NVARCHAR(2048) = ERROR_MESSAGE()
        RAISERROR(@ExMessage, 16, 1)
        -- Use THROW for SQL2012+
    END CATCH
END
GO

Usage (no gaps):

BEGIN TRAN
...
DECLARE @cn INT
EXEC dbo.p_GetNewCustomerNum
    @TenantNum          = ...,
    @NewCustomerNum     = @cn OUTPUT,
    [@HowManyCustomerNum = ...]
...
INSERT INTO dbo.Customer(..., CustomerNum, ...)
VALUES (..., @cs, ...)
COMMIT

Note: if you don't use transactions to generate the new customer number and then to insert this values into Customer table then they could get gaps.

How it works ?

1. {Primary key | Unique index} defined on TenantNum and CustomerNum will prevent any duplicates
2. Under default isolation level (READ COMMITTED) but also under READ UNCOMMITTED, REPETABLE READ and SERIALIZABLE, the UPDATE statement require and X lock. If we have two concurent SQL Server sessions (and transactions) which try to generate a new CustomerNum then first session will successfully get the X lock on tenant row and the second session will have to wait till first session (and transaction) will end (COMMIT or ROLLBACK). Note: I assumed that every session has one active transaction.
3. Regarding X lock behavior: this is possible because two [concurent] X locks are incompatibles. See table bellow with "Requested mode" and [Granted mode]:
4. For above reasons, only one connection/TX can update within dbo.[Master] a tenant row with a new customer number.

-- Tests #1

-- It insert few new and "old" tenants
INSERT  dbo.[Master] (TenantNum) VALUES (101)
INSERT  dbo.[Master] (TenantNum, LastCustomerNum) VALUES (102, 1111)

SELECT * FROM dbo.[Master]
/*
TenantNum   LastCustomerNum InitialValue Step
----------- --------------- ------------ -----------
101         NULL            1            1
102         1111            1            1
*/
GO


-- It generate one CustomerNum for tenant 101
DECLARE @cn INT
EXEC p_GetNewCustomerNum 101, @cn OUTPUT 
SELECT @cn AS [cn]
/*
cn
-----------
1
*/
GO
-- It generate second CustomerNums for tenant 101
DECLARE @cn INT
EXEC p_GetNewCustomerNum 101, @cn OUTPUT 
SELECT @cn AS [cn]
/*
cn
-----------
2
*/
GO
-- It generate three CustomerNums for tenant 101
DECLARE @cn INT
EXEC p_GetNewCustomerNum 101, @cn OUTPUT, 3
SELECT @cn AS [cn]
/*
cn
-----------
5           <-- This ID means that following range was reserved [(5-3)+1, 5] = [3, 5] = {3, 4, 5}; Note: 1 = Step
*/
GO
-- It generate one CustomerNums for tenant 102
DECLARE @cn INT
EXEC p_GetNewCustomerNum 102, @cn OUTPUT 
SELECT @cn AS [cn]
/*
cn
-----------
1112
*/
GO
-- End status of Master table
SELECT * FROM dbo.Master
/*
TenantNum   LastCustomerNum InitialValue Step
----------- --------------- ------------ -----------
101         5               1            1
102         1112            1            1
*/
GO

.

-- Tests #2: To test concurent sesssions / TX you could use bellow script
-- Step 1: Session 1
BEGIN TRAN
    -- It generate three CustomerNums for tenant 101
    DECLARE @cn INT
    EXEC p_GetNewCustomerNum 101, @cn OUTPUT
    SELECT @cn AS [cn] -- > It generates @cn 6



-- Step 2: Session 2
BEGIN TRAN
    -- It generate three CustomerNums for tenant 101
    DECLARE @cn INT
    EXEC p_GetNewCustomerNum 101, @cn OUTPUT -- Update waits for Session 1 to finish
    SELECT @cn AS [cn]
COMMIT


-- Step 3: Session 1
COMMIT -- End of first TX. Check Session 2: it'll output 7.

First end note: to manage transactions and exceptions I would use SET XACT_ABORT ON and/or BEGIN TRAN ... END CATCH. Discussion about this topic is beyond the the purpose of this answer.

Second end note: see updated section "How it works ?" (bullet 3 and 4).

Answer 2

I have not reviewed the existing solutions because they are quite long and elaborate. Wouldn't the following be all you need?

CREATE TABLE MasterDB.dbo.Sequences (ClientId INT NOT NULL PRIMARY KEY, LastGeneratedNumber INT NOT NULL)

DECLARE @nextId INT; //Holds the newly allocated ID

UPDATE MasterDB.dbo.Sequences
SET LastGeneratedNumber = LastGeneratedNumber + 1, @nextId = LastGeneratedNumber + 1
WHERE ClientId = 1234

This is correct under any isolation level and any index structure. The row holding the ID information will be U or X locked by the engine.

In case there never has been an ID generated this update statement will not do anything. You can solve that by using MERGE or by using control flow. I recommend MERGE.

Or, you insert a row whenever you create a new client. Set LastGeneratedNumber = 1000 - 1.

There is no need to use stored procedures but you certainly can. There is almost no performance difference to executing this T-SQL as a batch from the application. Do what's more convenient to you.

If you make this T-SQL part of your main transaction the ID assignment will be transactional. It will potentially roll back and it will serialize customer creation. If you don't like that use a separate transaction. That way IDs might be lost, though. This is unavoidable in any solution.

A variation the UPDATE given above is:

UPDATE MasterDB.dbo.Sequences
SET LastGeneratedNumber = LastGeneratedNumber + 1
OUTPUT INSERTED.LastGeneratedNumber
WHERE ClientId = 1234

You could use one sequence per client. This requires that your application executes DDL. This can be awkward. Also, you cannot make ID generation transactional. There is less control. I would not recommend that but it's possible.

Answer 3

I know this is little bit late, but still hope it helps you :)

We do have same situation over here... And we solved it by having a common table in a separate database, which contains only three columns (i.e. tablename, columnname and LastIndex). Now, we use a separate SP to always getting new number from this table with separate transaction (as it should always commit, regardless your main Insert function true/false). So, this will always return with new ID to any request, and that new Index will be used for inserting a record.

Let me know if you need any sample on this.

Answer 4

You want to use a Sequence, for example:

CREATE SEQUENCE Customer_Number_Seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1000
 MAXVALUE 100
 CYCLE;

and then possibly something like :

 CREATE TABLE Customers
(customer_nbr INTEGER DEFAULT NEXT VALUE FOR Customer_Number_Seq,
 .... other columns ....

The documentation has more details.