Sperrenausweitung (Datenbankmodul)
Die Sperrenausweitung ist der Vorgang, bei dem viele differenzierte Sperren zu einer kleineren Anzahl von groben Sperren konvertiert werden, wodurch zwar der Aufwand des Systems reduziert, aber gleichzeitig die Wahrscheinlichkeit von Parallelitätskonflikten erhöht wird.
Wenn Microsoft SQL Server-Datenbankmodul Sperren auf niedriger Ebene einrichtet, werden auch beabsichtigte Sperren für die Objekte eingerichtet, die diese Objekte der niedrigen Ebene enthalten:
- Beim Sperren von Zeilen oder Indexschlüsselbereichen richtet Datenbankmodul eine beabsichtigte Sperre für die Seiten ein, die diese Zeilen oder Schlüssel enthalten.
- Beim Sperren von Seiten richtet Datenbankmodul eine beabsichtigte Sperre für die Objekte der höheren Ebene ein, die diese Seiten enthalten. Zusätzlich zur Einrichtung einer beabsichtigten Objektsperre (OBJECT) für die Tabelle sind beabsichtigte Heap- oder B-Baum-Sperren (HOBT, Heap or B-Tree) erforderlich für:
- den nicht gruppierten Index, wenn die Seiten nicht gruppierte Indexseiten sind,
- den gruppierten Index, wenn die Seiten gruppierte Indexseiten sind (das schließt Datenseiten in Tabellen ein, die einen gruppierten Index haben),
- den Heap von Datenseiten, wenn die Seiten Datenseiten in einer Tabelle sind, die keinen gruppierten Index hat.
Datenbankmodul kann im Rahmen derselben Anweisung sowohl Zeilen- als auch Seitensperren bewirken, um die Anzahl der Sperren zu minimieren und um die Wahrscheinlichkeit zu verringern, dass eine Sperrenausweitung erforderlich wird. So könnte z. B. Datenbankmodul Seitensperren für einen nicht gruppierten Index (sofern ausreichend viele zusammenhängende Schlüssel im Indexknoten ausgewählt sind, um der Abfrage zu entsprechen) und Zeilensperren für die Daten einrichten.
Zum Ausweiten von Sperren versucht Datenbankmodul, die beabsichtigte Sperre für die Tabelle in eine entsprechende vollständige Sperre zu ändern, wodurch eine beabsichtigte exklusive Sperre (IX) zu einer exklusiven Sperre (X) bzw. eine beabsichtigte gemeinsame Sperre (IS) zu einer gemeinsamen Sperre (S) wird. Wenn der Versuch der Sperrenausweitung erfolgreich ist und die vollständige Tabellensperre eingerichtet wird, werden alle durch die Transaktion für den Heap bzw. den Index gehaltenen Sperren des Typs Heap oder B-Baum (HOBT), Seite (PAGE), Schlüsselbereich (KEY) oder Zeilenebene (RID) aufgehoben. Wenn die vollständige Sperre nicht erreicht wird, erfolgt keine Sperrenausweitung, und Datenbankmodul richtet weiterhin Zeilen-, Schlüssel- oder Seitensperren ein.
Datenbankmodul weitet keine Zeilen- oder Schlüsselbereichssperren zu Seitensperren aus, sondern weitet diese direkt zu Tabellensperren aus. In gleicher Weise werden Seitensperren immer zu Tabellensperren ausgeweitet.
Wenn beim Versuch zur Sperrenausweitung ein Fehler erzeugt wird, weil von gleichzeitigen Transaktionen miteinander im Konflikt stehende Sperren gehalten werden, versucht Datenbankmodul die Sperrenausweitung erneut für jeweils weitere 1.250 Sperren, die von der Transaktion eingerichtet werden.
Jedes Ausweitungsereignis wird primär auf der Ebene einer einzelnen Transact-SQL-Anweisung ausgeführt. Wenn das Ereignis startet, versucht Datenbankmodul die Ausweitung aller Sperren, die von der aktuellen Transaktion in einer der Tabellen gehalten werden, auf die durch die aktive Anweisung verwiesen wird, vorausgesetzt, dass diese die Schwellenwertanforderungen für die Ausweitung erfüllt. Wenn das Ausweitungsereignis startet, bevor die Anweisung auf eine Tabelle zugegriffen hat, wird nicht versucht, die Sperren für diese Tabelle auszuweiten. Wenn die Sperrenausweitung erfolgreich ist, werden alle Sperren, die durch die Transaktion in einer früheren Anweisung eingerichtet wurden und zum Zeitpunkt des Ereignisstarts noch immer gehalten werden, ausgeweitet, wenn durch die aktuelle Anweisung auf die Tabelle verwiesen wird und die Tabelle in das Ausweitungsereignis eingeschlossen ist.
Angenommen, eine Sitzung führt z. B. die folgenden Operationen durch:
- Beginnt eine Transaktion.
- Aktualisiert TableA. Damit werden exklusive Zeilensperren in TableA eingerichtet, die so lange gehalten werden, bis die Transaktion abgeschlossen ist.
- Aktualisiert TableB. Damit werden exklusive Zeilensperren in TableB eingerichtet, die so lange gehalten werden, bis die Transaktion abgeschlossen ist.
- Führt eine SELECT-Anweisung aus, die TableA mit TableC verknüpft. Der Abfrageausführungsplan ruft die aus TableA abzurufenden Zeilen auf, bevor die Zeilen aus TableC abgerufen werden.
- Die SELECT-Anweisung löst die Sperrenausweitung aus, während sie die Zeilen aus TableA abruft und bevor sie auf TableC zugegriffen hat.
Bei erfolgreicher Sperrenausweitung werden nur die von der Sitzung für TableA gehaltenen Sperren ausgeweitet. Das schließt sowohl die gemeinsamen Sperren aus der SELECT-Anweisung als auch die exklusiven Sperren aus der vorherigen UPDATE-Anweisung ein. Während bei der Beurteilung, ob die Sperrenausweitung erfolgen soll, nur die Sperren berücksichtigt werden, die die Sitzung in TableA für die SELECT-Anweisung eingerichtet hat, werden bei erfolgreicher Ausweitung alle von der Sitzung in TableA gehaltenen Sperren zu einer exklusiven Sperre für die Tabelle ausgeweitet, und alle anderen Sperren mit geringerer Granularität, einschließlich beabsichtigter Sperren, für TableA werden aufgehoben.
Es wird nicht versucht, die Sperren für TableB auszuweiten, weil es in der SELECT-Anweisung keinen aktiven Verweis auf TableB gibt. Desgleichen wird nicht versucht, die Sperren für TableC auszuweiten, weil zum Zeitpunkt der Ausweitung noch kein Zugriff auf die Tabelle erfolgt war.
Schwellenwerte für die Sperrenausweitung
Die Sperrenausweitung wird zu einem der folgenden Zeitpunkte ausgelöst:
- wenn eine einzelne Transact-SQL-Anweisung mindestens 5.000 Sperren für eine einzelne Tabelle oder einen einzelnen Index eingerichtet hat,
- wenn die Anzahl der Sperren in einer Instanz von Datenbankmodul die Arbeitsspeicherkapazität oder die Konfigurationsschwellenwerte übersteigt.
Wenn die Sperrenausweitung aufgrund von Sperrkonflikten nicht möglich ist, löst Datenbankmodul die Sperrenausweitung in regelmäßigen Abständen aus, sobald jeweils 1.250 neue Sperren eingerichtet werden.
Ausweitungsschwellenwert für eine Transact-SQL-Anweisung
Die Sperrenausweitung wird ausgelöst, wenn eine Transact-SQL-Anweisung mindestens 5.000 Sperren auf einem einzelnen Verweis einer Tabelle oder eines Indexes einrichtet (bzw. bei einer partitionierten Tabelle auf einem einzelnen Verweis einer Tabellen- oder Indexpartition). So wird z. B. keine Sperrenausweitung ausgelöst, wenn eine Anweisung 3.000 Sperren in einem Index und 3.000 Sperren in einem anderen Index derselben Tabelle einrichtet. So wird auch dann keine Sperrenausweitung ausgelöst, wenn eine Anweisung eine Reflexivverknüpfung zu einer Tabelle enthält und jeder Verweis auf die Tabelle lediglich 3.000 Sperren in der Tabelle einrichtet.
Die Sperrenausweitung tritt nur für Tabellen auf, auf die zum Zeitpunkt der Ausweitungsauslösung bereits zugegriffen wurde. Angenommen, eine einzelne SELECT-Anweisung ist eine Verknüpfung, die auf drei Tabellen in genau dieser Reihenfolge zugreift: TableA, TableB und TableC. Die Anweisung richtet 3.000 Zeilensperren im gruppierten Index für TableA ein und mindestens 5.000 Zeilensperren im gruppierten Index für TableB. Auf TableC wurde jedoch noch nicht zugegriffen. Wenn Datenbankmodul erkennt, dass die Anweisung mindestens 5.000 Zeilensperren in TableB eingerichtet hat, wird versucht, sämtliche von der aktuellen Transaktion in TableB gehaltenen Sperren auszuweiten. Es wird auch versucht, sämtliche von der aktuellen Transaktion in TableA gehaltenen Sperren auszuweiten, da aber die Anzahl der Sperren für TableA < 5.000 ist, ist die Ausweitung nicht erfolgreich. Es wird keine Sperrenausweitung für TableC versucht, da zum Zeitpunkt der Ausweitung noch kein Zugriff auf die Tabelle erfolgt war.
Ausweitungsschwellenwert für eine Instanz des Datenbankmoduls
Immer wenn die Anzahl der Sperren den Speicherschwellenwert für die Sperrenausweitung überschreitet, löst Datenbankmodul die Sperrenausweitung aus. Der Speicherschwellenwert richtet sich nach der Einstellung der Konfigurationsoption locks:
- Wenn die Option locks auf ihre Standardeinstellung 0 festgelegt ist, wird der Schwellenwert der Sperrenausweitung erreicht, wenn der von Sperrobjekten belegte Speicheranteil 24 % des von Datenbankmodul verwendeten Speichers (ausschließlich AWE-Speicher) beträgt. Die Datenstruktur zur Darstellung einer Sperre ist ungefähr 100 Byte lang. Dieser Schwellenwert ist dynamisch, da Datenbankmodul je nach wechselnder Arbeitsauslastung dynamisch Speicher reserviert und freigibt.
- Wenn die locks-Option einen von 0 abweichenden Wert hat, beträgt der Schwellenwert für die Sperrenauswertung 40 % des Wertes der locks-Option (oder weniger, wenn nicht genügend Arbeitsspeicher verfügbar ist).
Datenbankmodul kann jede aktive Anweisung aus jeder Sitzung zur Ausweitung auswählen, und für jeweils 1.250 neue Sperren wählt es Anweisungen zur Ausweitung aus, so lange der in der Instanz für Sperren beanspruchte Arbeitsspeicher oberhalb des Schwellenwerts bleibt.
Ausweiten von gemischten Sperrentypen
Wenn die Sperrenausweitung stattfindet, ist die für den Heap oder Index ausgewählte Sperre stark genug, um die Anforderungen der am restriktivsten Sperre auf unterer Ebene zu erfüllen.
Angenommen, eine Sitzung
- beginnt eine Transaktion,
- aktualisiert eine Tabelle, die einen gruppierten Index enthält,
- gibt eine SELECT-Anweisung aus, die auf dieselbe Tabelle verweist.
Die UPDATE-Anweisung richtet die folgenden Sperren ein:
- Exklusive Sperren (X) für die aktualisierten Datenzeilen.
- Beabsichtigte exklusive Sperren (IX) für die gruppierten Indexseiten, die diese Zeilen enthalten.
- Eine IX-Sperre für den gruppierten Index und eine weitere für die Tabelle.
Die SELECT-Anweisung richtet die folgenden Sperren ein:
- Gemeinsame Sperren (S) für alle gelesenen Datenzeilen, außer wenn die Zeile bereits durch eine X-Sperre aus der UPDATE-Anweisung geschützt wurde.
- Beabsichtigte gemeinsame Sperren (IS) für alle gruppierten Indexseiten, die diese Zeilen enthalten, außer wenn die Seite bereits durch eine IX-Sperre geschützt wurde.
- Keine Sperre für den gruppierten Index oder die Tabelle, da diese bereits durch IX-Sperren geschützt sind.
Wenn die SELECT-Anweisung ausreichend Sperren eingerichtet hat, um die Sperrenausweitung auszulösen, und die Ausweitung erfolgreich ist, wird die IX-Sperre für die Tabelle in eine X-Sperre umgewandelt, und sämtliche Zeilen-, Seiten- und Indexsperren werden aufgehoben. Sowohl die Aktualisierungen als auch Lesevorgänge sind durch die X-Sperre für die Tabelle geschützt.
Verringern von Sperrung und Ausweitung
In den meisten Fällen erzielt Datenbankmodul die beste Leistung, wenn es mit seinen Standardeinstellungen zur Sperrung und zur Sperrenausweitung arbeitet. Wenn eine Instanz von Datenbankmodul jedoch viele Sperren erzeugt und häufige Sperrenausweitungen durchführt, sollten Sie durch folgende Maßnahmen versuchen, das Ausmaß der Sperrung zu verringern:
- Verwenden einer Isolationsstufe, die keine gemeinsamen Sperren für Lesevorgänge erzeugt.
- READ COMMITTED-Isolationsstufe, wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON gesetzt ist.
- SNAPSHOT-Isolationsstufe.
- READ UNCOMMITTED-Isolationsstufe. Diese kann nur für Systeme verwendet werden, die mit Dirty Reads arbeiten können.
- Verwenden der Tabellenhinweise PAGLOCK oder TABLOCK, damit Datenbankmodul Seiten-, Heap- oder Indexsperren anstelle von Zeilensperren verwendet. Die Verwendung dieser Option führt jedoch verstärkt zu Problemen, weil Benutzer den Zugriff anderer Benutzer auf dieselben Daten blockieren, und darf daher nicht in Systemen mit mehr als einigen wenigen gleichzeitigen Benutzern verwendet werden.
Sie können auch die Ablaufverfolgungsflags 1211 und 1224 verwenden, um alle oder einige Sperrenausweitungen zu deaktivieren. Weitere Informationen finden Sie unter Ablaufverfolgungsflags (Transact-SQL). Überwachen Sie außerdem die Sperrenausweitung durch Verwendung des SQL Server Profiler-Ereignisses Lock:Escalation. Siehe Verwenden von SQL Server Profiler.
Siehe auch
Konzepte
Isolationsstufen im Datenbankmodul
Sperrengranularität und -hierarchien
Sperrmodi
Kompatibilität von Sperren (Datenbankmodul)
locks (Option)
Andere Ressourcen
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Tabellenhinweis (Transact-SQL)
Tabellen- und Indexarchitektur