Violation of PRIMARY KEY constraint with identity column

Violation of PRIMARY KEY constraint with identity column by mikegoodtampa

Resolved

as Not Reproducible Help for as Not Reproducible

Type:	Bug
ID:	778610
Opened:	2/5/2013 2:00:38 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 - Enterprise Core Edition

SQL Engine

Windows Server 2008 Enterprise

US English

No sure you can. We have tried and not been able to reproduce in non-prod environment. Create table with integer identity (1, 1) as PK; populate with 160M rows, then bang on it with multiple concurrent inserts until you run into this error.

In our case, this table is in a DB that belongs to an availability group, which has both readable (async) and non-readable (sync) replicas.

Violation of PRIMARY KEY attempting to insert to table. On subsequent inspection, another SPID has inserted row with same identity value reported in the error.

The insert stmt is in a stored proc that is unchanged for a long time, and continues to function. The insert stmt does not specify the identity PK column.

Inserts should succeed. Multiple concurrent inserts should queue up and succeed.

X64

0 attachments

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