0

I have a job that runs a stored procedure, that seems to intermittently fail with a duplicate key error when creating a unique clustered index on a temp table. How is it possible for me to get this error based on the code below.

It seems to happen when the source table is being updated. Transaction Isolation Level is set to read uncommitted, and there ARE some triggers on "SomeTableWithPrimaryKey". It doesn't happen every time, just three times in the last 100 runs. There are about 3 million rows in the source table "SomeTableWithPrimaryKey", and about 500k of them are being selected after the filters are applied.

Is this a SQL Server Bug? Some race condition?

This statement is based on a true query, the names have been changed to protect the innocent...and my job. :)

--on SQL Server 12.0.5589
SELECT  p.PrimaryKeyColumn, p.SomeOtherColumn
INTO    #Temp
FROM    dbo.SomeTableWithPrimaryKey p
WHERE   p.SomeCondition = 1
        AND (p.SomeCondition2 = 2   
            OR EXISTS(
                SELECT 1
                FROM    dbo.SomeTable st
                        INNER JOIN dbo.SomeOtherTable sot
                            ON st.Column1 = sot.Column1
                WHERE   st.SomeCondition1 = 1
                        AND st.PrimaryKeyColumn = p.PrimaryKeyColumn
                )
            )
;

--productid index
CREATE UNIQUE CLUSTERED INDEX CX_Temp ON #Temp(PrimaryKeyColumn)
;

^this is where we get the error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#Temp______...000000D5A436' and the index name 'CX_Temp'. The duplicate key value is (123456789).
[SQLSTATE 23000] (Error 1505) The statement has been terminated.
[SQLSTATE 01000] (Error 3621). The step failed.

  • 1
    Have you considered using SNAPSHOT isolation? It uses tempdb and row versioning, but your reads are guaranteed to not be blocked by writes. Check How to Choose Between RCSI and Snapshot Isolation Levels for a good discussion that is a clearer explanation of these isolation levels than anything else I have read (yet). – Laughing Vergil Nov 7 '18 at 18:02
  • I just realized the CI on the temp table didn’t include both columns. That puts it right back on the dirty reads and the order of operations that the application that is making updates to the table is doing. I’m deleting my answer as it doesn’t apply until the CI is extended to include the other column. – Aaron Nov 7 '18 at 19:04
1

When using the READ UNCOMMITTED isolation level, it is possible for SQL Server to read the same data twice - causing you to get duplicate results, even if there is a unique constraint ensuring there are no duplicates of that data in the table.

Looking at some of the comments on the question:

Thanks, the general consensus seems to be the iso level, but keep in mind, i've probably used that same basic query above in hundreds of places, and have never seen this behavior before

This won't happen all the time, and can depend a lot on whether the source table is being updated, the physical operations SQL Server chooses to use to read the data, and the nature of the query being run.

I will change the iso level and watch it (for possibly a year) to see if we get the same error in the future

That's great, changing the isolation level is your best option for preventing this problem!

When you have time, there is some great information out there on the issues around using the READ UNCOMMITTED isolation level:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.