ADR 12: Upsert pattern

Status

Proposed

Context

The standard IF EXISTS pattern requires two locks and allows for a deadlock. This can be improved.

Decision

From the article:

ditch the redundant check, wrap the sequence in a transaction, and protect the first table access with appropriate locking

BEGIN TRANSACTION;

UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END

Why do we need two hints? Isn't UPDLOCK enough?

UPDLOCK is used to protect against conversion deadlocks at the statement level (let another session wait instead of encouraging a victim to retry). SERIALIZABLE is used to protect against changes to the underlying data throughout the transaction (ensure a row that doesn't exist continues to not exist). It's a little more code, but it's 1000% safer, and even in the worst case (the row does not already exist), it performs the same as the anti-pattern. In the best case, if you are updating a row that already exists, it will be more efficient to only locate that row once.

Multiple row example, also from article:

CREATE PROCEDURE dbo.UpsertTheThings
    @tvp dbo.TableType READONLY
AS
BEGIN
  SET NOCOUNT ON;

  BEGIN TRANSACTION;

  UPDATE t WITH (UPDLOCK, SERIALIZABLE)
    SET val = tvp.val
  FROM dbo.t AS t
  INNER JOIN @tvp AS tvp
    ON t.[key] = tvp.[key];

  INSERT dbo.t([key], val)
    SELECT [key], val FROM @tvp AS tvp
    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);

  COMMIT TRANSACTION;
END

Consequences

Multiple locks are not needed, and in the update scenario, a separate lookup is skipped.