Free Press

Das Sperren ist für die erfolgreiche Verarbeitung von SQL Server-Transaktionen unerlässlich und wurde entwickelt, damit SQL Server nahtlos in einer Mehrbenutzerumgebung arbeiten kann. Sperren sind die Art und Weise, wie SQL Server die Gleichzeitigkeit von Transaktionen verwaltet. Im Wesentlichen sind Sperren In-Memory-Strukturen, die Eigentümer, Typen und den Hash der Ressource haben, die sie schützen sollen. Eine Sperre als In-Memory-Struktur hat eine Größe von 96 Byte.

Um das Sperren in SQL Server besser zu verstehen, ist es wichtig zu wissen, dass das Sperren dazu dient, die Integrität der Daten in der Datenbank zu gewährleisten, da es jede SQL Server-Transaktion zwingt, den ACID-Test zu bestehen.

Der ACID-Test besteht aus 4 Anforderungen, die jede Transaktion erfolgreich bestehen muss:

• Atomarität – erfordert, dass eine Transaktion, die zwei oder mehr diskrete Teile von Informationen umfasst, alle Teile oder keine übertragen muss
• Konsistenz – erfordert, dass eine Transaktion einen gültigen Zustand neuer Daten erzeugen muss, oder sie muss alle Daten auf den Zustand zurücksetzen, der vor der Ausführung der Transaktion bestand
• Isolation – erfordert, dass eine Transaktion, die noch läuft und noch nicht alle Daten übertragen hat, von allen anderen Transaktionen isoliert bleiben muss
• Dauerhaftigkeit – erfordert, dass festgeschriebene Daten mit einer Methode gespeichert werden müssen, die alle Daten in korrektem Zustand und für einen Benutzer verfügbar erhält, selbst im Falle eines Fehlers

SQL Server Sperren ist der wesentliche Teil der Isolationsanforderung und dient dazu, die von einer Transaktion betroffenen Objekte zu sperren. Solange Objekte gesperrt sind, verhindert SQL Server, dass andere Transaktionen Änderungen an Daten vornehmen, die in den von der Sperre betroffenen Objekten gespeichert sind. Sobald die Sperre durch Übertragen der Änderungen oder durch Zurücksetzen der Änderungen auf den Ausgangszustand aufgehoben wird, können andere Transaktionen die erforderlichen Datenänderungen vornehmen.

Übersetzt in die SQL Server-Sprache bedeutet dies, dass, wenn eine Transaktion ein Objekt sperrt, alle anderen Transaktionen, die den Zugriff auf dieses Objekt benötigen, gezwungen sind, zu warten, bis die Sperre aufgehoben ist, und dass diese Wartezeit mit dem entsprechenden Wartentyp registriert wird

SQL Server-Sperren können über die Sperrmodi und die Sperrgranularität spezifiziert werden

Sperrmodi

Der Sperrmodus berücksichtigt verschiedene Sperrtypen, die auf eine zu sperrende Ressource angewendet werden können:

• Exklusiv (X)
• Gemeinsam (S)
• Update (U)
• Intent (I)
• Schema (Sch)
• Massenaktualisierung (BU)

Exklusive Sperre (X) – Dieser Sperrtyp, stellt sicher, dass eine Seite oder Zeile ausschließlich für die Transaktion reserviert wird, die die exklusive Sperre verhängt hat, solange die Transaktion die Sperre hält.

Die exklusive Sperre wird von der Transaktion auferlegt, wenn sie die Seiten- oder Zeilendaten ändern will, was bei den DML-Anweisungen DELETE, INSERT und UPDATE der Fall ist. Eine exklusive Sperre kann nur dann für eine Seite oder Zeile verhängt werden, wenn keine andere gemeinsame oder exklusive Sperre bereits für das Ziel verhängt ist. Dies bedeutet praktisch, dass nur eine exklusive Sperre für eine Seite oder Zeile verhängt werden kann, und dass, sobald sie verhängt wurde, keine andere Sperre für gesperrte Ressourcen verhängt werden kann

Gemeinsame Sperre (S) – dieser Sperrtyp reserviert, wenn er verhängt wird, eine Seite oder Zeile, die nur zum Lesen verfügbar ist, was bedeutet, dass jede andere Transaktion daran gehindert wird, den gesperrten Datensatz zu ändern, solange die Sperre aktiv ist. Eine Lesesperre kann jedoch von mehreren Transaktionen gleichzeitig für dieselbe Seite oder Zeile verhängt werden, und auf diese Weise können sich mehrere Transaktionen die Fähigkeit zum Lesen von Daten teilen, da der Lesevorgang selbst die eigentlichen Seiten- oder Zeilendaten in keiner Weise beeinflusst. Außerdem erlaubt eine gemeinsame Sperre Schreiboperationen, aber keine DDL-Änderungen

Aktualisierungssperre (U) – diese Sperre ähnelt einer exklusiven Sperre, ist aber in gewisser Weise flexibler gestaltet. Eine Aktualisierungssperre kann auf einen Datensatz angewendet werden, der bereits eine gemeinsame Sperre hat. In einem solchen Fall wird durch die Aktualisierungssperre eine weitere gemeinsame Sperre für die Zielzeile verhängt. Sobald die Transaktion, die die Aktualisierungssperre hält, bereit ist, die Daten zu ändern, wird die Aktualisierungssperre (U) in eine exklusive Sperre (X) umgewandelt. Es ist wichtig zu verstehen, dass die Aktualisierungssperre in Bezug auf die Lesesperren asymmetrisch ist. Während die Aktualisierungssperre einem Datensatz auferlegt werden kann, der die gemeinsame Sperre hat, kann die gemeinsame Sperre nicht einem Datensatz auferlegt werden, der bereits die Aktualisierungssperre hat

Intent-Sperren (I) – diese Sperre ist ein Mittel, das von einer Transaktion verwendet wird, um eine andere Transaktion über ihre Absicht zu informieren, eine Sperre zu erwerben. Der Zweck einer solchen Sperre ist es, die ordnungsgemäße Ausführung von Datenänderungen zu gewährleisten, indem eine andere Transaktion daran gehindert wird, eine Sperre auf das nächste Objekt in der Hierarchie zu erwerben. In der Praxis erwirbt eine Transaktion, die eine Zeile sperren will, eine beabsichtigte Sperre für eine Tabelle, die ein Objekt mit höherer Hierarchie ist. Durch den Erwerb der Intent-Sperre erlaubt die Transaktion anderen Transaktionen nicht, die exklusive Sperre auf diese Tabelle zu erwerben (andernfalls würde die von einer anderen Transaktion auferlegte exklusive Sperre die Zeilensperre aufheben).

Dies ist ein wichtiger Sperrtyp unter dem Leistungsaspekt, da die SQL Server-Datenbank-Engine Intent Locks nur auf Tabellenebene untersucht, um zu prüfen, ob es für eine Transaktion möglich ist, eine Sperre auf sichere Weise in dieser Tabelle zu erwerben, und daher entfällt bei Intent Locks die Notwendigkeit, jede Zeilen-/Seitensperre in einer Tabelle zu untersuchen, um sicherzustellen, dass die Transaktion eine Sperre für die gesamte Tabelle erwerben kann

Es gibt drei reguläre Intent Locks und drei so genannte Conversion Locks:

Reguläre Absichtssperren:

Absichtliche Exklusivsperre (IX) – wenn eine absichtliche Exklusivsperre (IX) erworben wird, zeigt sie SQL Server an, dass die Transaktion die Absicht hat, einige Ressourcen in der unteren Hierarchie zu ändern, indem sie individuell exklusive (X) Sperren für diese Ressourcen in der unteren Hierarchie erwirbt

Absichtliche gemeinsame Sperre (IS) – wenn eine absichtliche gemeinsame Sperre (IS) erworben wird, zeigt sie SQL Server an, dass die Transaktion die Absicht hat, einige Ressourcen in einer niedrigeren Hierarchie zu lesen, indem sie individuell Shared Locks (S) für diese Ressourcen in der niedrigeren Hierarchie erwirbt

Intent update (IU) – wenn eine Intent Shared Lock (IS) erworben wird, zeigt sie SQL Server an, dass die Transaktion die Absicht hat, einige Ressourcen in einer niedrigeren Hierarchie zu lesen, indem sie individuell Shared Locks (S) für diese Ressourcen in der niedrigeren Hierarchie erwirbt. Die Absichtsaktualisierungssperre (IU) kann nur auf Seitenebene erworben werden, und sobald die Aktualisierungsoperation stattfindet, wird sie in die Absichts-Exklusivsperre (IX) umgewandelt:

Umwandlungssperren:

Gemeinsam mit Absichts-Exklusivsperre (SIX) – wenn sie erworben wird, zeigt diese Sperre an, dass die Transaktion beabsichtigt, alle Ressourcen in einer niedrigeren Hierarchie zu lesen und somit die gemeinsame Sperre für alle Ressourcen zu erwerben, die in der Hierarchie niedriger sind, und wiederum einen Teil dieser Ressourcen zu ändern, aber nicht alle. Dabei erwirbt sie eine absichtliche Exklusivsperre (IX) für die Ressourcen der unteren Hierarchie, die geändert werden sollen. In der Praxis bedeutet dies, dass die Transaktion, sobald sie eine SIX-Sperre für die Tabelle erwirbt, eine absichtliche exklusive Sperre (IX) für die geänderten Seiten und eine exklusive Sperre (X) für die geänderten Zeilen erwirbt.

Für eine Tabelle kann jeweils nur eine gemeinsam genutzte exklusive Sperre (SIX) erworben werden, die andere Transaktionen daran hindert, Aktualisierungen vorzunehmen, aber sie hindert andere Transaktionen nicht daran, die Ressourcen der unteren Hierarchie zu lesen, wenn sie die gemeinsam genutzte Sperre (IS) für die Tabelle erwerben können

Gemeinsam genutzte Sperre mit beabsichtigter Aktualisierung (SIU) – dies ist eine etwas spezifischere Sperre, da sie eine Kombination aus der gemeinsam genutzten Sperre (S) und der beabsichtigten Aktualisierung (IU) ist. Ein typisches Beispiel für diese Sperre ist, wenn eine Transaktion eine Abfrage verwendet, die mit dem PAGELOCK-Hinweis und der Abfrage ausgeführt wird, dann die Aktualisierungsabfrage. Nachdem die Transaktion eine SIU-Sperre für die Tabelle erworben hat, erwirbt die Abfrage mit dem PAGELOCK-Hinweis die gemeinsame (S) Sperre, während die Aktualisierungsabfrage die absichtliche Aktualisierungssperre (IU) erwirbt

Aktualisierung mit absichtlicher Exklusivsperre (UIX) – wenn Aktualisierungssperre (U) und absichtliche Exklusivsperren (IX) gleichzeitig an Ressourcen in der Tabelle mit niedrigerer Hierarchie erworben werden, wird die Aktualisierungssperre mit ausschließlicher Absicht als Folge auf der Tabellenebene erworben

Schemasperren (Sch) – Die SQL Server-Datenbank-Engine erkennt zwei Arten von Schemasperren: Schema-Modifikationssperre (Sch-M) und Schema-Stabilitätssperre (Sch-S)

• Eine Schema-Modifikationssperre (Sch-M) wird erworben, wenn eine DDL-Anweisung ausgeführt wird, und sie verhindert den Zugriff auf die gesperrten Objektdaten, während die Struktur des Objekts geändert wird. SQL Server erlaubt eine einzige Schemaänderungssperre (Sch-M) für jedes gesperrte Objekt. Um eine Tabelle zu ändern, muss eine Transaktion darauf warten, eine Sch-M-Sperre für das Zielobjekt zu erhalten. Sobald sie die Schema-Modifikationssperre (Sch-M) erworben hat, kann die Transaktion das Objekt ändern und nach Abschluss der Änderung wird die Sperre freigegeben. Ein typisches Beispiel für eine Sch-M-Sperre ist ein Index-Rebuild, da ein Index-Rebuild ein Tabellenänderungsprozess ist. Sobald die Indexneuaufbau-ID ausgegeben wird, wird eine Schemaänderungssperre (Sch-M) für diese Tabelle erworben und erst freigegeben, wenn der Indexneuaufbau abgeschlossen ist (bei Verwendung der ONLINE-Option erwirbt der Indexneuaufbau die Sch-M-Sperre kurz am Ende des Prozesses)
• Eine Schemastabilitätssperre (Sch-S) wird erworben, während eine schemaabhängige Abfrage kompiliert und ausgeführt und ein Ausführungsplan erstellt wird. Diese spezielle Sperre blockiert keine anderen Transaktionen für den Zugriff auf die Objektdaten und ist mit allen Sperrmodi außer der Schemaänderungssperre (Sch-M) kompatibel. Im Wesentlichen werden Schemastabilitätssperren von jeder DML- und Select-Abfrage erworben, um die Integrität der Tabellenstruktur zu gewährleisten (sicherzustellen, dass sich die Tabelle nicht ändert, während die Abfragen ausgeführt werden).

Bulk-Update-Sperren (BU) – diese Sperre ist für Bulk-Import-Operationen gedacht, die mit einem TABLOCK-Argument/Hinweis ausgegeben werden. Wenn eine Massenaktualisierungssperre erworben wird, können andere Prozesse während der Ausführung des Massenimports nicht auf eine Tabelle zugreifen. Eine Massenaktualisierungssperre verhindert jedoch nicht, dass ein anderer Massenimport parallel verarbeitet werden kann. Beachten Sie jedoch, dass die Verwendung von TABLOCK auf einer geclusterten Indextabelle kein paralleles Bulk-Importing ermöglicht. Weitere Einzelheiten hierzu finden Sie in Guidelines for Optimizing Bulk Import

Sperrhierarchie

SQL Server hat die Sperrhierarchie eingeführt, die beim Lesen oder Ändern von Daten angewendet wird. Die Sperrhierarchie beginnt mit der Datenbank auf der höchsten Hierarchieebene und geht über die Tabelle und die Seite bis zur Zeile auf der niedrigsten Ebene

Im Wesentlichen gibt es immer eine gemeinsame Sperre auf der Datenbankebene, die auferlegt wird, wenn eine Transaktion mit einer Datenbank verbunden ist. Die gemeinsame Sperre auf Datenbankebene wird auferlegt, um zu verhindern, dass die Datenbank gelöscht oder eine Datenbanksicherung über die verwendete Datenbank wiederhergestellt wird. Wenn beispielsweise eine SELECT-Anweisung zum Lesen von Daten ausgegeben wird, wird eine gemeinsame Sperre (S) auf Datenbankebene, eine gemeinsame Sperre (IS) auf Tabellen- und Seitenebene und eine gemeinsame Sperre (S) auf die Zeile selbst auferlegt

Im Falle einer DML-Anweisung (d.h.. Insert, Update, Delete) wird auf Datenbankebene eine Shared Lock (S), auf Tabellen- und Seitenebene eine Intent-Exclusive-Lock (IX) oder Intent-Update-Lock (IU) und auf der Zeile eine Exclusive- oder Update-Lock (X oder U) auferlegt

Sperren werden immer von oben nach unten erworben, da SQL Server auf diese Weise das Auftreten einer sogenannten Race-Bedingung verhindert.

Nachdem nun die Sperrmodi und die Sperrhierarchie erklärt wurden, wollen wir uns nun näher mit den Sperrmodi beschäftigen und wie sich diese auf die Sperrhierarchie auswirken.

Nicht alle Sperrmodi können auf allen Ebenen angewendet werden.

Auf Zeilenebene können die folgenden drei Sperrmodi angewendet werden:

• Exklusiv (X)
• Gemeinsam (S)
• Aktualisierung (U)

Zum Verständnis der Kompatibilität dieser Modi siehe folgende Tabelle:

✓ – Kompatibel ✗ – Inkompatibel

Auf Tabellenebene, gibt es fünf verschiedene Arten von Sperren:

• Exklusiv (X)
• Gemeinsam genutzt (S)
• Mit Absicht exklusiv (IX)
• Mit Absicht gemeinsam genutzt (IS)
• Gemeinsam genutzt mit Absicht exklusiv (SIX)

Die Kompatibilität dieser Modi ist aus der folgenden Tabelle ersichtlich

✓ – Kompatibel ✗ – Inkompatibel

Eine Schemasperre (Sch) ist auch eine Sperre auf Tabellenebene, aber es ist keine datenbezogene Sperre

Um die Kompatibilität zwischen diesen Sperrtypen besser zu verstehen, schauen Sie bitte in diese Tabelle:

Sperr-Eskalation

Um zu verhindern, dass eine Sperre zu viele Ressourcen verbraucht, hat SQL Server die Funktion der Sperr-Eskalation eingeführt.

Ohne Eskalation könnten Sperren eine erhebliche Menge an Speicherressourcen beanspruchen. Nehmen wir ein Beispiel, bei dem eine Sperre für 30.000 Datenzeilen mit einer Größe von 500 Byte pro Zeile verhängt werden soll, um den Löschvorgang durchzuführen. Ohne Eskalation wird eine gemeinsame Sperre (S) auf die Datenbank, eine absichtliche ausschließliche Sperre (IX) auf die Tabelle, 1.875 absichtliche ausschließliche Sperren (IX) auf die Seiten (8KB-Seiten enthalten 16 Zeilen von 500 Bytes, was 1.875 Seiten ergibt, die 30.000 Zeilen enthalten) und 30.000 ausschließliche Sperren (X) auf die Zeilen selbst auferlegt. Da jede Sperre 96 Byte groß ist, benötigen 31.877 Sperren etwa 3 MB Speicher für einen einzigen Löschvorgang. Die parallele Ausführung einer großen Anzahl von Operationen könnte erhebliche Ressourcen erfordern, nur um sicherzustellen, dass der Sperrmanager die Operation reibungslos durchführen kann

Um eine solche Situation zu verhindern, verwendet SQL Server die Sperreneskalation. Das bedeutet, dass SQL Server in einer Situation, in der mehr als 5.000 Sperren auf einer einzigen Ebene erworben werden, diese Sperren zu einer einzigen Sperre auf Tabellenebene eskaliert. Standardmäßig wird SQL Server immer direkt auf die Tabellenebene eskalieren, was bedeutet, dass eine Eskalation auf die Seitenebene nie stattfindet. Anstatt zahlreiche Zeilen und Seiten zu sperren, wird SQL Server auf die exklusive Sperre (X) auf Tabellenebene eskalieren

Während dies den Bedarf an Ressourcen reduziert, bedeuten exklusive Sperren (X) in einer Tabelle, dass keine andere Transaktion in der Lage ist, auf die gesperrte Tabelle zuzugreifen und alle Abfragen, die versuchen, auf diese Tabelle zuzugreifen, blockiert werden. Daher wird der System-Overhead reduziert, aber die Wahrscheinlichkeit von Gleichzeitigkeitskonflikten erhöht

Um die Kontrolle über die Eskalation zu gewährleisten, wird ab SQL Server 2008 R2, wird die Option LOCK_EXCALATION als Teil der ALTER TABLE-Anweisung eingeführt

Jede dieser Optionen wird definiert, um eine spezifische Kontrolle über den Sperreneskalationsprozess zu ermöglichen:

Tabelle – Dies ist die Standardoption für jede neu erstellte Tabelle, da SQL Server standardmäßig immer eine Sperreneskalation auf Tabellenebene durchführt, was auch partitionierte Tabellen einschließt

Auto – Diese Option ermöglicht die Sperreneskalation auf eine Partitionsebene, wenn eine Tabelle partitioniert ist. Wenn 5.000 Sperren in einer einzigen Partition erworben werden, erwirbt die Lock Escalation eine exklusive Sperre (X) auf dieser Partition, während die Tabelle eine beabsichtigte exklusive Sperre (IX) erwirbt. Wenn die Tabelle nicht partitioniert ist, erwirbt die Sperreneskalation die Sperre auf Tabellenebene (entsprechend der Option Tabelle).

Obwohl dies eine sehr nützliche Option zu sein scheint, muss sie sehr vorsichtig eingesetzt werden, da sie leicht einen Deadlock verursachen kann. In einer Situation, in der wir zwei Transaktionen auf zwei Partitionen haben, wo die exklusive Sperre (X) erworben wurde, und die Transaktionen versuchen, auf das Datum der Partition zuzugreifen, die von der anderen Transaktion verwendet wird, wird ein Deadlock auftreten

Also, ist es sehr wichtig, das Datenzugriffsmuster sorgfältig zu kontrollieren, wenn diese Option aktiviert ist, was nicht einfach zu erreichen ist, und deshalb ist diese Option nicht die Standardeinstellung in SQL Server

Deaktivieren – Diese Option deaktiviert die Sperrenerweiterung für eine Tabelle vollständig. Auch diese Option muss mit Bedacht eingesetzt werden, um zu vermeiden, dass der SQL Server-Sperrmanager gezwungen ist, eine übermäßige Menge an Speicher zu verwenden

Wie man sieht, kann die Sperreneskalation eine Herausforderung für DBAs darstellen. Wenn das Anwendungsdesign das Löschen oder Aktualisieren von mehr als 5.000 Zeilen auf einmal erfordert, besteht eine Lösung zur Vermeidung der Lock-Eskalation und der daraus resultierenden Auswirkungen darin, die einzelne Transaktion in zwei oder mehr Transaktionen aufzuteilen, von denen jede weniger als 5.000 Zeilen bearbeitet, da auf diese Weise die Lock-Eskalation umgangen werden kann

Informationen über aktive SQL Server-Sperren abrufen

SQL Server bietet die Dynamics Management View (DMV) sys.dm_tran_locks zur Verfügung, die Informationen über aktuell verwendete Lockmanager-Ressourcen zurückgibt, d.h. sie zeigt alle von Transaktionen erworbenen „aktiven“ Sperren an. Weitere Details zu dieser DMV finden Sie im Artikel sys.dm_tran_locks (Transact-SQL).

Die wichtigsten Spalten zur Identifizierung der Sperre sind resource_type, request_mode und resource_description. Bei Bedarf können weitere Spalten als zusätzliche Informationsquelle bei der Fehlersuche mit einbezogen werden

Hier das Beispiel der Abfrage

Die where-Klausel in dieser Abfrage wird als Filter auf den zu eliminierenden resource_type verwendet. Die where-Klausel dieser Abfrage wird als Filter für den Ressourcentyp verwendet, um aus den Ergebnissen diejenigen zu eliminieren, die im Allgemeinen gemeinsam genutzte Sperren auf der Datenbank erworben haben, da diese immer auf Datenbankebene vorhanden sind

Eine kurze Erläuterung der drei hier vorgestellten Spalten:

Ressourcentyp – Zeigt eine Datenbankressource an, bei der die Sperren erworben werden. Die Spalte kann einen der folgenden Werte anzeigen: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

request_mode – zeigt den Sperrmodus an, der auf die Ressource angewendet wird

resource_description – zeigt eine kurze Beschreibung der Ressource an und wird nicht für alle Sperrmodi ausgefüllt. Meistens enthält die Spalte die ID der Zeile, Seite, des Objekts oder der Datei, usw

• Autor
• Recent Posts

Nikola ist Computerfreak seit 1981 und ein SQL-Enthusiast mit der Absicht, ein Freak zu werden. Spezialisiert auf SQL Server Auditing, Compliance und Leistungsüberwachung.
Liebhaber der militärischen Luftfahrt und leidenschaftlicher Modellbauer von Flugzeugen. Extremsportfan; Fallschirmspringer und Bungee-Jump-Lehrer. Früher ernsthaft, jetzt nur noch Freizeitfotograf
Alle Beiträge von Nikola Dimitrijevic anzeigen

Neueste Beiträge von Nikola Dimitrijevic (alle anzeigen)

• SQL Server trace flags guide; von -1 bis 840 – March 4, 2019
• How to handle the SQL Server WRITELOG wait type – June 13, 2018
• SQL Server performance counters (Batch Requests/sec or Transactions/sec): Was zu überwachen ist und warum – June 5, 2018