Lösungen für INSERT OR UPDATE auf SQL Server

Question

Nehmen wir eine Tabellenstruktur von MyTable(KEY, datafield1, datafield2...) .

Häufig möchte ich entweder einen vorhandenen Datensatz aktualisieren oder einen neuen Datensatz einfügen, wenn dieser noch nicht existiert.

Im Wesentlichen:

IF (key exists)
  run update command
ELSE
  run insert command

Wie kann man das am besten schreiben?

Answer 1

Wenn Sie den Weg über UPDATE if-no-rows-updated then INSERT gehen, sollten Sie erwägen, zuerst INSERT zu machen, um eine Race Condition zu verhindern (vorausgesetzt, es liegt kein DELETE dazwischen)

INSERT INTO MyTable (Key, FieldA)
   SELECT @Key, @FieldA
   WHERE NOT EXISTS
   (
       SELECT *
       FROM  MyTable
       WHERE Key = @Key
   )
IF @@ROWCOUNT = 0
BEGIN
   UPDATE MyTable
   SET FieldA=@FieldA
   WHERE Key=@Key
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

Abgesehen von der Vermeidung einer Wettlaufbedingung, wenn der Datensatz in den meisten Fällen bereits existiert, führt dies dazu, dass das INSERT fehlschlägt und CPU verschwendet wird.

Die Verwendung von MERGE ist wahrscheinlich für SQL2008 und höher vorzuziehen.

Answer 2

MS SQL Server 2008 führt die MERGE-Anweisung ein, die meiner Meinung nach Teil des SQL:2003-Standards ist. Wie viele gezeigt haben, ist es keine große Sache, Fälle mit nur einer Zeile zu behandeln, aber wenn man mit großen Datenmengen zu tun hat, braucht man einen Cursor, mit all den damit verbundenen Leistungsproblemen. Die MERGE-Anweisung ist eine willkommene Ergänzung, wenn man mit großen Datenmengen arbeitet.

Answer 3

Bevor jetzt jeder aus Angst vor diesen bösartigen Benutzern, die Ihre Sprocs direkt ausführen, zu HOLDLOCK-s übergeht, möchte ich darauf hinweisen, dass Sie müssen die Einzigartigkeit der neuen PK-s durch Design garantieren (Identitätsschlüssel, Sequenzgeneratoren in Oracle, eindeutige Indizes für externe IDs, durch Indizes abgedeckte Abfragen). Das ist das A und O des Problems. Wenn Sie das nicht haben, werden Sie von keinem HOLDLOCK des Universums gerettet, und wenn Sie das haben, brauchen Sie nichts anderes als UPDLOCK beim ersten Select (oder Update first) zu verwenden.

Sprocs laufen normalerweise unter sehr kontrollierten Bedingungen und unter der Annahme eines vertrauenswürdigen Anrufers (mittlere Ebene). Das heißt, wenn ein einfaches Upsert-Muster (Update+Insert oder Merge) jemals doppelte PK sieht, bedeutet das einen Fehler in Ihrem Mid-Tier- oder Tabellendesign und es ist gut, dass SQL in einem solchen Fall einen Fehler meldet und den Datensatz zurückweist. Das Setzen eines HOLDLOCKs in diesem Fall ist gleichbedeutend mit dem Verzehr von Ausnahmen und der Aufnahme potenziell fehlerhafter Daten, abgesehen von der Verringerung Ihrer Leistung.

Allerdings ist die Verwendung von MERGE oder UPDATE und dann INSERT einfacher für Ihren Server und weniger fehleranfällig, da Sie nicht daran denken müssen, (UPDLOCK) zum ersten Select hinzuzufügen. Wenn Sie außerdem Einfügungen/Aktualisierungen in kleinen Stapeln vornehmen, müssen Sie Ihre Daten kennen, um entscheiden zu können, ob eine Transaktion sinnvoll ist oder nicht. Wenn es sich nur um eine Sammlung von nicht zusammenhängenden Datensätzen handelt, ist eine zusätzliche "umhüllende" Transaktion nachteilig.

Answer 4

Sind die Race Conditions wirklich von Bedeutung, wenn Sie zuerst eine Aktualisierung und dann eine Einfügung versuchen? Angenommen, Sie haben zwei Threads, die einen Wert für einen Schlüssel setzen wollen Schlüssel :

Thema 1: Wert = 1
Thema 2: Wert = 2

Beispiel für ein Race-Condition-Szenario

1. Schlüssel ist nicht definiert
2. Thread 1 schlägt beim Update fehl
3. Thread 2 schlägt beim Update fehl
4. Genau einer von Faden 1 oder Faden 2 hat Erfolg beim Einfügen. Z.B. Thread 1

5. Der andere Thread scheitert beim Einfügen (mit dem Fehler Duplikatschlüssel) - Thread 2.

   • Ergebnis: Die "erste" der beiden einzufügenden Stufen, entscheidet über den Wert.
   • Gesuchtes Ergebnis: Der letzte der 2 Threads, der Daten schreibt (Update oder Insert), soll den Wert bestimmen

In einer Multithreading-Umgebung entscheidet jedoch der Scheduler des Betriebssystems über die Reihenfolge der Thread-Ausführung - in dem obigen Szenario, in dem wir diese Wettlaufbedingung haben, war es das Betriebssystem, das über die Reihenfolge der Ausführung entschieden hat. D.h.: Es ist falsch zu sagen, dass "Thread 1" oder "Thread 2" aus Sicht des Systems "zuerst" ausgeführt wurde.

Wenn der Ausführungszeitpunkt für Thread 1 und Thread 2 so nah beieinander liegt, spielt das Ergebnis der Race Condition keine Rolle. Die einzige Anforderung sollte sein, dass einer der Threads den resultierenden Wert definiert.

Für die Umsetzung: Wenn eine Aktualisierung gefolgt von einer Einfügung den Fehler "doppelter Schlüssel" ergibt, sollte dies als Erfolg behandelt werden.

Außerdem sollte man natürlich nie davon ausgehen, dass der Wert in der Datenbank derselbe ist wie der, den man zuletzt geschrieben hat.

Answer 5

Sie können diese Abfrage verwenden. Sie funktioniert in allen SQL Server-Editionen. Sie ist einfach und übersichtlich. Aber Sie müssen 2 Abfragen verwenden. Sie können verwenden, wenn Sie MERGE nicht verwenden können

    BEGIN TRAN

    UPDATE table
    SET Id = @ID, Description = @Description
    WHERE Id = @Id

    INSERT INTO table(Id, Description)
    SELECT @Id, @Description
    WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)

    COMMIT TRAN

HINWEIS: Bitte erläutern Sie negative Antworten