November 16, 2020 at 12:14 am
Hello,
Recently I came across an issue in production where a sequence is not always returning a number. Below is the create script from a year ago when it was implemented and code snippet from a stored procedure with all the other code removed.
From the code snippet, the error will get thrown during high traffic. There are a couple other processes that also use the same sequence object and have never had an issue. Does anyone have any suggestions?
Sequence was created on 2014 and server has since been updated to 2019
create sequence dbo.SubscriberIDs
as int
start with 200000000
increment by 1
no cycle
cache 500
DECLARE @NewID varchar(20);
--Code in between removed
BEGIN TRY
BEGIN TRAN;
SET @NewID = NEXT VALUE FOR dbo.SubscriberIDs;
IF ISNULL(@NewID,'')='' THROW 50002, 'Subscriber ID was not generated', 1;
COMMIT TRAN;
END TRY
November 16, 2020 at 2:36 pm
I used the below, which calls 1 million times (required catch block to be executable), and couldn't get a null (I assume that's what you meant by not always returning a number?).
What are the current value & last used value of the sequence?
I assume there is a reason you are setting the sequence in a varchar rather than integer variable?
SELECT current_value, last_used_value FROM sys.sequences WHERE name = 'SubscriberIDs' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo';
SET NOCOUNT ON;
CREATE sequence dbo.SubscriberIDs
as int
start with 200000000
increment by 1
no cycle
cache 500
DECLARE @NewID varchar(20);
DECLARE @NewIntID INt;
DECLARE @Counter INT = 0;
WHILE @Counter < 1000000
BEGIN
BEGIN TRY
BEGIN TRAN;
SET @NewID = NEXT VALUE FOR dbo.SubscriberIDs;
SET @NewIntID = NEXT VALUE FOR dbo.SubscriberIDs;
SET @Counter = @Counter + 1;
PRINT CONCAT('@Counter = ', @Counter, '; @NewIntID = ', @NewIntID, '; @NewID = ', @NewID,';')
IF ISNULL(@NewID,'')='' THROW 50002, 'Subscriber ID was not generated', 1;
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT @@ERROR, ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE();
END CATCH;
END
DROP SEQUENCE dbo.SubscriberIDs;
November 16, 2020 at 2:38 pm
When you say it is not returning a number, what is it returning?
Also, in your code, you are declaring @newID as a VARCHAR and then setting it to the result of an INT. Wouldn't it make more sense to match those data types (ie have them both as INT)?
When you say high traffic, how much are you talking about? Any chance you have run out of subscriber ID sequence numbers?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 16, 2020 at 2:40 pm
I guess the sequence must be called so many times that you're running out of values for an int.
Maybe use a bigint for the sequence?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 16, 2020 at 10:48 pm
This code works if I execute it separately. Sql converts it to varchar. Yes I agree the variable should be int, but I can't change it being a prod environment.
Sequence still has alot of numbers left, sorry not at my work computer to check it.
As far as traffic, it happens when this other team's API calls it around 100x within a minute. This issue doesn't always occur, and I don't know what it's returning, I'm assuming a null because of the IF statement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply