INSERT INTO .. SELECT .. unique constraint violation

Question

I'm running a stored procedure that selects values my temp table and inserts them into the database like so:

 INSERT INTO emails (EmailAddress)   (
     SELECT
       DISTINCT eit.EmailAddress
     FROM #EmailInfoTemp eit
       LEFT JOIN emails ea
         ON eit.EmailAddress = ea.EmailAddress
     WHERE ea.EmailAddressID IS NULL   )

On rare cases(~ once every couple of hours on a server that handles thousands of requests a minute), I then receive a unique constraint error "Violation of UNIQUE KEY constraint�".. on an index on the EmailAddress column.

I can confirm that I am not passing in duplicate values. Even if I was, it should be caught by the DISTINCT.

-SQL Server 2008 -Stored proc + not using transactions + JDBC callablestatement

Could it happen that between the SELECT and the ensuing INSERT, there was another call to the same/different stored proc that completed an INSERT with similiar data? If so, what would be the best way to prevent that?

Some ideas: We have many duplicate instances of "clients" who communicate with this one SQL Server at once in production, so my first reaction was a concurrency issue, but I can't seem to replicate it myself. That's the best guess I had, but it's gone nowhere so far. This does not happen on our staging environment where the load is insignificant compared to the production environment. That was the main reason I started looking into concurrency issues.

Answer 1

The error is probably caused by two sessions executing an insert at the same time.

You can make your SQL code safer by using MERGE. As Aaron Bertrand's comment says (thanks!), you have to include a with (holdlock) hint to make merge really safe.

; merge emails e with (holdlock)
using   #EmailInfoTemp eit
on      e.EmailAddress = eit.EmailAddress
when    not matched then insert
        (EmailAddress) values (eit.EmailAddress)

The merge statement will take appropriate locks to ensure that no other session can sneak in between it's "not matched" check and the "insert".

If you can't use merge, you could solve the problem client-side. Make sure that no two inserts are running at the same time. This is typically easy to do with a mutex or other synchronization construct.