Ich teste einen Prozess, der viele, viele Datensätze gleichzeitig löscht. Es kann nicht TRUNCATE TABLE sein, weil dort Datensätze vorhanden sind, die bleiben müssen.
Wegen der Lautstärke habe ich das Löschen in eine Schleife ähnlich wie folgt aufgeteilt:
%Vor% MyTable ist eine gruppierte Tabelle. MyField befindet sich in der ersten Spalte in dem gruppierten Index. Es zeigt eine logische Gruppierung von Datensätzen an, so dass MyField = SomeValue oft viele Datensätze auswählt. Es ist mir egal, in welcher Reihenfolge sie gelöscht werden, solange eine Gruppe gleichzeitig bearbeitet wird. Es gibt keine anderen Indizes für diese Tabelle.
Ich habe den ROWLOCK -Hinweis hinzugefügt, um zu versuchen, Sperren-Eskalationen zu vermeiden, die wir in der Produktion gesehen haben. Ich habe den READPAST Hinweis hinzugefügt, um zu vermeiden, dass Datensätze gelöscht werden, die von anderen Prozessen gesperrt sind. Das sollte nie passieren, aber ich versuche, in Sicherheit zu sein.
Problem: Manchmal trifft diese Schleife ein Sperrzeitlimit 1222 "Zeitlimit für Sperranforderung überschritten", wenn es nur noch läuft.
Ich bin überzeugt, dass es während dieses Tests keine anderen Aktivitäten auf diesem System gibt, da es meine eigene Entwicklerbox ist, niemand anderes verbunden ist, keine anderen Prozesse darauf laufen und der Profiler keine Aktivität zeigt.
Ich kann das gleiche Skript eine Sekunde später erneut ausführen und es wird dort fortgesetzt, wo es aufgehört hat, und glücklich Datensätze gelöscht - bis zum nächsten Sperrzeitlimit.
Ich habe einen BEGIN TRY / BEGIN CATCH versucht, den Fehler 1222 zu ignorieren und das Löschen erneut zu versuchen, aber es schlägt sofort wieder mit demselben Sperrzeitfehler fehl. Es schlägt auch fehl, wenn ich eine kurze Verzögerung vor dem erneuten Versuch hinzufüge.
Ich gehe davon aus, dass die Sperrzeiten wegen einer Seitenaufteilung liegen, aber ich bin nicht sicher, warum das mit der aktuellen Schleifeniteration kollidieren würde. Die vorherige delete-Anweisung sollte bereits abgeschlossen sein, und ich dachte, das bedeute, dass alle Seitenaufteilungen ebenfalls beendet seien.
Warum trifft die DELETE-Schleife ein Sperrzeitlimit für sich selbst?
Gibt es eine Möglichkeit, wie der Prozess dieses Sperrzeitlimit vermeiden oder erkennen kann, dass die Fortsetzung sicher ist?
Dies ist auf SQL Server 2005.
- BEARBEITEN -
Ich habe dem Profiler das Lock: Timeout-Ereignis hinzugefügt. Beim Löschen wird ein PAGELOCK ausgegeben:
%Vor%DBCC PAGE meldet, dass diese Seiten außerhalb des Bereichs der Master-Datenbank (ID 1) liegen.
- EDIT 2 -
Ich habe eine BEGIN TRY / BEGIN CATCH hinzugefügt und eine exec sp_lock im catch-Block ausgeführt. Hier ist, was ich gesehen habe:
SPID 19 ist ein SQL Server TASK MANAGER. Warum sollte einer dieser Task-Manager Sperren für MyTable erwerben?
Ich habe die Antwort gefunden: Meine geloopte Löschung steht im Konflikt mit der Geisterbereinigung proc.
Unter Verwendung von Nicholas 'Vorschlag habe ich eine BEGIN TRANSACTION und eine COMMIT hinzugefügt. Ich habe die Löschschleife in einem BEGIN TRY / BEGIN CATCH eingepackt. In der BEGIN CATCH , direkt vor einer ROLLBACK , habe ich sp_lock und sp_who2 ausgeführt. (Ich habe die Codeänderungen in der obigen Frage hinzugefügt.)
Als mein Prozess blockiert wurde, sah ich folgende Ausgabe:
%Vor%Wenn SQL Server später Datensätze löscht, wird ein Bit darauf gesetzt, um sie als "Ghost-Datensätze" zu markieren. Alle paar Minuten wird ein interner Prozess namens Geisterbereinigung ausgeführt, um Seiten mit vollständig gelöschten Datensätzen wiederherzustellen (d. H. Alle Datensätze sind Ghost-Datensätze).
Der Geisterbereinigungsprozess wurde in dieser Frage auf ServerFault diskutiert.
Hier ist Paul S. Randals Erklärung des Geisterbereinigungsprozesses.
Es ist möglich, den Geisterbereinigungsprozess mit einem Ablaufverfolgungsflag zu deaktivieren. Aber ich hatte es nicht um dies in diesem Fall zu tun.
Ich habe ein Lock-Timeout von 100 ms hinzugefügt. Dies führt zu gelegentlichen Lock-Timeouts im Ghost-Record-Bereinigungsprozess, aber das ist akzeptabel. Ich fügte auch eine eigene Schleife hinzu, die Sperrzeitüberschreitungen bis zu 5 Mal wiederholt. Mit diesen beiden Änderungen ist mein Prozess nun in der Regel abgeschlossen. Jetzt wird nur eine Zeitüberschreitung angezeigt, wenn es einen sehr langen Prozess gibt, der viele Daten umschreibt, die Tabellen- oder Seitensperren für die Daten erfordern, die mein Prozess aufräumen muss.
EDIT 2016-07-20
Der endgültige Code sieht so aus:
%Vor%Sie oder eine andere Person, die die Verbindung verwendet, legt das Sperrzeitlimit auf einen anderen Wert als den Standardwert fest. Einzelheiten finden Sie Ссылка .
Die Standardsperrzeit beträgt -1 Millisekunden und bedeutet "Immer warten".
Die Zeilenhinweise sind nett, aber sie sind ein Code-Geruch und sollten vermieden werden. Lassen Sie SQL Server seine Aufgabe erledigen. Es hat mehr Informationen als Sie über das System als Ganzes.
Zunächst können Sie die Sperrgröße nicht steuern: Die Sperreskalation erfolgt automatisch basierend auf der Anzahl der ausstehenden Sperren. Es beginnt mit Zeilensperren. Wenn Sie zu viele Zeilensperren ansammeln, eskaliert SQL Server zur Seitensperre. Erwerben Sie zu viele Seitensperren und eskaliert zu Tabellensperren. Siehe Ссылка für Details zur Sperreskalation. Es gibt ein paar Ablaufverfolgungsflags, die Sie jedoch festlegen können. Dadurch wird eine Sperrenausweitung verhindert. Dies beeinträchtigt jedoch die Leistung des SQL Servers.
Eine andere Sache: Sie sollten die DELETE -Anweisung in eine Transaktion einfügen, insbesondere in eine gespeicherte Prozedur.
Dies macht Ihre Absicht klar und stellt sicher, dass Schlösser freigegeben werden, wenn sie sein sollten.
Tags und Links sql sql-server sql-server-2005 locking