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 5, 2012 1:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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!
Post #1393191
Posted Wednesday, December 5, 2012 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1393200
Posted Wednesday, December 5, 2012 3:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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.
Post #1393234
Posted Monday, December 10, 2012 2:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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.
Post #1394782
Posted Tuesday, January 8, 2013 11:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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.
Post #1404408
Posted Wednesday, January 23, 2013 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 8:47 AM
Points: 1, Visits: 12
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.
Post #1410660
Posted Thursday, January 31, 2013 10:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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.

Post #1414255
Posted Tuesday, February 19, 2013 3:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 53, Visits: 183
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.
Post #1421869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse