Home Dashboard Directory Help
Search

Next value for SEQUENCE can fail to return proper next value by jchampagne


Status: 

Active


7
0
Sign in
to vote
Type: Bug
ID: 790161
Opened: 6/14/2013 1:21:07 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

This is not an easily reproduced condition - it happens every now and then in a development environment I own. Basically under rather heavy load sometimes I will get an error like: "Violation of PRIMARY KEY constraint 'PK__StoredTraceSQL__6A43A4A7'. Cannot insert duplicate key in object 'dbo.StoredTraceSQL'. The duplicate key value is (-9223372036853896059).
The statement has been terminated." In this case, the PK is a bigint with a DEFAULT that's NEXT VALUE FOR a SEQUENCE. If I manually reseed / restart the sequence with the table's MAX value + 1, things start to work again - until the problem randomly happens again in the future.

I know no-one likes the "I can't reproduce it easily", but this is actually happening for me, and I'm hoping perhaps someone else has seen it.
Details
Sign in to post a comment.
Posted by EugenePac on 6/26/2014 at 7:52 PM
We've experienced a similar issue when doing a database backup.

After restoring the database the current value of the sequence may be up to 10 less than the Primary Key on a table.

(Caching is set to DEFAULT).
Posted by Steve Powell on 1/24/2014 at 1:01 AM
Interestingly it seems this is only failing on 2 environments that have the build number 11.0.3000.0 which was the first release of SQL2012 SP1, a subsequent release 11.0.3128.0 which fixed some installer issues seems to have also corrected this problem.

The other environments seem to have

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)


and do not exhibit the same behaviour. So it would seem this was fixed between these two builds and it is a legacy issue of the mixed platforms we have.
Posted by Steve Powell on 1/22/2014 at 7:05 AM
See my note here for details of an instance of this that should be easy to reproduce

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8909d16a-80ad-42ce-a92a-8bc80dca822d/sequence-issuing-numbers-more-than-once?forum=sqldatabaseengine

Posted by ct5125 on 11/5/2013 at 4:13 AM
Yes, I have seen something similar a couple of times, with the PK set in the same way. I can't yet reproduce it though, and the very next insert into the table will also assign the correct value. I've only seen it under heavy load.
Posted by Microsoft on 8/19/2013 at 10:24 AM
Thank you for submitting this issue. Since we have received no response to our requests for a repro, we are closing this case. Please open a CSS support case or reactivate this case, if you encounter this issue again.

Thanks again for your continued support in our products.
Posted by Microsoft on 7/25/2013 at 12:15 PM
Thank you for submitting this feedback.

Could you please provide us with the schema, data and a detailed description of the steps required to reproduce this issue.

Thanks again for reporting the product issue and continued support in improving our product.
Sign in to post a workaround.