Encountering 'Cannot Insert Duplicate Key in Object' Error Due to Parallel Query Execution

Nechba 20

The issue at hand involves encountering a "Cannot insert duplicate key in object" error during parallel execution of queries by multiple threads. The error indicates that duplicate keys are being attempted to be inserted into the database object, despite the absence of duplicate data. This problem arises due to concurrent execution of the same query by multiple threads simultaneously. It is suspected that the parallelism inherent in this scenario is causing contention, leading to the duplication of keys. However, the proposed solution of setting MAXDop = 1 would adversely impact performance, which is undesirable. Therefore, an alternative solution is sought to resolve the issue without sacrificing performance.

Olaf Helper 45,291 Reputation points

2024-03-27T08:13:17.5733333+00:00

Again?

https://learn.microsoft.com/en-us/answers/questions/1623784/would-it-be-possible-to-resolve-the-error-cannot-i
Nechba 20 Reputation points

2024-03-27T08:30:55.8+00:00

Hi Olfa

As I mentioned in the description, I need a solution that doesn't impact performance. So, if you have a proposed solution, it will be appreciated.

Thanks,
Nechba
Erland Sommarskog 113.3K Reputation points MVP

2024-03-27T22:15:30.12+00:00

The query you seem to have depends on undefined behaviour.

Tells us about the actual problem you are trying to solve, and we may be able to find a better solution.
Nechba 20 Reputation points

2024-04-03T12:11:47.33+00:00

Hi Erland

This is the query that is causing the problem. (Just for information, the problem occurs randomly.

DECLARE @VALINDEX INT; SET @VALINDEX = 0; UPDATE WORKTABLE_xxxxxx SET VALINDEX = @VALINDEX, @VALINDEX = @VALINDEX + 1 WHERE ISNULL(VALINDEX, 0) <> '-1';
Erland Sommarskog 113.3K Reputation points MVP

2024-04-03T21:09:07.1466667+00:00

And as I said, it seems to rely on undefined behaviour.

And as I said, tells us about the actual problem you are trying to solve, and we may be able to find a better solution. What you have now might work with MAXDOP 1 - but it is still by chance, so it is not a tenable solution.

1 answer

博雄胡 505 Reputation points

2024-03-27T08:23:41.38+00:00

It has nothing to do with concurrency.

It is due to the occurrence of duplicate values in subsequent insertions triggered by the trigger, rather than the occurrence of duplicate values in the insertions to WorkTABLE_xxxxxx.
Please sign in to rate this answer.
Nechba 20 Reputation points

2024-03-27T10:59:14.87+00:00

This is the query that is causing the problem:
DECLARE @VALINDEX INT; SET @VALINDEX = 0; UPDATE WORKTABLE_xxxxxx SET VALINDEX = @VALINDEX, @VALINDEX = @VALINDEX + 1 WHERE ISNULL(VALINDEX, 0) <> '-1';

The work table is a temporary table, so there is no data in this table initially. Additionally, there is no duplicate data present. The duplication occurs only when this query runs simultaneously with different threads. Therefore, I need to find a solution to this problem.

Bruce (SqlWork.com) 68,081 Reputation points

2024-03-27T15:30:26.1366667+00:00

this query fails if called twice, much less concurrently. just replace this buggy code with an identity column.

Nechba 20 Reputation points

2024-04-03T12:15:07.34+00:00

In this query, #WORKTABLE_xxxxxx refers to a temporary table where no data is stored.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Encountering 'Cannot Insert Duplicate Key in Object' Error Due to Parallel Query Execution

1 answer

Your answer