@CU04051204-0
I was also facing the same issue while using the stored procedure. The issue is now resolved after making a change.
Please try altering the stored procedure [dbo].[getNumInternalWithNoBlocking] and check if it resolves the problem on your end as well.
USE [AxDB]
GO
/****** Object: StoredProcedure [dbo].[getNumInternalWithNoBlocking] Script Date: 10/29/2025 9:52:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getNumInternalWithNoBlocking]
@numberid bigint,
@globalTransId bigint,
@UserId nvarchar(20),
@sessionid int,
@sessionLoginDateTime datetime,
@deploymentNumberId bigint,
@Partition bigint,
@nextValue bigint output
AS
BEGIN
-- hesitant to add one more SP as artifact rather than duplicate a code;
-- using SysFlighting rather than using ECSFlighting - as it is simple to retire a feature flight - instead of passing through code
-- not making this mandatory - from rollout - these fixes are needed for edge cases - we more often do not have diff partition if
-- need only for on-prem to cloud upgrade - -enableNumSeqConcurrentIssueInPrePopulateFix - need when prefetch value is high
-- -enableCNSEnableOrderByInPickUpClause - this is needed only for wierd customer ask - as CONTINUOUS KEYWORD IS MISNOMER (it is no gap not sequential)
-- documented - still it keeps coming!
DECLARE @enableNumSeqConcurrentIssueInPrePopulateFix int =0;
DECLARE @enableCNSEnableOrderByInPickUpClause int =0;
DECLARE @PerformPartitionCheckFlag int = 0;
DECLARE @extended int;
DECLARE @continuous int;
DECLARE @fetchAheadQty bigint;
DECLARE @DefaultMaxAssignmentConfigCount int = 5;
DECLARE @MaxAssignmentConfigCount int = @DefaultMaxAssignmentConfigCount;
DECLARE @NextValueTable TABLE (NextValue BIGINT);
--whether we should start take a lock on Number Sequence table while populating record in pre-populate
DECLARE @EnableNumSeqConcurrentIssueInPrePopulateFlightName nvarchar(512) = 'EnableNumSeqConcurrentIssueInPrePopulateFix';
DECLARE @EnableCNSEnableOrderByInPickUpClauseFlightName nvarchar(512) = 'EnableCNSEnableOrderByInPickUpClause';
DECLARE @PerformPartitionCheckFlightName nvarchar(512) = 'PerformPartitionCheckInNumberSequence';
WITH FlightStatus AS (
SELECT FlightName, Enabled
FROM dbo.SysFlighting WITH (NOLOCK)
WHERE FlightName IN (
@EnableNumSeqConcurrentIssueInPrePopulateFlightName,
@EnableCNSEnableOrderByInPickUpClauseFlightName,
@PerformPartitionCheckFlightName
)
)
SELECT
@enableNumSeqConcurrentIssueInPrePopulateFix = MAX(CASE WHEN FlightName = @EnableNumSeqConcurrentIssueInPrePopulateFlightName THEN Enabled ELSE 0 END),
@enableCNSEnableOrderByInPickUpClause = MAX(CASE WHEN FlightName = @EnableCNSEnableOrderByInPickUpClauseFlightName THEN Enabled ELSE 0 END),
@PerformPartitionCheckFlag = MAX(CASE WHEN FlightName = @PerformPartitionCheckFlightName THEN Enabled ELSE 0 END)
FROM FlightStatus;
-- better to explicit it to 0 ; incase no record found
SET @enableNumSeqConcurrentIssueInPrePopulateFix = ISNULL(@enableNumSeqConcurrentIssueInPrePopulateFix, 0);
SET @enableCNSEnableOrderByInPickUpClause = ISNULL(@enableCNSEnableOrderByInPickUpClause, 0);
SET @PerformPartitionCheckFlag = ISNULL(@PerformPartitionCheckFlag, 0);
SELECT TOP 1 @continuous = [CONTINUOUS], @extended = [EXTENDED], @fetchAheadQty = [FETCHAHEADQTY] FROM [dbo].[NUMBERSEQUENCETABLE] WITH (NOLOCK) WHERE [NUMBERSEQUENCETABLE].[RECID] = @numberid;
DECLARE @MaxAssignmentConfigCountName varchar(256) = 'VOLATILE_NUMSEQ_CNS_MAXASSIGNMENTCOUNT';
SELECT TOP 1 @MaxAssignmentConfigCount = [value] from [dbo].sysGlobalConfiguration (nolock) where [NAME] =@MaxAssignmentConfigCountName;
if @MaxAssignmentConfigCount IS NULL or @MaxAssignmentConfigCount <= 0
BEGIN
SET @MaxAssignmentConfigCount = @DefaultMaxAssignmentConfigCount;
END
--Get the next available free number from NSL table.
if @enableCNSEnableOrderByInPickUpClause > 0
BEGIN
;WITH TargetRow AS (
SELECT TOP(1) * FROM NUMBERSEQUENCELIST WITH (ROWLOCK, READPAST)
WHERE
NUMBERSEQUENCEID = @numberid AND STATUS = 0
and (@PerformPartitionCheckFlag = 0 OR PARTITION = @Partition)
ORDER BY RecId
)
UPDATE TargetRow
SET
STATUS = 4, TRANSID = @globalTransId,MODIFIEDTRANSACTIONID = @globalTransId, USERID = @UserId,MODIFIEDBY = @UserId,
SESSIONID = @sessionid,SESSIONLOGINDATETIME = @sessionLoginDateTime
OUTPUT
CASE WHEN @extended != 0 THEN INSERTED.NEXTREC64 ELSE INSERTED.NEXTREC END
INTO @NextValueTable;
SELECT @nextValue = NextValue FROM @NextValueTable;
select @nextValue as DidWeFindValue;
END
ELSE
BEGIN
UPDATE top(1) [NUMBERSEQUENCELIST] with (rowlock, readpast)
SET
STATUS=4, TRANSID=@globalTransId, MODIFIEDTRANSACTIONID=@globalTransId, USERID=@UserId,
MODIFIEDBY=@UserId, SESSIONID=@sessionid, SESSIONLOGINDATETIME=@sessionLoginDateTime,
@nextValue= CASE WHEN @extended != 0 THEN [NEXTREC64] ELSE [NEXTREC] END
WHERE
NUMBERSEQUENCEID= @numberid and STATUS = 0 and (@PerformPartitionCheckFlag = 0 OR PARTITION = @Partition)
END
IF @nextValue = 0 or @nextValue is null
BEGIN
-- if the free numbe is not present in the NSL table, generate the new numbers and then return it.
exec prePopulateNumbers @numberseqid=@numberid, @maxquantity=@fetchAheadQty, @enableNumSeqConcurrentIssueInPrePopulateFix = @enableNumSeqConcurrentIssueInPrePopulateFix;
if @enableCNSEnableOrderByInPickUpClause > 0
BEGIN
;WITH TargetRow AS (
SELECT TOP(1) * FROM NUMBERSEQUENCELIST WITH (ROWLOCK, READPAST)
WHERE
NUMBERSEQUENCEID = @numberid AND STATUS = 0
and (@PerformPartitionCheckFlag = 0 OR PARTITION = @Partition)
ORDER BY RecId
)
UPDATE TargetRow
SET
STATUS = 4, TRANSID = @globalTransId,MODIFIEDTRANSACTIONID = @globalTransId, USERID = @UserId,MODIFIEDBY = @UserId,
SESSIONID = @sessionid,SESSIONLOGINDATETIME = @sessionLoginDateTime
OUTPUT
CASE WHEN @extended != 0 THEN INSERTED.NEXTREC64 ELSE INSERTED.NEXTREC END
INTO @NextValueTable;
SELECT @nextValue = NextValue FROM @NextValueTable;
END
ELSE
BEGIN
UPDATE top(1) [NUMBERSEQUENCELIST] with (rowlock, readpast)
SET
STATUS=4, TRANSID=@globalTransId, MODIFIEDTRANSACTIONID=@globalTransId, USERID=@UserId,
MODIFIEDBY=@UserId, SESSIONID=@sessionid, SESSIONLOGINDATETIME=@sessionLoginDateTime,
@nextValue= CASE WHEN @extended != 0 THEN [NEXTREC64] ELSE [NEXTREC] END
WHERE
NUMBERSEQUENCEID= @numberid and STATUS = 0 and (@PerformPartitionCheckFlag = 0 OR PARTITION = @Partition);
END
END
select @nextValue;
END
GO
