December 17, 2018 at 12:05 pm
Cannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
This error first started occurring several weeks ago during the nightly insert/update process for a data warehouse, and it re-occurs occasionally, maybe once a week or every couple of weeks. There is no apparent correlation between the data and the error. The error occurs seemingly randomly on DEV, UAT, and Production. UAT and Production actually query from the same source database daily, because the goal is to keep the database model and data between the two environments in tandem, yet the error can occur independently in one environment but not the other.
I know what you're going to say, but actually there is NOT a unique constraint nor a foreign key constraint on the index IX_Payment_PurchaseId; and also no triggers on the table. There are actually a handful of different tables where this key violation issue is occurring, but I'll use PurchasePayment as the example here.
Here is the definition of the index cited in the error:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
There is a non-clustered primary key and then a clustered unique index, neither of which are ever cited in the duplicate key error messages.
CONSTRAINT [PK_PurchasePayment]
PRIMARY KEY NONCLUSTERED
(
[PurchasePaymentId] ASC,
[StoreCode] ASC,
[RowEndDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [ps_RowEndDate]( [RowEndDate] ),
CONSTRAINT [UQ_PurchasePayment_RowStartDate_PurchasePaymentId]
UNIQUE CLUSTERED
(
[RowStartDate] ASC,
[RowEndDate] ASC,
[PurchasePaymentId] ASC,
[SourceStore] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [ps_RowEndDate]([RowEndDate])
) ON [ps_RowEndDate]([RowEndDate])
Doing some searching has revealed similar issues in versions 2008 - 2014, and the reccomended solution has been to apply the latest CU.
https://www.sqlservercentral.com/Forums/1934540/Cannot-insert-duplicate-key-row-in-object
https://social.msdn.microsoft.com/Forums/en-US/964cc26c-9c41-41dd-8965-b10dbc03e0e1/cannot-insert-duplicate-key-row-in-object-xxx-with-unique-index-yyy-value0x8be8070005000300
So, I applied the latest CU4 to SQL Server 2016 SP2 and was optimistic for a week, until it happened again. So, the CU has not resolved the issue.
Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)
Nov 3 2018 00:01:54
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
For a workaround, the following MS support article suggests trace flag 8690 to disable the Spool operation. I havn't resorted to this yet, because I can't intentionally reproduce the issue, and it could be weeks before it occurs again.
https://support.microsoft.com/en-us/help/2962767/fix-cannot-insert-duplicate-key-error-occurs-when-you-update-a-table-b
I'm reach out here for ideas or common experience before I resort to opening a support case with Microsoft.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:11 pm
None of those indexes match the name in the error message.
What is the DDL for index IX_Payment_PurchaseId?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 12:25 pm
ScottPletcher - Monday, December 17, 2018 12:11 PMNone of those indexes match the name in the error message.What is the DDL for index IX_Payment_PurchaseId?
Sorry for the mistype. I have corrected the index name in the original post to match the name in the error message.
Here it is again:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:30 pm
I have confirmed with the ETL developers that they are using READ COMMITTED ISOLATION on both remote source database and target database.
The version of the source database server is as follows:
Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) Sep 23 2016 16:56:29
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:31 pm
Eric M Russell - Monday, December 17, 2018 12:25 PMScottPletcher - Monday, December 17, 2018 12:11 PMNone of those indexes match the name in the error message.What is the DDL for index IX_Payment_PurchaseId?
Sorry for the mistype. I have corrected the index name in the original post to match the name in the error message.
Here it is again:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
Interesting. I take it those 4 columns are not unique, it takes the "uniquifier" -- in this case 32999 -- to make them unique. If it's possible you might be deleting all the old rows with those leading 4 keys, I'm guessing that SQL for whatever reason lost the "ghost row" it sometimes seems to use to keep track of the uniquifier, and thuse ended up reusing a value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 12:50 pm
I'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.
Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)
I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:57 pm
Eric M Russell - Monday, December 17, 2018 12:50 PMI'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
As I stated, I think it's the uniquifier for that index, and that somehow SQL is reusing a uniquifier (which of course it should never do, hence it clearly seems to be a bug; I was just guessing the cause of the bug, but purely a guess).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 1:10 pm
ScottPletcher - Monday, December 17, 2018 12:57 PMEric M Russell - Monday, December 17, 2018 12:50 PMI'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
As I stated, I think it's the uniquifier for that index, and that somehow SQL is reusing a uniquifier (which of course it should never do, hence it clearly seems to be a bug; I was just guessing the cause of the bug, but purely a guess).
OK, I see your point now.
My understanding is that: Every row in the leaf level of a b-tree index should uniquely point to one row in the table, even for logically non-unique indexes. SQL Server does this by adding a uniquifier, and this mystery 5th value of 32999 is the uniquifier. Your theory regarding the duplicate key violation is that SQL Server is somehow duplicating the uniquifier?
That might be the case, but for non-unique non-clustered indexes, I would think the uniquifier would be the actual row id coming from the table. It may be time to get Microsoft PSS involved on this one.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 1:20 pm
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 7:35 am
Eric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
December 18, 2018 at 10:26 am
RAThor - Tuesday, December 18, 2018 7:35 AMEric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
I've scripted out the table and confirmed no triggers, change tracking, CDC, FK constraints, function based defaults, function based check constraints, or computed columns. It's just a very large (100+ GB) table with basic column design. I suspect it's the DML against the table that's tricky. I'm currently looking at Query Store to try and isolate which operation was running against this specific table around the time the error occurred.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 11:18 am
Eric M Russell - Tuesday, December 18, 2018 10:26 AMRAThor - Tuesday, December 18, 2018 7:35 AMEric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
I've scripted out the table and confirmed no triggers, change tracking, CDC, FK constraints, function based defaults, function based check constraints, or computed columns. It's just a very large (100+ GB) table with basic column design. I suspect it's the DML against the table that's tricky. I'm currently looking at Query Store to try and isolate which operation was running against this specific table around the time the error occurred.
can you also post the DDL for index IX_Payment_PurchaseId on Table Payment - not the one you posted before as it is a different table
December 18, 2018 at 11:54 am
frederico_fonseca - Tuesday, December 18, 2018 11:18 AMcan you also post the DDL for index IX_Payment_PurchaseId on Table Payment - not the one you posted before as it is a different table
I'm having the obscure the actual object names. The index cited in the error is as originally posted, although I posted it with the wrong table name originally.
Cannot insert duplicate key row in object 'PurchasePayment' with unique index 'IX_Payment_PurchaseId'.
The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 12:34 pm
Check for other dependencies on the table. I'm thinking that someone may have snuck in an indexed view or synonym on you. I can't imagine why it would cause a problem but you might also want to check to see if any form of auditing has been enabled.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2018 at 2:56 pm
Just curious - but is it possible the PAGE compression on the index is contributing to this issue?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply