SQL Server-Sperren

Alles, was Sie über SQL Server-Sperren wissen müssen

SQLsentry-white

Aktualisiert: 29. Juni 2020

Inhaltsverzeichnis:

 

 

Was sind SQL Server-Sperren?

 

Sperren werden in sperrenbasierten Parallelitätssystemen verwendet, wenn Prozesse auf Ressourcen zugreifen. Je nach Art des benötigten Zugriffs erhält ein Prozess eine logische „Sperre“. Diese Sperre weist konkurrierende Prozessen an, zu warten. Die Prozesse, die auf eine Sperre warten, sind „gesperrt“.

SQL Server ist ein sperrenbasiertes Parallelitätssystem. SQL Server-Sperren sind daher geplant und erforderlich, um die Datenintegrität zu schützen und parallele Prozesse zu ermöglichen. Sie sind normal und zu erwarten.

 

Weitere Informationen zu SQL Server-Sperren

SQL Server-Sperrmodi

Sperren in SQL Server verwenden ein ausgeklügeltes Design. Dieses Design verbessert die Parallelität von Prozessen, da nicht alle Arten von Sperren alle anderen Arten von Sperren blockieren. Jede Sperre verwendet einen Modus, der dem Prozess die benötigte Zugriffsart auf die Ressource mitteilt. Der Sperrmodus legt fest, wie restriktiv die Sperre für andere Prozesse ist, und damit auch, wie wahrscheinlich es ist, dass ein Prozess eine Blockade verursacht.

Die folgende Tabelle enthält eine Liste der gängigsten Sperrmodi. (Eine vollständige Liste finden Sie in der Microsoft-Dokumentation.)

  • Exklusiv – Eine exklusive Sperre wird für Datenänderungen erteilt, um sicherzustellen, dass zu jedem Zeitpunkt nur ein Prozess die Daten ändern kann.
  • Schema – Eine Schemasperre wird erteilt, wenn sich ein Prozess darauf verlassen muss, dass das Schema während seiner Vorgänge unverändert bleibt.
  • Gemeinsam – Gemeinsame Sperren werden für Lesevorgänge erteilt und verursachen nur minimale Sperren.
  • Aktualisierung – Aktualisierungssperren werden normalerweise für Änderungen erteilt, die in mehreren Schritten erfolgen.

Absichtssperren werden für Änderungen verwendet. Die Sperrmodi IX oder IS werden beispielsweise häufig verwendet. Ein Sperrmodus mit einem „I“ deutet auf eine beabsichtigte Sperre hin. Mit dieser Leistungsoptimierung kann SQL Server potenzielle Kompatibilitätsprobleme im Zusammenhang mit Sperren schneller auswerten. Eine ausführlichere Erklärung von beabsichtigten Sperren finden Sie in diesem Blogbeitrag.

Kompatibilität von SQL Server-Sperren

Die Kompatibilität von Sperren gibt an, ob ein bestimmter Sperrmodus erteilt werden kann, wenn ein bestimmter anderer Sperrmodus bereits erteilt wurde. Sperrmodi, die nicht miteinander kompatibel sind, führen zu einer Blockade.

In der folgenden Tabelle können Sie ablesen, welche Sperrtypen einander gegenseitig sperren. Die Tabelle enthält nicht alle Sperrmodi, aber Sie sollten alle im Normalfall auftretenden Modi darin finden.

Microsoft SQL Server Documentation

Quelle: Microsoft SQL Server-Dokumentation

Sperrenausweitung

Die Sperrenausweitung ist ein Mechanismus in SQL Server. Dabei werden mehrere Sperren auf einer niedrigen Ebene zu weniger Sperren auf einer höheren Stufe in der Hierarchie zusammengefasst. Die Sperrenausweitung reduziert den für die Sperrenverwaltung benötigten Arbeitsspeicher. Die Arbeitsspeicherauslastung wird reduziert, da weniger Sperren für größere Bereichsobjekte anstelle von vielen Sperren für kleinere Objekte verwaltet werden.

Sperrenhierarchie

Die Sperrenhierarchie in SQL Server ist relativ einfach. Auf der höchsten Ebene gibt es Datenbanksperren und auf der niedrigsten Ebene Zeilensperren. Falls Sie schon von Latches gehört haben, vergessen Sie diesen Mechanismus für einen Moment. Sperren und Latches haben einen ähnlichen Zweck, sind jedoch keinesfalls identisch.

192993ec-3e4d-c88c-cc2b-01d6b15b2c86

Die Sperrenhierarchie für SQL Server von oben nach unten

Sind SQL Server-Sperren grundsätzlich schlecht?

Sperren sind in SQL Server völlig normal. Die meisten kurzzeitigen Sperren erfolgen völlig unbemerkt. Manchmal dauert es länger als erwartet, bis eine Sperre behoben wird. Sperren mit längerer Dauer können zu Kettenreaktionen führen, bei denen Prozesse einander blockieren. Diese Art von Sperren ist problematisch. Die Antwort ist also Ja und Nein. Sperren sind zu erwarten, können aber auch zu Problemen führen.

 

Wie kann ich problematische Sperren erkennen?

Zunächst müssen Sie festlegen, ab wann eine Sperre problematisch ist. Dafür wird oft die Dauer der Sperre verwendet. Wenn Sie sich nicht sicher sind, empfehlen wir einen Ausgangswert zwischen 5 und 15 Sekunden. Mehr als 30 Sekunden ist nicht empfehlenswert, da viele Client-Frameworks standardmäßig eine Zeitüberschreitung von 30 Sekunden verwenden. Wenn ein Befehl abgebrochen wird, bevor die Sperre erkannt wird, erfahren Sie unter Umständen nicht, ob sich die zugrunde liegende Sperre auf die Benutzer der Anwendung ausgewirkt hat.

 

Gesperrte Anfragen auflisten

Nachdem Sie sich für eine Dauer entschieden haben, können Sie SQL Server DMVs verwenden, um nach Sperren mit einer längeren Dauer zu suchen. Die folgende einfache Abfrage verwendet die DMV „sys.dm_exec_requests“:

declare @durationInSeconds float = 5;

select

der.session_id,

der.blocking_session_id,

der.wait_type,

der.wait_time

from

sys.dm_exec_requests der

where

der.wait_time >= (@durationInSeconds * 1000)

and der.blocking_session_id != 0;

 

Diese Abfrage liefert eine Liste der gesperrten SPIDs mit der sperrenden SPID, dem aktuellen Wartetyp und der Wartezeit in Millisekunden. Die Abfrage verwendet einen Filter mit der Dauer, die Sie im Parameter am Anfang festlegen.

Diese Abfrage weist zwei grundsätzliche Einschränkungen auf:

  1. Sie zeigt nur eine Momentaufnahme und hilft nicht beim Verständnis von Sperren, die zu anderen Zeitpunkten aufgetreten sind.
  2. Sie zeigt nur die SPID der Anfrage und der sperrenden SPID an. Die Ursache des Problems ist jedoch an der Spitze der Sperrenkette zu suchen. Anhand dieser Ergebnissen wird es Ihnen schwer fallen, sich ein Bild zu machen. Es ist sehr wahrscheinlich, dass die eigentlichen problematischen Prozesse übersehen werden.

 

Fortlaufende Überwachung von SQL Server-Sperren

Wenn wir die Einschränkungen der DMV-Abfrage beheben, können wir unser System fortlaufend auf Sperren überwachen. Dazu können wir im einfachsten Fall einen Auftrag planen, der die Ergebnisse einer DMV-Abfrage in einer Tabelle speichert. Als Alternative können Sie eine externe Leistungsüberwachungslösung für Ihre Datenbank verwenden, wie etwa SentryOne SQL Sentry. SQL Sentry sammelt beispielsweise Informationen zu problematischen Sperren, erfasst den Verlauf der Sperrendetails und bereitet Sperrenketten für die Analyse grafisch auf.

f39861b8-a667-9485-5f20-ea1513de4fd5

 

Wie kann ich Probleme im Zusammenhang mit SQL Server-Sperren beheben?

Problematische SQL Server-Sperren sind oft auf dieselben Ursachen zurückzuführen.

  1. Zum Beispiel eine langsame Abfrage mit aggressiven Sperren, während viele andere Prozesse dieselben Daten lesen oder bearbeiten.
  2. Oder eine Anwendung mit einer komplexen Transaktion, die mehrere Anweisungen oder Batches vor dem Commit ausführt.
  3. Oder eine Transaktion, die versehentlich offen gelassen wird und ihre Sperren nicht freigibt.

Um die ersten beiden Gründe zu beheben, müssen Sie zunächst die Abfragen an der Spitze der Sperrenkette identifizieren. Dazu können Sie Ihre Überwachungsplattform verwenden oder DMVs analysieren. Sobald Sie die Abfragen identifiziert haben, können Sie deren Leistung optimieren, um das Sperrenproblem zu beheben. Die Leistungsoptimierung von Abfragen übersteigt den Umfang dieses Artikels, aber SentryOne Plan Explorer ist ein guter Ausgangspunkt.

Im Fall von verwaisten offenen Transaktionen müssen Sie die SPID identifizieren, die dazu führt, dass die Transaktion nicht geschlossen wird. Dazu können Sie die DMV sys.dm_tran_active_transactions abfragen. An dieser Stelle haben Sie eingeschränkte Optionen zur Auswahl. Wenn Sie die Abfrage identifizieren und unter Kontrolle bringen, können Sie einen Rollback ausführen. Andernfalls müssen Sie vermutlich den T-SQL-Befehl „kill“ verwenden, um den Prozess zu beenden.

SQL Server-Sperren mit SQL Sentry überwachen

 

Es ist nicht zwangsläufig kompliziert, SQL Server-Sperren nachzuverfolgen und zu beheben. Mit den Sperrenanalysen von SQL Sentry können Sie alle Details zu SQL-Sperren auf Basis der konfigurierten Schwellenwerte für die Sperrendauer erfassen. Sie können die Sperrenketten entweder in Echtzeit oder als Verlaufsdaten anzeigen. Informationen wie die ausgeführte Anweisung, Anmeldung, Host und Datenbank werden für alle beteiligten Prozesse angezeigt. Außerdem werden Wartetypen und wartende Ressourcen angezeigt. Sie können Sperren direkt beim Auftreten erkennen und sperrende Prozesse direkt im SQL Sentry-Client mit einem Klick beenden.

SQL Sentry speichert den Verlauf für Top SQL, gesperrte SQL- und Deadlock-Daten. Nutzen Sie die jederzeit verfügbaren Leistungsverlaufsdaten, um sich ein eindeutiges Bild des Geschehens zu verschaffen und Sperren mühelos zu beheben. Sie können sogar Reaktionen für verschiedene SQL-Sperrbedingungen konfigurieren. Bei problematischen Sperren werden Sie benachrichtigt und eine bestimmte Aktion wird gemäß Ihrer Einstellungen ausgeführt.

SQL Sentry zeigt Sperren als Hierarchie an, in der Sie die Beziehungen aller gesperrten und sperrenden SPIDs in einer SQL Server-Sperrenkette ablesen können. An den farbkodierten Knoten in der blockierenden Anweisung können Sie erkennen, ob eine Anweisung ausgeführt wird, abgeschlossen wurde oder blockiert wird.

9906c63b-2fb5-1eaa-14a3-e7447e9d2a8b

 

 

Im SentryOne Event Calendar können Sie Sperren außerdem parallel zu anderen Ereignissen anzeigen. SentryOne bietet unglaubliche Funktionen, mit denen Sie Probleme im Zusammenhang mit SQL Server-Sperren beheben und Problemursachen schnell und effizient identifizieren können.

0f09e062-8f41-27df-a664-a84ca81dd102
Mit der SQL Sentry-Kalenderansicht können Sie viele verschiedene Arten von Ereignissen analysieren, inklusive SQL Server-Sperren.


Das sagen unsere Kunden auf TrustRadius

 

Probeversion starten?

SQLsentry

SQL Sentry-Testversion herunterladen