Tim Liberty Tim Liberty - 4 months ago 10
SQL Question

Generating custom ID for my application

I want to generate a custom ID for one of the feature in my application. Here is the procedure to do that:

CREATE PROCEDURE [dbo].[GetNextVendorInvoiceNo]
AS
BEGIN

Declare @StartingVendorInvoiceNo int = 0

Select @StartingVendorInvoiceNo = MAX(StartingVendorInvoiceNo) + 1
From SystemSettings WITH (TABLOCK)

Update SystemSettings
Set StartingVendorInvoiceNo = @StartingVendorInvoiceNo


Select @StartingVendorInvoiceNo

END


Would there be any issue if multiple users end up calling this procedure. Obviously I don't want multiple users to have the same ID. I am using TABLOCK but not sure if this is the right way or anything else too is required.

Answer

SQL Server 2012 has SEQUENCE feature, which is definitely safe for multi-user environment. It is based on an integer type, though.

If you have a complex procedure that generates a "next" ID and you want to make sure that only one instance of the procedure runs at any moment (at the expense of throughput), I'd use `sp_getapplock'. It is easy to use and understand and you don't need to worry about placing correct query hints.

Your procedure would look like this:

CREATE PROCEDURE [dbo].[GetNextVendorInvoiceNo]
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;
    BEGIN TRY

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'GetNextVendorInvoiceNo_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        Declare @StartingVendorInvoiceNo int = 0;

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock, generate the "next" ID
            Select @StartingVendorInvoiceNo = MAX(StartingVendorInvoiceNo) + 1
            From SystemSettings;

            Update SystemSettings
            Set StartingVendorInvoiceNo = @StartingVendorInvoiceNo;

        END ELSE BEGIN
            -- TODO: handle the case when it takes too long to acquire the lock,
            -- i.e. return some error code
            -- For example, return 0
            SET @StartingVendorInvoiceNo = 0;
        END;

        Select @StartingVendorInvoiceNo;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- TODO: handle the error
    END CATCH;

END

Simple TABLOCK as you wrote is definitely not enough. You need to wrap everything into transaction. Then make sure that lock is held till the end of the transaction, see HOLDLOCK. Then make sure that the lock you are getting is the correct one. You may need TABLOCKX. So, overall you need a pretty good understanding of all these hints and how locking works. It is definitely possible to achieve the same effect with these hints. But, if the logic in the procedure is more complicated than your simplified example it can easily get pretty ugly.

To my mind, sp_getapplock is easy to understand and maintain.

Comments