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 7007, 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 5, 2012 at 2:03 pm
well so far, i haven't been able to get a duplicate in my limited testing;
i was thinking that maybe a developer had a transaction open, went to lunch, and then committed it later, but i didn't get any duplicates; even when hammering it with million row transactions.
are there any triggers ont he table?
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
CREATE TABLE IntegrationQueue( IntegrationQueueID bigint,SomeData varchar(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]
--repeated multiple times
insert into IntegrationQueue(SomeData) select 'IL:' + CONVERT(VARCHAR,GETDATE(),112) + '-' + CONVERT(VARCHAR,GETDATE(),114) from sys.tables T1 CROSS APPLY sys.tables T2
--in another window, in a transaction, i did this at various intervals
insert into IntegrationQueue(SomeData) select 'TX:' + CONVERT(VARCHAR,GETDATE(),112) + '-' + CONVERT(VARCHAR,GETDATE(),114) from sys.tables CROSS APPLY sys.tables T2
Lowell
December 5, 2012 at 3:03 pm
Hi Lowell,
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 (I erroneously identified this as 7007 above) 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 10, 2012 at 2:15 pm
I continued to have this problem, 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 3 days now. It was happening once a day.
January 8, 2013 at 11:36 am
After seeing no problems with NOCACHE, I bumped the cache up to 10. The problem returned and I noticed that the value the sequence is "resetting" to the value of the cache. That is, if the last value of the sequence is at 1050 and my sequence cache is set to 5, when this problem occurs, the sequence reverts back to 1046, causing the next 4 inserts to fail before the sequence catches up to where it was.
We are using Always on in our environment; I don't know if this has anything to do with it. The reset does not appear to be related to fail-overs in the availability groups.
I will see if I can created an repeatable failure.
January 23, 2013 at 9:07 am
I have had this exact problem before. I re-deployed our database from .sql scripts (no changes), and never saw the issue again. I thought i would let you know that you're not the only one who has experienced this.
January 31, 2013 at 10:37 am
Thanks Dwightrau. Unfortunately I'm not in a position to redeploy. 🙁 Interesting that this fixed it for you. You actually dropped the dB and rebuilt it from scratch with scripts? Was the previous version upgraded from 2008 R2?
I haven't had time to do any more testing, so at this point my sequence is set to NOCACHE and I'm not seeing the problem, but I'm also missing out on one of the compelling reasons for using a sequence: an increased cache.
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply