Geek asks and answers

INSERT INTO .. ​​SELECT .. unique violation of restrictions

I run a stored procedure that selects the values ​​of my temporary table and inserts them into the database as follows:

 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   )

In rare cases (~ once every couple of hours on a server that processes thousands of requests per minute), I get a unique restriction error "Violation of the UNIQUE KEY restriction" ... in the index in the EmailAddress column.

I can confirm that I do not pass duplicate values. Even if I were, it should be caught by DISTINCT.

-SQL Server 2008 -Installed proc + not using transactions + CallDeskatement JDBC

Could it happen that between SELECT and subsequent INSERT there was another call to the same / different stored proc that terminated INSERT with similar data? If so, what would be the best way to prevent this?

Some ideas. We have many duplicate instances of "clients" that immediately interact with this SQL Server, so my first reaction was a concurrency problem, but I cannot reproduce it myself. This is the best I have, but so far it has not disappeared. This does not occur in our intermediate environment, where the load is low compared to the production environment. That is why I began to study the problems of concurrency.

+5
source share
1 answer

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

SQL , MERGE. (!), with (holdlock), merge .

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

merge , , " " "".

merge, . , . mutex .

+5

More articles:


All Articles