I rolled my own a while ago and it works very well in SQL Server. This functionality should be able to be replicated in other database servers as well, although the locking hints will need to be changed.
There are a lot of options in the Counter table that define counter behavior. They can be ignored for this demonstration. The default values are good enough.
DDL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Counter](
[CounterID] [varchar](128) NOT NULL,
[CounterType] [int] NOT NULL CONSTRAINT [dfCounter_CounterType] DEFAULT (1),
[BranchSpecificFg] [varchar](1) NOT NULL CONSTRAINT [dfCounter_BranchSpecificFg] DEFAULT ('N'),
[MinimumValue] [int] NOT NULL CONSTRAINT [dfCounter_MinimumValue] DEFAULT (0),
[MaximumValue] [int] NOT NULL CONSTRAINT [dfCounter_MaximumValue] DEFAULT (99999999),
[InitialValue] [int] NOT NULL CONSTRAINT [dfCounter_InitialValue] DEFAULT (0),
[AllowRolloverFg] [varchar](1) NOT NULL CONSTRAINT [dfCounter_AllowRolloverFg] DEFAULT ('Y'),
[ReturnZeroPaddedFg] [varchar](1) NOT NULL CONSTRAINT [dfCounter_ReturnZeroPaddedFg] DEFAULT ('N'),
[AlphaInitialValue] [varchar](32) NOT NULL CONSTRAINT [dfCounter_AlphaInitialValue] DEFAULT (''),
[AlphaMaximumLength] [int] NOT NULL CONSTRAINT [dfCounter_AlphaMaximumLength] DEFAULT (0),
[Description] [varchar](255) NOT NULL CONSTRAINT [dfCounter_Description] DEFAULT (''),
CONSTRAINT [pkCounter] PRIMARY KEY CLUSTERED ( [CounterID] ASC )
)
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter__Alpha_Counter_Check__AlphaInitialValue-AlphaMaximumLength] CHECK ((((not([CounterType] = 3))) and ((not([CounterType] = 2))) or [AlphaMaximumLength] > 0 and len([AlphaInitialValue]) = [AlphaMaximumLength]))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter__Numeric_Counter_Check__InitialValue-MinimumInitialValue-MaximumInitialValue] CHECK (([CounterType] <> 1 or [MinimumValue] <= [InitialValue] and [InitialValue] < [MaximumValue]))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_AllowRolloverFg] CHECK (([AllowRolloverFg] = 'Y' or [AllowRolloverFg] = 'N'))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_AlphaMaximumLength] CHECK (([AlphaMaximumLength] >= 0))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_BranchSpecificFg] CHECK (([BranchSpecificFg] = 'Y' or [BranchSpecificFg] = 'N'))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_CounterType] CHECK (([CounterType] = 3 or ([CounterType] = 2 or [CounterType] = 1)))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_InitialValue] CHECK (([InitialValue] >= 0))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_MaximumValue] CHECK (([MaximumValue] >= 0))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_MinimumValue] CHECK (([MinimumValue] >= 0))
GO
ALTER TABLE [Counter] WITH CHECK ADD CONSTRAINT [ckCounter_ReturnZeroPaddedFg] CHECK (([ReturnZeroPaddedFg] = 'Y' or [ReturnZeroPaddedFg] = 'N'))
GO
CREATE TABLE [CounterValue](
[BranchID] [varchar](32) NOT NULL,
[CounterID] [varchar](128) NOT NULL,
[ValueNumber] [int] NOT NULL,
[ValueAlpha] [varchar](32) NOT NULL,
[ModAt] [datetime] NOT NULL CONSTRAINT [dfCounterValue_ModAt] DEFAULT ('18991231'),
[ModBy] [varchar](16) NOT NULL CONSTRAINT [dfCounterValue_ModBy] DEFAULT (''),
CONSTRAINT [pkCounterValue] PRIMARY KEY CLUSTERED
(
[BranchID] ASC,
[CounterID] ASC
)
)
GO
ALTER TABLE [CounterValue] WITH CHECK ADD CONSTRAINT [ckCounterValue_ValueNumber] CHECK (([ValueNumber] >= 0))
GO
Stored Procedure (Removed additional counter types for brevity)
/*****************************************************************************
* Description:
* Returns the next value of the specified CounterID.
******************************************************************************
* Notes:
* 1. CounterID MUST exist in the Counter table before it can be used.
* 2. Using the "hold update" lock hint, the function plays better in multi-user
* environment.
* 3. Use the old value to determine if it has changed. If so, repeat the
* increment attempt.
* 4. CounterType:
* 1 - Rolling Integer
******************************************************************************/
CREATE PROCEDURE [pg_spGetCounter]
(
@CounterID varchar(128),
@BranchID varchar(32) = NULL
)
AS
BEGIN
SET NOCOUNT ON
-- Working Variables
-- =================
DECLARE @iNew integer
DECLARE @sNew varchar(128)
DECLARE @sOld varchar(128)
DECLARE @iOld integer
DECLARE @iLoop integer
-- Counter Variables
-- =================
DECLARE @iCounterType integer
DECLARE @sBranchSpecificFg varchar(1)
DECLARE @iMinimumValue integer
DECLARE @iMaximumValue integer
DECLARE @iInitialValue integer
DECLARE @sAllowRolloverFg varchar(1)
DECLARE @sReturnZeroPaddedFg varchar(1)
DECLARE @sAlphaInitialValue varchar(128)
DECLARE @iAlphaMaximumLength integer
-- Validate Inputs
-- ===============
SET @CounterID = COALESCE(@CounterID, '')
SET @BranchID = COALESCE(NULLIF(@BranchID,''), '<NONE>')
IF @CounterID = ''
BEGIN
RAISERROR('pg_spGetCounter: CounterID cannot be blank.', 16, 1)
RETURN -1
END
-- Get Counter Information
-- =======================
SELECT
@iCounterType = CounterType,
@sBranchSpecificFg = BranchSpecificFg,
@iMinimumValue = MinimumValue,
@iMaximumValue = MaximumValue,
@iInitialValue = InitialValue,
@sAllowRolloverFg = AllowRolloverFg,
@sReturnZeroPaddedFg = ReturnZeroPaddedFg,
@sAlphaInitialValue = AlphaInitialValue,
@iAlphaMaximumLength = AlphaMaximumLength
FROM Counter
WHERE CounterID = @CounterID
-- Validate Data Exists
-- ====================
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('pg_spGetCounter: CounterID ''%s'' does not exist in Counter table. Please add before using.', 16, 1, @CounterID)
RETURN -1
END
-- Validate Branch Data
-- ====================
IF @sBranchSpecificFg = 'Y' AND @BranchID = '<NONE>'
BEGIN
RAISERROR('pg_spGetCounter: CounterID ''%s'' is branch specific but no BranchID was supplied. Please specify a BranchID when using this CounterID.', 16, 1, @CounterID)
RETURN -1
END
ELSE
BEGIN
SET @BranchID = '<NONE>' -- Not branch specific setting... force to <NONE>!
END
-- Validate Counter Value Record Exists
-- ====================================
IF NOT EXISTS (SELECT * FROM CounterValue WHERE CounterID = @CounterID AND BranchID = @BranchID)
INSERT INTO CounterValue (BranchID, CounterID, ValueNumber, ValueAlpha, ModAt, ModBy)
VALUES (@BranchID, @CounterID, @iInitialValue, @sAlphaInitialValue, CONVERT(varchar, CURRENT_TIMESTAMP, 20), 'SYSTEM')
-- ========================================================================================================
-- ========================================================================================================
-- Perform Increment
-- ========================================================================================================
-- ========================================================================================================
-- Standard Rolling Number
-- - Increment number by 1
-- ========================================================================================================
IF @iCounterType = 1
BEGIN
RETRY_COUNTER_TYPE_1:
-- Set @iNew to the next value.
-- The CounterValue table holds the value that was just used!
-- ==========================================================
-- This works but it takes two statements...
SELECT
@iOld = ValueNumber,
@iNew = ValueNumber + 1
FROM CounterValue WITH (UPDLOCK, ROWLOCK)
WHERE CounterID = @CounterID AND BranchID = @BranchID
UPDATE CounterValue
SET ValueNumber = @iNew
WHERE CounterID = @CounterID
AND BranchID = @BranchID
AND ValueNumber = @iOld
/*
-- This method worked great except that it caused another thread to block until the entire batch was finished
-- Probably not a problem (not like anyone is going to execute this function 20000 times in a row in two different
-- sessions), but I recoded it to play nice... but like I said, it doesn't really matter.
UPDATE CounterValue
SET
@iNew = ValueNumber + 1,
ValueNumber = ValueNumber + 1
WHERE CounterID = @CounterID
AND BranchID = @BranchID
*/
-- Perform Validation on the New Record
-- ====================================
IF @iNew > @iMaximumValue -- Is the new value is greater then the maximum?
BEGIN
IF @sAllowRolloverFg = 'Y' -- Are we allowed to start over?
BEGIN
-- Reset the counter.
-- Make sure we don't "reset" the counter if someone else already did
-- ==================================================================
UPDATE CounterValue
SET ValueNumber = @iMinimumValue
WHERE CounterID = @CounterID AND BranchID = @BranchID
AND ValueNumber = @iNew
-- Retry Transaction / Start Over.
-- (Specifically designed not to use the MinimumValue.)
-- ====================================================
GOTO RETRY_COUNTER_TYPE_1
END
ELSE
BEGIN
RAISERROR('pg_spGetCounter: CounterID ''%s'' has reached it''s maximum value of %d and is not configured to rollover. Cannot continue.', 16, 1, @CounterID, @iMaximumValue)
RETURN -1
END
END
-- Return Value
-- ============
IF @sReturnZeroPaddedFg = 'Y'
SELECT RIGHT(REPLICATE('0', LEN(@iMaximumValue)) + CAST(@iNew AS varchar(100)), LEN(@iMaximumValue)) CounterValue
ELSE
SELECT @iNew CounterValue
RETURN(0)
END
-- Not Defined! ERROR!!!!
-- ======================
ELSE
BEGIN
-- NOT DEFINED!
-- ============
RAISERROR('pg_spGetCounter: Counter Type ''%d'' is not implemented. Cannot increment value for CounterID ''%s''.', 16, 1, @iCounterType, @CounterID)
RETURN -1
END
END
GO
Add a Counter ID
INSERT INTO Counter (CounterID) VALUES ('test')
Use the Counter
EXEC pg_spGetCounter 'test'
EXEC pg_spGetCounter 'test'
EXEC pg_spGetCounter 'test'
EXEC pg_spGetCounter 'test'
EXEC pg_spGetCounter 'test'
Results
CounterValue
1
CounterValue
2
CounterValue
3
CounterValue
4
CounterValue
5
Note: The procedure can be modified to return the value via OUTPUT instead of SELECT.