Home Dashboard Directory Help
Search

Violation of PRIMARY KEY constraint with identity column by mikegoodtampa


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


3
0
Sign in
to vote
Type: Bug
ID: 778610
Opened: 2/5/2013 2:00:38 PM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

We have table with an identity integer primary key, and a single procedure inserts to it. This has worked for years and has inserted 100's of millions of rows without problem. A few months ago we upgraded to SQL 2012, but this code and this table did not change. Today we were alerted to insert failure, PK violation, error specified the key that was problem. Such a row already existed. This has only happened once.

We manually verified that the current identity seed is correct and continues to increase as expected. We've run DBCC CHECKIDENT (table, NO_RESEED), came back clean.

The only possibly theory I can come up with is that there is a bug that allows this to happen very rarely--two simultaneous connections get same identity value, one fails.
Details
Sign in to post a comment.
Posted by Rb_Brewer on 1/7/2014 at 11:59 AM
I am having similar issue but with Sql Server 2008 R2 SP2. I can insert thousands of records to my table and then suddenly get a duplicate primary key error on an identity column. I never specify the column and the seed looks good. Please advise.
Posted by Microsoft on 3/14/2013 at 5:38 PM
Dear Customer,

I am the dev owning the identity feature. Thanks for reporting the bug.

The identity feature has been changed in SQL2012, so I need to investigate your case with full details.
Can you try to give me a report or the dump when the error is raised?
Also, I may need the sp that you descibed from your report.
If you can give me the above, then I may be able to find the root cause.

Thanks very much!
Bryan
Sign in to post a workaround.