Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SEQUENCE Problems Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 51, Visits: 163
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!
Post #1395798
Posted Wednesday, December 12, 2012 10:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 51, Visits: 163
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.
Post #1395801
Posted Wednesday, December 12, 2012 10:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 51, Visits: 163
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.
Post #1395803
Posted Friday, October 04, 2013 12:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:31 PM
Points: 195, Visits: 154
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
Post #1501429
Posted Friday, October 04, 2013 10:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 51, Visits: 163
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.


[url=http://www.sqlservercentral.com/Forums/Topic1393191-373-1.aspx][/url]
Post #1501678
Posted Wednesday, February 19, 2014 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:52 AM
Points: 1, Visits: 8
same problem here, randomly sql reset sequence to a lower value.
Post #1543204
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse