Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Duplicate key issue ... again ... and again ... Expand / Collapse
Author
Message
Posted Thursday, March 27, 2014 9:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:53 AM
Points: 411, Visits: 1,310
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.
Post #1555475
Posted Thursday, March 27, 2014 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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)
Post #1555494
Posted Thursday, March 27, 2014 9:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:53 AM
Points: 411, Visits: 1,310
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.
Post #1555496
Posted Thursday, March 27, 2014 2:41 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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
Post #1555659
Posted Thursday, March 27, 2014 3:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:53 AM
Points: 411, Visits: 1,310
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.
Post #1555671
Posted Thursday, March 27, 2014 5:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 35,371, Visits: 31,914
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1555700
Posted Friday, March 28, 2014 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555859
Posted Friday, March 28, 2014 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Can you post the table definition and the procedure?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1555881
Posted Friday, March 28, 2014 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 6,490, Visits: 13,968
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"
Post #1555887
Posted Friday, March 28, 2014 7:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:53 AM
Points: 411, Visits: 1,310
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.
Post #1555915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse