Nebenläufige Upserts in SQL Server ohne Existenz-Rennen

Ein Upsert scheint einfach: Existiert der fachliche Schlüssel, wird aktualisiert, andernfalls eingefügt. Zwei Sitzungen können jedoch gleichzeitig denselben fehlenden Schlüssel prüfen, beide eine Einfügung planen und anschließend einen Eindeutigkeitsfehler auslösen. Ohne passende Einschränkung können sogar beide erfolgreich sein und das Datenmodell verletzen.

Zuerst muss die Bedeutung einer Aktualisierung feststehen. Eine Spracheinstellung zu ersetzen ist etwas anderes als einen Kontostand zu erhöhen oder eine veraltete Bearbeitung zurückzuweisen. Der Upsert beschreibt eine fachliche Schreibregel. Erst danach lässt sich das passende Sperrverfahren auswählen.

Den fachlichen Schlüssel absichern

Diese Übungstabelle erlaubt genau eine Einstellung je Kunde und Einstellungsname. Erstellen Sie sie ausschließlich in einer entbehrlichen Übungsdatenbank.

-- Create only in a disposable practice database.
CREATE TABLE dbo.PreferenceDemo (
    CustomerId int NOT NULL,
    PreferenceKey nvarchar(50) NOT NULL,
    PreferenceValue nvarchar(200) NOT NULL,
    CONSTRAINT PK_PreferenceDemo PRIMARY KEY (CustomerId, PreferenceKey)
);

Der eindeutige Schlüssel bleibt die letzte Integritätsgrenze, selbst wenn alle Anwendungen dieselbe Prozedur verwenden sollen. Gehört zur Eindeutigkeit ein Mandant, muss auch TenantId enthalten sein. Bei Textschlüsseln bestimmen Normalisierung und Kollation, welche Schreibweisen als gleich gelten.

Ein getrenntes IF NOT EXISTS und INSERT ist unter gewöhnlichem Read Committed anfällig für dieses Rennen. Eine Transaktion allein schützt den fehlenden Schlüssel nicht zwingend. Geschützt werden muss der Bereich, in den eine neue Zeile eingefügt würde.

Der folgende Batch aktualisiert zuerst und hält den Schutz der Suche bis zum Transaktionsende.

DECLARE @CustomerId int = 42;
DECLARE @Key nvarchar(50) = N'language';
DECLARE @Value nvarchar(200) = N'en';
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'This batch owns its transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.PreferenceDemo WITH (UPDLOCK, HOLDLOCK)
    SET PreferenceValue = @Value
    WHERE CustomerId = @CustomerId AND PreferenceKey = @Key;

    IF @@ROWCOUNT = 0
        INSERT dbo.PreferenceDemo(CustomerId, PreferenceKey, PreferenceValue)
        VALUES (@CustomerId, @Key, @Value);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

HOLDLOCK bewirkt für diesen Tabellenzugriff serialisierbares Verhalten. UPDLOCK fordert für die Aktualisierung geeignete Sperren an. Der eindeutige Index ermöglicht einen gezielten Schutz des Schlüssels beziehungsweise Schlüsselbereichs. Das garantiert keine Sperrung ausschließlich einer einzigen physischen Zeile; Zugriffspfad und weitere Arbeiten beeinflussen den Umfang.

@@ROWCOUNT muss unmittelbar nach UPDATE geprüft werden. Eine dazwischengeschobene Protokollanweisung würde das Ergebnis verfälschen. Eine gefundene Zeile zählt auch dann als betroffen, wenn der neue Wert dem alten entspricht. Deshalb wird in diesem Fall keine zusätzliche Zeile eingefügt.

Zulässige Konflikte ausdrücklich definieren

Zwei gleichzeitige Zuweisungen verschiedener Sprachen werden nacheinander ausgeführt. Die letzte abgeschlossene serialisierte Zuweisung bestimmt den Wert. Das ist keine Garantie, dass die zuletzt beim Webserver eingegangene Anfrage gewinnt. Ebenso wenig erkennt das Verfahren das Überschreiben einer fremden Bearbeitung.

Sollen veraltete Änderungen abgelehnt werden, kann eine erwartete rowversion Teil des UPDATE-Prädikats sein. Kein Treffer muss dann als Konflikt untersucht werden. rowversion ist ein Änderungskennzeichen, keine Uhrzeit. Erstellen und bedingtes Ersetzen können dafür getrennte Schnittstellen benötigen.

Die erneute Zuweisung desselben Werts ist auf Datenebene meist wiederholbar. Ein erneutes Addieren von zehn ist es nicht. Auch Trigger, Auditzeilen oder externe Nachrichten können zusätzliche Wirkungen erzeugen. Bei einer fachlich genau einmal anzuwendenden Anfrage ist eine dauerhafte Anfragekennung sinnvoll.

MERGE ersetzt diese Überlegungen nicht. Eindeutigkeit, Isolation und Nebenläufigkeit bleiben auch bei einer einzigen Anweisung zu prüfen. Maßgeblich sind der konkrete Zugriff und die eingesetzte Engine-Version.

Mit konkurrierenden Verbindungen prüfen

Führen Sie den Batch aus zwei Verbindungen gegen dieselbe Übungstabelle aus, insbesondere für einen noch fehlenden Schlüssel. Für einen kontrollierten Versuch kann eine Sitzung nach UPDATE mit offener Transaktion kurz angehalten werden. Die andere sollte warten. Diese künstliche Pause gehört nicht in den Anwendungscode.

Prüfen Sie außerdem verschiedene Schlüssel, identische Wiederholungen, Einschränkungsfehler und Verbindungsabbrüche während COMMIT. Danach ist dessen Ergebnis für den Client möglicherweise unbekannt. Wiederholungen müssen über Anfragekennung oder maßgeblichen Datenzustand geklärt werden.

Bereichssperren können Konkurrenz erhöhen und schließen Deadlocks nicht aus. Bearbeiten Sie mehrere Schlüssel in einheitlicher Reihenfolge und halten Sie Transaktionen kurz. Wiederholen Sie nach einem Deadlock die vollständige Transaktion mit begrenzter Wartezeit, nicht nur INSERT innerhalb einer beschädigten Transaktion.

Technische Referenzen: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.