Ich muss einige Datensätze in eine Tabelle in einer Legacy-Datenbank einfügen, und da es von anderen alten Systemen verwendet wird, ist das Ändern der Tabelle keine Lösung.
Das Problem besteht darin, dass die Zieltabelle einen int-Primärschlüssel aber keine Identitätsspezifikation hat. Also muss ich die nächste verfügbare ID finden und diese verwenden:
%Vor%Ich möchte jedoch verhindern, dass andere Anwendungen in die Tabelle eingefügt werden, so dass wir keine Probleme haben. Ich habe es versucht:
%Vor%in zwei verschiedenen Fenstern in SQL Management Studio und die eine Transaktion wird immer als Deadlock-Opfer getötet.
Ich habe auch SET TRANSACTION ISOLATION LEVEL SERIALIZABLE zuerst mit dem gleichen Ergebnis versucht ...
Irgendwelche guten Vorschläge, wie ich sicherstellen kann, dass ich die nächste ID bekomme und diese benutze, ohne zu riskieren, dass jemand anderes (oder ich!) abgespritzt wird?
Tut mir leid, dass ich das nicht früher erwähnt habe, aber dies ist ein SQL 2000 Server, daher kann ich keine Dinge wie FOR UPDATE und OUTPUT
verwendenDer WAITFOR ist so, dass ich mehrere Verbindungen haben kann und die Abfrage mehrere Male starten kann, um Parallelität zu provozieren.
Danke an Quassnoi für die Antwort und an alle anderen Leute, die dazu beigetragen haben! Super!
Erstellen Sie eine andere Tabelle:
%Vor% mit einer einzelnen Zeile, sperren Sie diese Zeile, und erhöhen Sie value jedes Mal um eins, wenn Sie eine IDENTITY benötigen.
Um den neuen Wert in einer einzelnen Anweisung zu sperren, zu erhöhen und zurückzugeben, verwenden Sie:
%Vor%Wenn Sie nicht aktualisieren möchten, sperren Sie einfach und geben dann Folgendes aus:
%Vor%Dies wird die Tabelle bis zum Ende der Transaktion sperren.
Wenn Sie immer t_identity sperren, bevor Sie mit ancient_table rumspielen, werden Sie niemals einen Deadlock erhalten.
Fügen Sie eine weitere Tabelle mit einer Identitätsspalte hinzu und verwenden Sie diese neue Tabelle und Spalte, um Ihre Identitätswerte für die alte Tabelle auszuwählen / zu generieren.
Aktualisieren : Abhängig von der Häufigkeit der INSERTS (und der Anzahl der vorhandenen Zeilen e ) könnten Sie Ihre neuen IDENTITY-Werte auf < stark> e + x wobei x ausreichend groß ist. Dies würde einen Konflikt mit den Legacy-Einfügungen vermeiden. Eine traurige Lösung, eine unvollkommene, aber etwas zum Nachdenken?
BEARBEITEN Dies ist im Grunde die von @Quassnoi beabsichtigte Methode, ich implementiere es einfach in einer Schleife, so dass Sie es gegen mehrere Fenster gleichzeitig ausführen können, um zu sehen, dass es großartig funktioniert.
eingerichtet:
Erstellen Sie die vorhandene Tabelle des Benutzers:
%Vor%Erstellen Sie eine neue Tabelle, um die fehlende Identität zu verfolgen, Sie könnten eine zusätzliche Spalte hinzufügen, um eine Tabelle zu verfolgen, wenn dies für mehrere Tabellen erforderlich ist, aber ich habe das in diesem Beispiel nicht getan:
%Vor%Erstellen Sie ein Testskript, fügen Sie dieses in mehrere Fenster ein und führen Sie es gleichzeitig aus:
%Vor% -------------------------------------------- -------------------
BEARBEITEN Hier ist mein ursprünglicher Versuch, der in einigen Fällen zu Deadlocks führen wird Schleife und in mehreren Fenstern gleichzeitig. Die obige Methode funktioniert immer. Ich habe alle Kombinationen von Transaktionen mit (holdlock) versucht, und die Transaktionsisolationsstufe serialisierbar usw. gesetzt, aber ich konnte es nicht so gut wie die obige Methode ausführen lassen.
eingerichtet:
%Vor%verwendet, um die ID zu erfassen:
%Vor%die Einfügung:
%Vor%listet die neue ID auf:
%Vor%listet alle IDs auf:
%Vor%Sie sollten hier keinen Deadlock bekommen, da der zweite nur auf den ersten warten sollte. Ihr Problem besteht darin, dass Sie eine Transaktion erstellen und dann während dieser Transaktion eine weitere Sperre hinzufügen.
Außerdem erhalten Sie die ID, die Sie dann in zwei separaten Anweisungen verwenden, während Sie alles in einer einzigen Lösung durchführen könnten:
%Vor%Dies sollte sicherstellen, dass Sie nur Konsistenz für Ihre Einsätze haben. Wenn Sie angeben, dass Legacy-Anwendungen diese Tabelle ebenfalls verwenden, können Sie jedoch sicher sein, dass beim Einfügen neuer Datensätze kein Konflikt auftritt?
Tags und Links sql sql-server deadlock transactions locking