INSERT/UPDATE/DELETE OUTPUT, UPSERT Patterns, MERGE Risk Awareness, and Concurrency Safety

Learning outcomes

An integration worker receives external ticket state repeatedly. The naïve implementation first checks whether an external key exists, then inserts if absent or updates if present. Two workers can both observe “absent” and race to insert the same key. Another service interprets rows returned by OUTPUT as proof that the surrounding transaction committed. A third team reaches for MERGE because “one statement must be safer.” This lesson separates changed-row capture, uniqueness, transaction isolation, locking, retry semantics, and statement choice.

1. Make the idempotency key a database invariant

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab06') IS NULL EXEC(N'CREATE SCHEMA lab06 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab06.ExternalTicket;GOCREATE TABLE lab06.ExternalTicket(  ticket_id bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_lab06_ExternalTicket PRIMARY KEY,  external_key varchar(40) NOT NULL,  status varchar(20) NOT NULL,  payload_version int NOT NULL,  updated_at datetime2(3) NOT NULL CONSTRAINT DF_lab06_ExternalTicket_Updated DEFAULT SYSUTCDATETIME(),  CONSTRAINT UQ_lab06_ExternalTicket_ExternalKey UNIQUE(external_key));GO

The unique constraint is not merely a performance aid; it is the final integrity barrier against duplicate external keys. Application code can coordinate, but the database must still reject a duplicate state if concurrency reaches it.

2. OUTPUT exposes affected rows, not final transaction success

OUTPUT can expose columns from inserted and deleted for INSERT, UPDATE, DELETE, and MERGE. It is useful for generated identifiers, before/after values, integration staging, or audit pipelines. Microsoft warns that a DML statement with OUTPUT can return rows to the client even when the statement later encounters an error and rolls back; therefore a caller must not treat receipt of OUTPUT rows as a commit acknowledgement.

USE ServiceHubLab;GODECLARE @changes table(  action_name varchar(10), ticket_id bigint,  old_status varchar(20) NULL, new_status varchar(20) NULL);INSERT lab06.ExternalTicket(external_key,status,payload_version)OUTPUT 'INSERT', inserted.ticket_id, NULL, inserted.statusINTO @changes(action_name,ticket_id,old_status,new_status)VALUES ('crm-1001','open',1);UPDATE lab06.ExternalTicketSET status='assigned', payload_version=2, updated_at=SYSUTCDATETIME()OUTPUT 'UPDATE', inserted.ticket_id, deleted.status, inserted.statusINTO @changes(action_name,ticket_id,old_status,new_status)WHERE external_key='crm-1001';SELECT * FROM @changes ORDER BY ticket_id, action_name;GO

Within a successful batch, the table variable gives a convenient changed-row stream. In an application transaction, publish external side effects only after the database commit is known to have succeeded; otherwise you can emit a message for work that the database rolled back.

3. Why IF NOT EXISTS then INSERT is a race

-- Do not use this as the concurrency contract.IF NOT EXISTS   (SELECT 1 FROM lab06.ExternalTicket WHERE external_key=@external_key)BEGIN  INSERT lab06.ExternalTicket(external_key,status,payload_version)  VALUES(@external_key,@status,@version);ENDELSEBEGIN  UPDATE lab06.ExternalTicket  SET status=@status,payload_version=@version,updated_at=SYSUTCDATETIME()  WHERE external_key=@external_key;END

Two sessions can evaluate the existence check before either inserts. The unique constraint then protects correctness by allowing at most one insert, but one caller receives a duplicate-key error. That may be an acceptable optimistic strategy if the application intentionally catches 2601/2627 and retries/updates. It is not acceptable to assume the race cannot occur.

4. One explicit serialized update/insert pattern

When the business requirement is “for this external key, update if present; otherwise insert, and serialize competing writers for that key,” one defensible pattern uses a short transaction plus serializable range protection on an indexed key. The exact lock behavior depends on the access path, so the unique index is part of the design. Keep the transaction small and be prepared to retry deadlock victims or transient failures.

USE ServiceHubLab;GODECLARE @external_key varchar(40)='crm-2001',        @status varchar(20)='open',        @version int=1;SET XACT_ABORT ON;BEGIN TRY  BEGIN TRAN;  UPDATE t WITH (UPDLOCK, SERIALIZABLE)  SET status=@status,      payload_version=@version,      updated_at=SYSUTCDATETIME()  OUTPUT inserted.ticket_id, deleted.status AS old_status, inserted.status AS new_status  FROM lab06.ExternalTicket AS t  WHERE t.external_key=@external_key;  IF @@ROWCOUNT=0  BEGIN    INSERT lab06.ExternalTicket(external_key,status,payload_version)    OUTPUT inserted.ticket_id, NULL AS old_status, inserted.status AS new_status    VALUES(@external_key,@status,@version);  END  COMMIT;END TRYBEGIN CATCH  IF XACT_STATE()<>0 ROLLBACK;  THROW;END CATCH;GO

SERIALIZABLE/HOLDLOCK-style range protection reduces concurrency because it prevents a competing transaction from changing the relevant key range until the transaction ends. That cost is precisely why this is not a universal “best” UPSERT. Another valid design is insert-first with duplicate-key handling. Choose based on contention, latency, idempotency requirements, and measured behavior.

5. MERGE is a tool with explicit concurrency caveats

MERGE can express matched and unmatched actions in one statement, which is useful for some synchronization/ETL workloads. Current Microsoft documentation also states that at scale MERGE can introduce complicated concurrency issues, that separate INSERT/UPDATE/DELETE logic can perform better with less blocking in heavy-concurrency scenarios, and that HOLDLOCK can be necessary in some unique-key insert/update cases. Therefore the course neither bans MERGE nor recommends it by default.

USE ServiceHubLab;GODECLARE @source table(external_key varchar(40),status varchar(20),payload_version int);INSERT @source VALUES('crm-3001','open',1),('crm-1001','closed',3);MERGE lab06.ExternalTicket WITH (HOLDLOCK) AS tgtUSING @source AS srcON tgt.external_key=src.external_keyWHEN MATCHED THEN  UPDATE SET status=src.status,payload_version=src.payload_version,updated_at=SYSUTCDATETIME()WHEN NOT MATCHED BY TARGET THEN  INSERT(external_key,status,payload_version)  VALUES(src.external_key,src.status,src.payload_version)OUTPUT $action, inserted.ticket_id, inserted.external_key;GO

Before production use, test the exact SQL Server build, indexes, triggers, target table features, source duplicates, concurrency, and failure/retry behavior. A syntactically compact statement is not automatically easier to operate.

6. Two-session verification and rollback

To reproduce contention locally, open two SSMS/VS Code/sqlcmd sessions. In both, run the transactional pattern for the same new external_key, pausing one transaction before COMMIT if you want to observe the other wait. Query sys.dm_tran_locks and sys.dm_exec_requests from a third session if your permissions permit. The expected invariant is one target row for the key; the second writer should wait, update, or fail/retry according to the chosen pattern—not silently create a duplicate.

USE ServiceHubLab;GOSELECT external_key, COUNT(*) AS copies, MAX(payload_version) AS latest_versionFROM lab06.ExternalTicketGROUP BY external_keyORDER BY external_key;GO-- Optional cleanup after the lesson:-- DROP TABLE IF EXISTS lab06.ExternalTicket;

Production monitoring should include unique-key violations, deadlocks, lock waits, transaction duration, retry rates, duplicate integration messages, and unknown commit outcomes after network failure. Application retries must be bounded and idempotent; blindly retrying a non-idempotent operation can repeat effects.

Authoritative references

• OUTPUT clause — changed-row capture and rollback caveat
• MERGE — syntax, index guidance, and current concurrency considerations
• Table hints — UPDLOCK/HOLDLOCK/SERIALIZABLE hint semantics
• Transaction isolation levels — serializable behavior and concurrency tradeoffs
• SQL Server 2025 build versions — servicing baseline