SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Duplicate key issue ... again ... and again ...


Duplicate key issue ... again ... and again ...

Author
Message
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1930
Is someone else experiencing this problem, SQL2012, of course:


Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.



This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.

For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.

My new variables are:

-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)

I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.

Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26038 Visits: 17528
sql-lover (3/27/2014)
Is someone else experiencing this problem, SQL2012, of course:


Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.



This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.

For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.

My new variables are:

-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)

I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.

Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.


I have not experienced anything like this or heard of it.

You didn't provide a lot of detail here. Are these inserts using identity columns or some sort of other way to determine what value to insert? I sort of get the impression this is identity.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1930
Sean Lange (3/27/2014)
sql-lover (3/27/2014)
Is someone else experiencing this problem, SQL2012, of course:


Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.



This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.

For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.

My new variables are:

-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)

I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.

Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.


I have not experienced anything like this or heard of it.

You didn't provide a lot of detail here. Are these inserts using identity columns or some sort of other way to determine what value to insert? I sort of get the impression this is identity.


Yes, you are right. There is a column that uses Identity. So sometimes, the nightly batches fail because the Identity value that the sproc is about to insert is lower than what it is.

I have no technical data to prove is the SAN, or VMware, but I can't say is MS-SQL2012 either, as I never had that issue before. And this happens sporadically. Sometimes one or two times in a week. Sometimes one per month.

I think the ID values are generating on SQL2012 in a different way, but I am not sure. What I am know for sure, is that this is not a code issue and this is not normal.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12403 Visits: 8548
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.

And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.

Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1930
TheSQLGuru (3/27/2014)
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.

And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.

Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?



?

Why is a concurrency issue? Locking mechanism did not change on 2012. Like I said, there is only one sproc that is always inserting there, so no way it can be a concurrency issue. And if that was the case, inserts lock the required pages or table, and block anything else ... insert ... and then sql increases the value. So next insert should use next ID, already increased.

I am almost sure this is a bug, but (or a VMware/SAN issue) but I can't reproduce.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
sql-lover (3/27/2014)
TheSQLGuru (3/27/2014)
This is a concurrency issue, therefore I am 99.984% certain it is a CODE issue. Something somewhere is not properly handling locking and concurrency. As for why this is now starting on 2012 but did not on older SQL Server the most likely cause is that the optimizer is coming up with a different plan that is allowing it to happen and your code isn't dealing with it properly. the 0.016% chance could be a bug.

And like someone else said, you didn't tell us much at all about details of code, structures, indexing, the other processes other than the sproc, etc., etc.

Did you update statistics with a full scan after upgrading to 2012? Also, did you update ANY piece of hardware or the VM during the upgrade?



?

Why is a concurrency issue? Locking mechanism did not change on 2012. Like I said, there is only one sproc that is always inserting there, so no way it can be a concurrency issue. And if that was the case, inserts lock the required pages or table, and block anything else ... insert ... and then sql increases the value. So next insert should use next ID, already increased.

I am almost sure this is a bug, but (or a VMware/SAN issue) but I can't reproduce.


I was going to ask the same question. How would a concurrency issue cause a dupe with an IDENTITY column?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39747 Visits: 32639
Another thought, are there assumptions built into the code that the next value will always be one greater, because starting in SQL Server 2012, there can be large gaps in IDENTITY columns. There's a bug report and a series of work arounds if those gaps cause problems for you here on Connect.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87173 Visits: 45270
Can you post the table definition and the procedure?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19948 Visits: 17242
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing this

sql-lover (3/27/2014)
I fixed it via DBCC command


you are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1930
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing this

sql-lover (3/27/2014)
I fixed it via DBCC command


you are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here


You misread what I said. Of you are confused.

If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)

My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.

I may try later and post table's definition though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search