Sperrenausweitung (Datenbankmodul)
Die Sperrenausweitung ist der Vorgang, bei dem viele differenzierte Sperren zu einer kleineren Anzahl von groben Sperren konvertiert werden. Dies reduziert zwar den Systemaufwand, erhöht jedoch gleichzeitig die Wahrscheinlichkeit von Parallelitätskonflikten erhöht.
Wenn SQL Server Database Engine (Datenbankmodul)-Sperren auf niedriger Ebene eingerichtet werden, werden auch beabsichtigte Sperren für die Objekte eingerichtet, die diese Objekte der niedrigen Ebene enthalten:
Beim Sperren von Zeilen oder Indexschlüsselbereichen richtet Database Engine (Datenbankmodul) eine beabsichtigte Sperre für die Seiten ein, die diese Zeilen oder Schlüssel enthalten.
Beim Sperren von Seiten richtet Database Engine (Datenbankmodul) eine beabsichtigte Sperre für die Objekte der höheren Ebene ein, die diese Seiten enthalten. Zusätzlich zur beabsichtigten Sperre für das Objekt werden beabsichtigte Sperren für die folgenden Objekte angefordert:
Seiten auf Blattebene von nicht gruppierten Indizes
Datenseiten von gruppierten Indizes
Heap mit Datenseiten
Database Engine (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. Database Engine (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 Database Engine (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 freigegebene Sperre (IS) zu einer freigegebenen 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, Seite (PAGE) oder Zeilenebene (RID) aufgehoben. Wenn die vollständige Sperre nicht erreicht wird, erfolgt keine Sperrenausweitung, und Database Engine (Datenbankmodul) richtet weiterhin Zeilen-, Schlüssel- oder Seitensperren ein.
Database Engine (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. In SQL Server 2008 kann das Sperren von partitionierten Tabellen auf die HoBT-Ebene für die zugehörige Partition statt auf die Tabellensperre ausgeweitet werden. Eine Sperre auf HoBT-Ebene führt nicht notwendigerweise dazu, dass die ausgerichteten HoBTs für die Partition gesperrt werden.
.gif "Hinweis") Hinweis |
|---|
Sperren auf HoBT-Ebene führen in der Regel zur Erhöhung der Parallelität, erhöhen jedoch auch die Möglichkeit von Deadlocks, wenn Transaktionen, die verschiedene Partitionen sperren, jeweils ihre exklusiven Sperren auf andere Partitionen ausweiten möchten. In seltenen Fällen wird mit der TABLE-Sperrgranularität eine bessere Leistung erzielt. |
Wenn beim Versuch zur Sperrenausweitung ein Fehler erzeugt wird, weil von gleichzeitigen Transaktionen miteinander im Konflikt stehende Sperren gehalten werden, versucht Database Engine (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 Database Engine (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 freigegebenen 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, wenn sie für die Tabelle nicht deaktiviert ist, mit der ALTER TABLE SET LOCK_ESCALATION-Option ausgelöst, und wenn eine der folgenden Bedingungen zutrifft:
Eine einzelne Transact-SQL-Anweisung ruft mindestens 5.000 Sperren für eine einzelne nicht partitionierte Tabelle oder einen Index ab.
Eine einzelne Transact-SQL-Anweisung ruft mindestens 5.000 Sperren für eine einzelne Partition einer partitionierten Tabelle ab, und die ALTER TABLE SET LOCK_ESCALATION-Option ist auf AUTO festgelegt.
Die Anzahl von Sperren in einer Instanz von Database Engine (Datenbankmodul) überschreitet den Arbeitsspeicher oder die Konfigurationsschwellenwerte.
Wenn die Sperrenausweitung aufgrund von Sperrkonflikten nicht möglich ist, löst Database Engine (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 Database Engine (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 Database Engine (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 Database Engine (Datenbankmodul) verwendeten Speichers (ausschließlich AWE-Speicher) beträgt. Die Datenstruktur zur Darstellung einer Sperre ist ungefähr 100 Bytes lang. Dieser Schwellenwert ist dynamisch, da Database Engine (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 Werts der locks-Option (oder weniger, wenn nicht genügend Arbeitsspeicher verfügbar ist).
Database Engine (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:
Freigegebene 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 freigegebene 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 Database Engine (Datenbankmodul) die beste Leistung, wenn es mit seinen Standardeinstellungen zur Sperrung und zur Sperrenausweitung arbeitet. Wenn eine Instanz von Database Engine (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 freigegebenen 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.
| Hinweis |
|---|
Die Änderung der Isolationsstufe wirkt sich auf alle Tabellen der Instanz von Database Engine (Datenbankmodul) aus. |
Verwenden der Tabellenhinweise PAGLOCK oder TABLOCK, damit Database Engine (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.
Verwenden Sie für partitionierte Tabellen die LOCK_ESCALATION-Option von ALTER TABLE, um Sperren auf die HoBT-Ebene auszuweiten statt auf die Tabelle, oder um die Sperrenausweitung zu deaktivieren.
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