MSSQL duplicate key row exception

Question

We have a rule engine that is categorizing articles. Currently there is an issue, where we get the following error:

Cannot insert duplicate key row in object 'dbo.article_category' with unique index 'IX_article_category_no_duplicates'. The duplicate key value is (123, 456). The statement has been terminated.

The creation of the entry in article_category happens in a stored procedure. The main part of the procedure is this:

MERGE article_category AS [target]
USING (
    SELECT articleId, @categoryId, @creator, @now, @ruleId, 2 
    FROM @articleIdList
) AS [source] (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    ON (
        target.articleId = source.articleId 
        AND target.categoryId= source.categoryId
    )
WHEN NOT MATCHED THEN
    INSERT (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    VALUES (source.articleId, source.categoryId, source.creator, source.createDate, source.ruleId, source.assignmentTypeId);

When two rules try to insert the same category for the same article, this exception above happens.

How is it possible to stop this from happening? I thought that using the merge statement instead of if not exists (...) should prevent this from happening?

Are there other possibilities to prevent this exception?

Thanks in advance

Does @articleIdList have any rows which would cause the duplication? The merge clause is only checked once for the dataset, not one row at a time. — Thom A
– Thom A ♦, Commented Feb 19, 2019 at 12:03
Not sure if it's a duplicate but Aaron Bertrand's answer to this post can help you understand and prevent the race-condition that happens when two inserts of the same key are executed simultaneously. — Zohar Peled
– Zohar Peled, Commented Feb 19, 2019 at 12:06
@Larnu the @articleIdList is unique per call of the stored procedure — xeraphim
– xeraphim, Commented Feb 19, 2019 at 14:02

SMor · Accepted Answer · 2019-02-19 13:52:54Z

1

Independent from merge, if the data you are inserting/updating violates unique constraint, you can have this error on both update or insert.

the merge ... using does not handle Unique Index Constraints. Merge makes insertion if it does not match with the condition held on using statement, otherwise make update operation.

In your case,

target.articleId = source.articleId 
    AND target.categoryId= source.categoryId

does not guarantee uniqueness thus you are getting unique index contraint expception.

edited Feb 19, 2019 at 13:52

SMor

2,8934 gold badges13 silver badges14 bronze badges

answered Feb 19, 2019 at 12:07

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krunal Barot · Accepted Answer · 2019-02-19 12:04:56Z

0

Yes as you answered the not exists clause will help instead of merge, plus modify the stored proc in a way that any given time only one condition will have access to insert the row.. This is a classy case of dead lock

answered Feb 19, 2019 at 12:04

Krunal Barot

9426 silver badges17 bronze badges

1 Comment

xeraphim Over a year ago

with the "not exists" clause the duplicate error still happened :(

EzLo · Accepted Answer · 2019-02-19 12:10:02Z

0

You need to use a SOURCE that doesn't have duplicates by your UNIQUE columns. You can use a ROW_NUMBER() with a PARTITION BY <unique column> and a posterior filter to remove potentially duplicates before the actual insert:

;WITH DuplicateRanking AS
(
    SELECT 
        articleId = articleId, 
        categoryId = @categoryId, 
        creator = @creator, 
        createDate = @now, 
        ruleId = @ruleId, 
        assignmentTypeId = 2 ,
        DuplicateRanking = ROW_NUMBER() OVER (
            PARTITION BY 
                articleId       -- Your unique columns here
            ORDER BY 
                (SELECT NULL))  -- Your desired order here (will determine which row gets inserted)
    FROM 
        @articleIdList  
)
MERGE article_category AS [target]
USING DuplicateRanking AS [source]
    ON (
        target.articleId = source.articleId AND 
        target.categoryId= source.categoryId
    )
WHEN NOT MATCHED AND [source].DuplicateRanking = 1 -- Only insert 1 row per unique column set 
    THEN
    INSERT (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    VALUES (source.articleId, source.categoryId, source.creator, source.createDate, source.ruleId, source.assignmentTypeId);

answered Feb 19, 2019 at 12:10

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

3 Comments

EzLo Over a year ago

@xeraphim it's not possible to insert duplicates with this code unless you aren't correctly declaring the PARTITION BY column/values or there's another clause of the merge that does an update. Is your unique constraint on columns articleId and categoryId?

xeraphim Over a year ago

yes it's on articleId and categoryId. Can the problem not be that this procedure is executed multiple times in milliseconds?

EzLo Over a year ago

The merge ensures that the comparison/join is done on the same transaction and scope as the operations it will do. If its failing due to other records then there must be records already inserted with these values even if this insert fails. Do you have other operations on the same transaction? Are you sure that this merge is causing the error?

Collectives™ on Stack Overflow

MSSQL duplicate key row exception

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related