Hello.
I've started using a SEQUENCE in a table instead of an identity.
I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.
For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7002, but the last inserted row has a primary key of 7032!
I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.
When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.
The definition for the sequence is:
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
The default constraint for the primary key on the table is defined as:
ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]
Does anyone have any ideas how this could be happening or experienced it themselves??
I'm very excited about using sequences moving forward but this is making me nervous about their reliability.
Thanks!
December 12, 2012 at 10:36 am
There are no triggers on the table.
The table has records periodically imported into it, every 5 minutes. Records were created as follows:
integrationQueueID, Created
7002 - 7018, 11:20am
7019 -7023, 11:25am
7024 - 7029, 11:30am
7030 - 7032, 11:35am
On the 11:40 attempt the sequence somehow had been reset to 7002 and the duplicate primary key error occurred.
The import had worked for days and then suddenly the sequence reset to something that had been generated 4 iteration earlier.
December 12, 2012 at 10:37 am
I continued to have this problem for several days, the SEQUENCE somehow "resetting" to a lower value.
I was caching the SEQUENCE at 50 (as seen above). I set the SEQUENCE to NOCACHE and have not seen any problems since.
At this point I'm not sure if it is coincidental or not, but I haven't seen the problem reoccur for 4 days now. It was happening once a day.
October 4, 2013 at 12:15 am
Hi Dave,
I've just started experiencing the same problem that the one you're describing.
And funnily enough it started this week too.
I am using lots of sequences as default constraint in our database tables and our testers started to mention to me that they were getting duplicate key errors quite often (most of the sequences are for primary keys).
I couldn't explain the reason why the sequences would be out of sync and I found your post today.
I've just changed our sequences to NO CACHE and the testers are busy testing that change.
Thanks for sharing as it might save my day lol.
Would you have any more information about this problem? do you see it fixed on your side?
Did you raise a ticket to Microsoft?
Cheers
Hi Frederic.
I do indeed have a solution! It ended up on a parallel thread. That dogged me for a long time. Hope this helps.
-Dave
------
Looks like I've got a solution!
If you are experiencing problems with a SEQUENCE resetting to a lower value, disable CYCLING. When I did this, I was able to leave CACHE set to 50 and no longer have problems with intermittent resetting of the sequence value as described above.
February 19, 2014 at 1:43 pm
same problem here, randomly sql reset sequence to a lower value.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply