Only inserting a row if it's not already there

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?

42262 次浏览
小开

I don't know if this is the "official" way, but you could try the INSERT, and fall back to UPDATE if it fails.

小开

I added HOLDLOCK which wasn't present originally. Please disregard the version without this hint.

As far as I'm concerned, this should be enough:

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

Also, if you actually want to update a row if it exists and insert if it doesn't, you might find this question useful.

小开

I've done a similar operation in past using a different method. First, I declare a variable to hold the primary key. Then I populate that variable with the output of a select statement which looks for a record with those values. Then I do and IF statement. If primary key is null, then do insert, else, return some error code.

     DECLARE @existing varchar(10)
SET @existing = (SELECT primaryKey FROM TABLE WHERE param1field = @param1 AND param2field = @param2)


IF @existing is not null
BEGIN
INSERT INTO Table(param1Field, param2Field) VALUES(param1, param2)
END
ELSE
Return 0
END
小开
最佳答案

What about the "JFDI" pattern?

BEGIN TRY
INSERT etc
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
RAISERROR etc
END CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

Read lesson 4:

Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…) line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.

小开

You could use MERGE:

MERGE INTO Target
USING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2)
ON Target.key = Source.key
WHEN MATCHED THEN
UPDATE SET value1 = Source.value1, value2 = Source.value2
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2)
小开

Firstly, huge shout out to our man @gbn for his contributions to the community. Can't even begin to explain how often I find myself following his advice.

Anyway, enough fanboy-ing.

To add slightly to his answer, perhaps "enhance" it. For those, like me, left feeling unsettled with what to do in the <> 2627 scenario (and no an empty CATCH is not an option). I found this little nugget from technet.

    BEGIN TRY
INSERT etc
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();


RAISERROR (
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END
END CATCH
小开

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/


提交答案