Only inserting a row if it's not already there

user_411051 0
Viewed 41676

I had always used something similar to the following to achieve it:

INSERT INTO TheTable
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WHERE
        PrimaryKey = @primaryKey)

...but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic?

The problem is that this is almost impossible to recreate at will.

Perhaps I could change it to the something like the following:

INSERT INTO TheTable
WITH
    (HOLDLOCK,
    UPDLOCK,
    ROWLOCK)
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WITH
        (HOLDLOCK,
        UPDLOCK,
        ROWLOCK)
    WHERE
        PrimaryKey = @primaryKey)

Although, maybe I'm using the wrong locks or using too much locking or something.

I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.

Does anyone have any ideas?

concurrency locking tsql sql-server sql
7 Answers

In addition to the accepted answer JFDI pattern, you probably want to ignore 2601 errors too (in addition to 2627) which is "Violation of unique index".

...
IF ERROR_NUMBER() NOT IN (2601, 2627) THROW
...

P.S. And if you're already using C# and .NET here's how you can neatly handle this without complicated SQL code using a simple C# 6.0 when statement:

try
{
    connection.Execute("INSERT INTO etc");
}
catch (SqlException ex) when (ex.Number == 2601 || ex.Number == 2627)
{
    //ignore "dup key" errors
}

By the way, here's a good read on the subject: https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

user_566210
Related