Click here to monitor SSC
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
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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 Moden's 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
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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 at GMail
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45277 Visits: 39934
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17629 Visits: 32268
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 16562
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
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 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