SQL Server-Sperreskalationsproblem

  

Könnte jemand auf einfache Weise erklären, warum Lock Escalation benötigt wird und wie hoch der sogenannte Lockoverhead ist?

Wenn Sie eine Tabelle aktualisieren und eine Zeile sperren, müssen Sie diese Tatsache irgendwie aufzeichnen: das ist eine Zeile, sie wurde aktualisiert und gesperrt.

Wenn Sie Millionen Zeilen aktualisieren, müssen Sie dies millionenfach tun und haben daher etwas Platz, um Millionen Sperren zu halten.

SQL Server führt eine Liste der Sperren im Speicher, während Oracle in den Tabellenbereichen ausgeführt wird.

Dies liegt wahrscheinlich daran, dass Oracle alt ist (älter als ich) und SQL Server im Vergleich zu Oracle jung ist.

Temporäre Ressourcen (wie Locks) in einem permanenten Speicher zu halten, ist aus Sicht des Designers keine so offensichtliche Lösung. Nur eine Sache zu erwähnen: Sie müssen möglicherweise eine Festplatte schreiben, um eine SELECT FOR UPDATE durchzuführen.

Die Kernfunktionen von Oracle wurden Anfang der 80er Jahre entwickelt, als es nicht möglich war, Dinge im Speicher zu behalten. Sie hatten nur , um Speicherplatz irgendwie zu benutzen.

Wenn Speicherplatz sowieso verwendet werden soll, müssen Sie eine Sperre irgendwo auf der Festplatte platzieren.

Und wo bleibt eine Sperre für eine Zeile, wenn nicht in der Zeile selbst?

Entwickler des SQL Server-Sperrsystems entschieden sich bei der Erfindung des Designs ihres RDBMS namens Sybase dafür, temporäre Dinge (d. h. Sperren) im temporären Speicher (z. B. RAM) zu speichern.

Aber das Design von Oracle ist immer ausgewogen: Wenn Sie 1.000.000 Zeilen in Ihrer Datenbank haben, dann haben Sie einen Speicherplatz für 1.000.000 Sperren, wenn Sie eine Milliarde Zeilen haben, können Sie Milliarden Sperren usw. speichern.

Der Entwurf von SQL Server ist in diesem Sinne fehlerhaft, weil der Arbeitsspeicher und der Festplattenspeicher möglicherweise nicht ausgewogen sind. Sie können problemlos 16 MB RAM und mehrere Terabyte Speicherplatz haben. Und deine Erinnerung kann nicht alle Schlösser halten.

Aus diesem Grund entscheidet SQL Server, wenn die Anzahl der Sperren eine bestimmte Grenze erreicht hat, die Sperren zu eskalieren: Statt Sperren für beispielsweise 10 einzelne Zeilen auf einer Datenseite (die 10 Datensätze benötigt) zu sperren, wird die gesamte Datenseite gesperrt (was 1 Datensatz erfordert).

Oracle dagegen schreibt beim Aktualisieren einer Zeile einfach die Sperre direkt in die Datenseite.

Aus diesem Grund sind die Sperren von Oracle auf Zeilenebene.

Oracle "verwaltet" die Sperren im allgemeinen Sinne des Wortes nicht: Sie können nicht sagen, eine Liste gesperrter Seiten in Oracle zu erhalten.

Wenn eine Transaktion eine Zeile aktualisieren muss, geht sie einfach in die Zeile und sieht, ob sie gesperrt ist.

Wenn ja, sieht es aus, welche Transaktion eine Sperre enthält (diese Information ist im Sperrdeskriptor auf der Datenseite enthalten) und fügt sich selbst zur Benachrichtigungswarteschlange dieser Transaktion hinzu: Wenn die Sperrtransaktionen abstürzen, wird die ursprüngliche Transaktion benachrichtigt und gesperrt die Daten.

Aus der Sicht des Nebenläufers ist die Lock-Eskalation völlig die Lösung eines armen Mannes: Es fügt nichts zur Nebenläufigkeit hinzu. Sie können zum Beispiel eine Reihe sperren, die Sie nicht einmal berührt haben.

Aus der Sicht der Performance ist es natürlich schneller, Dinge im Speicher zu erledigen, als sie auf der Festplatte auszuführen.

Da Oracle jedoch die Datenblöcke zwischenspeichert und die oben beschriebenen Operationen im Speicher ausgeführt werden, ist die Leistung gleich oder liegt nahe beieinander.

Wenn der SQL Server-Optimierer schätzt / entscheidet, dass eine Abfrage alle Zeilen in einem bestimmten Bereich "besucht", ist es effizienter, eine einzige Sperre über diesen Bereich zu halten, als viele Sperren aushandeln zu müssen (Sperren müssen) auf Typ getestet werden). Dies ist zusätzlich dazu, dass weniger Sperrressourcen verbraucht werden (eine systemweite Ressource).

Wenn Sie ein gut gestaltetes Schema und für Ihren Abfrage-Workload geeignete Indizes haben, die regelmäßig gepflegt werden, sollten Sie sich keine Gedanken über die auftretende Eskalation machen. In vielen Fällen können blockierende Tabellensperren durch die entsprechenden abdeckenden Indizes eliminiert werden.

UPDATE: Ein überdeckender Index für eine Abfrage bedeutet, dass eine Suche in das Clustered nicht durchgeführt werden muss, und dies verringert die Wahrscheinlichkeit, dass Einfügungen in die Tabelle blockiert werden.

Der Overhead der Sperre bedeutet, dass die Verwaltung einer Tabellensperre besser ist als die Verwaltung einer großen Anzahl von Zeilensperren. Da jede Sperre Speicherplatz benötigt, können viele Zeilensperren viel mehr Speicher belegen als eine Tabellensperre. Die Lock-Eskalation geht also von row- & gt; page- & gt; table lock aus.

Die Definition von "effizient" ist komplex. Manchmal ist es effizienter, auf Gleichzeitigkeit zu optimieren, wenn viele Prozesse dies ohne Kollisionen tun können. Manchmal ist es effizienter, einen temporären Concurrency-Hit zu nehmen, um einen einzelnen Prozess schneller zu erledigen. Die eskalierte Sperre hält andere Prozesse außer Betrieb, damit dieser Prozess seine Arbeit erledigen und aus dem Weg schaffen kann.

Genauere Informationen darüber, wie Sperren beibehalten werden, finden Sie in Kapitel 8 von Microsoft SQL Server 2005: Die Speicher-Engine (Ich bin nicht angegliedert, dies sind nur die ersten internen Informationen, auf die ich gestoßen bin). Wenn du einen books24x7-Account hast, ist er da. Es zeigt auf einem & gt; 16GB Speichergerät 2 ^ 25 (33554432) Slots in der Lock-Hash-Tabelle, mit einer oberen Grenze von 2 ^ 31 Slots.

Für eine gegebene Anwendung kann es sehr gut sein, dass der Gesamtdurchsatz höher ist, wenn nur feinkörnige Sperren verwendet werden. Wie Sie wahrscheinlich erraten können, hängt alles davon ab, wie der Overhead der Sperrverwaltung im Vergleich zu einer übermäßigen Sperrung steht.