1

I'm using a stored procedure in SQL Server to get the next available number for a tenant. Here is the table, that holds the counter information:

CREATE TABLE [NumbersPool]
(
    TenantId [varchar](32) NOT NULL,
    Counter [bigint] NOT NULL DEFAULT 0
)

And here is the stored procedure:

CREATE PROCEDURE getNextNumber
    (@tenantId varchar(32)) 
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @Counter AS BIGINT

    SELECT @Counter = Counter
    FROM NumbersPool WITH (TABLOCK, HOLDLOCK)
    WHERE TenantId = @tenantId

    IF @Counter IS NULL
    BEGIN
        SET @Counter = 0

        INSERT INTO NumbersPool (TenantId, Counter) 
        VALUES (@tenantId, @Counter)
    END
    ELSE
    BEGIN
        SET @Counter = @Counter + 1

        UPDATE NumbersPool
        SET Counter = @Counter
        WHERE TenantId = @tenantId
    END

    RETURN @Counter
END

Now the problem. The procedure seems to work fine with multiple concurrent calls (is called in a transaction with serializable isolation level), but at some point the counter jumps from e.g. 1992 to 25276 without any obvious reason.

Does anybody see any issues in it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gera
  • 11
  • 1
  • 4
    Why not use a `SEQUENCE`? – Thom A Jan 23 '20 at 20:23
  • https://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server – PM 77-1 Jan 23 '20 at 20:28
  • 2
    TABLOCK is the wrong lock hint. Should be UPDLOCK, or TABLOCKX. With TABLOCK you still get a shared lock, which will lead to deadlocks on UPDATE. – David Browne - Microsoft Jan 23 '20 at 20:30
  • The table and the SP will become a hotspot if you have lots of concurrent request for the counter. So it can be the bottle neck for a highly concurrent system – PeterHe Jan 23 '20 at 21:21
  • The SP needs to be called in a transaction, or you should have BEGIN TRAN/COMMIT in your SP – PeterHe Jan 23 '20 at 21:23
  • Thanks for the quick replies and good suggestions!! @Larnu since I need a separate sequence for each tenant in our system I would need to create about a thousand sequences, so I thought a table might be simple to manage. – Gera Jan 24 '20 at 04:23

0 Answers0