Error: Cannot insert duplicate key row in... a non-unique index?!

  • Eric M Russell

    SSC Guru

    Points: 125010

    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

  • ScottPletcher

    SSC Guru

    Points: 98087

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Eric M Russell

    SSC Guru

    Points: 125010

    ScottPletcher - Monday, December 17, 2018 12:11 PM

    None 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

  • Eric M Russell

    SSC Guru

    Points: 125010

    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

  • ScottPletcher

    SSC Guru

    Points: 98087

    Eric M Russell - Monday, December 17, 2018 12:25 PM

    ScottPletcher - Monday, December 17, 2018 12:11 PM

    None 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Eric M Russell

    SSC Guru

    Points: 125010

    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

  • ScottPletcher

    SSC Guru

    Points: 98087

    Eric M Russell - Monday, December 17, 2018 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).

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Eric M Russell

    SSC Guru

    Points: 125010

    ScottPletcher - Monday, December 17, 2018 12:57 PM

    Eric M Russell - Monday, December 17, 2018 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).

    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

  • Eric M Russell

    SSC Guru

    Points: 125010

    I'm going to look into setting up a trace to capture the insert statements tonight, hoping to get the execution plan and other session settings like isolation level. They tell me read committed, but read uncommitted may explain what's going on. Maybe.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • RAThor

    SSC Eights!

    Points: 801

    Eric M Russell - Monday, December 17, 2018 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).

    Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?

  • Eric M Russell

    SSC Guru

    Points: 125010

    RAThor - Tuesday, December 18, 2018 7:35 AM

    Eric M Russell - Monday, December 17, 2018 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).

    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

  • frederico_fonseca

    SSChampion

    Points: 14086

    Eric M Russell - Tuesday, December 18, 2018 10:26 AM

    RAThor - Tuesday, December 18, 2018 7:35 AM

    Eric M Russell - Monday, December 17, 2018 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).

    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

  • Eric M Russell

    SSC Guru

    Points: 125010

    frederico_fonseca - Tuesday, December 18, 2018 11:18 AM

    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

    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

  • Jeff Moden

    SSC Guru

    Points: 994555

    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


    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88080

    Just curious - but is it possible the PAGE compression on the index is contributing to this issue?

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply