SQL Server – How to Avoid Duplicate Key Error

concurrencysql serversql server 2014

This statement:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

…will fail with a primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several threads at the same time) with the default transaction isolation level of READ COMMITTED:

Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.

What is the best way to prevent that?

The table looks something like this:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Update

From comments:

Why do you have multiple sessions, which can obviously pull the same id from somewhere, using the same permanent table without having some kind of session key to tell them apart? Even without a duplicate error, how are you going to know which rows belong to which session?

This is a stored procedure that is called by an external service that populates data in this table. The service generates the record id and it provides no guarantee that it won't send the same data twice. Or send the same data at the same time.

The database code is supposed to discard all the records with the same id, if one already exists. The service never sends two records with the same id in the same batch.

Best Answer

If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key.

This will just give a warning instead of an error when an insert would result in a duplicate key violation. But instead of failing it will discard the row(s) that if inserted would cause the uniqueness violation.

CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] 
PRIMARY KEY ([id] ASC) 
WITH (IGNORE_DUP_KEY = ON));

Example on sqlfiddle

Paul White's detailed explanation on the IGNORE_DUP_KEY option. Thanks Paul.

Related Question