5

Assume a table structure of MyTable(MyTableId NVARCHAR(MAX) PRIMARY KEY, NumberOfInserts INTEGER).

I often need to either update i.e. increment a counter of an existing record, or insert a new record if it doesn't exist with a value of 0 for NumberOfInserts.

Essentially:

IF (MyTableId exists)
    run UPDATE command
ELSE
    run INSERT command

My concern is losing data due to race conditions, etc.

What's the safest way to write this?

I need it to be 100% accurate if possible, and willing to sacrifice speed where necessary.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
WonderWorker
  • 8,539
  • 4
  • 63
  • 74
  • You could look into using the MERGE command, it combined update, insert and delete all at once. Couple of observations, please don't call that column 'key' it's a reserved word in SQL Server. Also, are you using that as your primary key? If so, NVARCHAR(MAX) is a really bad idea. – Rich Benner Jul 06 '16 at 09:02

1 Answers1

9

MERGE statement can perform both UPDATE and INSERT (and DELETE if needed).

Even though it is a single atomic statement, it is important to use HOLDLOCK query hint to prevent race condition. There is a blog post “UPSERT” Race Condition With MERGE by Dan Guzman where he explains in great details how it works and provides a test script to verify it.

The query itself is straight-forward:

DECLARE @NewKey NVARCHAR(MAX) = ...;

MERGE INTO dbo.MyTable WITH (HOLDLOCK) AS Dst
USING 
(
    SELECT @NewKey AS NewKey
) AS Src
ON Src.NewKey = Dst.[Key]
WHEN MATCHED THEN
UPDATE
SET NumberOfInserts = NumberOfInserts + 1
WHEN NOT MATCHED THEN
INSERT
(
    [Key]
    ,NumberOfInserts
)
VALUES
(
    @NewKey
    ,0
);

Of course, you can also use explicit two-step approach with a separate check if a row exists and separate UPDATE and INSERT statements. Just make sure to wrap them all in a transaction with appropriate table locking hints.

See Conditional INSERT/UPDATE Race Condition by Dan Guzman for details.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • 3
    Be careful using MERGE...there's a ton of issues with it: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Joe the Coder Aug 12 '17 at 19:57