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]
Declare @StartingVendorInvoiceNo int = 0
Select @StartingVendorInvoiceNo = MAX(StartingVendorInvoiceNo) + 1
From SystemSettings WITH (TABLOCK)
Set StartingVendorInvoiceNo = @StartingVendorInvoiceNo
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
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.