Artikel
09/24/2010

Schlüsselbereichssperren

Schlüsselbereichssperren schützen einen Bereich von Zeilen, die implizit in ein Recordset eingeschlossen wurden, das von einer Transact-SQL-Anweisung gelesen wird; dies geschieht bei Verwendung der Isolationsstufe für die serialisierbare Transaktion. Für die serialisierbare Isolationsstufe muss jede Abfrage, die während einer Transaktion ausgeführt wird, dieselben Zeilen erhalten, wenn sie im Rahmen der Transaktion ausgeführt wird. Durch eine Schlüsselbereichssperre wird diese Anforderung geschützt, indem verhindert wird, dass von anderen Transaktionen neue Zeilen eingefügt werden, deren Schlüssel dem Schlüsselbereich zugehörig sind, die von der serialisierbaren Transaktion gelesen werden.

Durch Schlüsselbereichssperren werden Phantomlesezugriffe verhindert. Indem die Schlüsselbereiche zwischen Zeilen geschützt werden, wird außerdem verhindert, dass es zu Phantomeinfügungsvorgängen in Datensätzen kommt, auf die eine Transaktion zugreift.

Eine Schlüsselbereichssperre wird für einen Index platziert; auf diese Weise wird ein Start- und Endschlüsselwert angegeben. Durch diese Sperre wird jeglicher Versuch blockiert, eine Zeile mit einem Schlüsselwert einzufügen, zu aktualisieren oder zu löschen, der dem Bereich zugehörig ist, da von diesen Vorgängen zunächst eine Sperre für den Index eingerichtet werden müsste. Eine serialisierbare Transaktion könnte beispielsweise eine SELECT-Anweisung ausgeben, die sämtliche Zeilen liest, deren Schlüsselwerte zwischen AAA und CZZ liegen. Eine Schlüsselbereichssperre für die Schlüsselwerte im Bereich von AAA bis CZZ verhindert, dass andere Transaktionen Zeilen mit Schlüsselwerten in diesem Bereich einfügen, beispielsweise ADG, BBD oder CAL.

Schlüsselbereichssperrmodi

Zu Schlüsselbereichssperren gehören eine Bereichs- und eine Zeilenkomponente, die im Bereichszeilenformat angegeben werden.

Bereich stellt den Sperrmodus dar, der den Bereich zwischen zwei aufeinander folgenden Indexeinträgen schützt.
Zeile stellt den Sperrmodus dar, der den Indexeintrag schützt.

Modus stellt den kombinierten Sperrmodus dar, der verwendet wird. Schlüsselbereichssperrmodi setzen sich aus zwei Teilen zusammen. Der erste gibt den Sperrtyp wieder, der zum Sperren des Indexbereichs (RangeT) verwendet wird, und der zweite gibt den Sperrtyp wieder, der zum Sperren eines bestimmten Schlüssels (K) verwendet wird. Die beiden Teile sind durch einen Bindestrich (-) miteinander verbunden, beispielsweise RangeT-K.

Bereich	Zeile	Mode	Beschreibung
RangeS	S	RangeS-S	Freigegebene Sperren für Bereich und Ressource, serialisierbarer Bereichsscan
RangeS	U	RangeS-U	Freigegebene Sperre für Bereich und Aktualisierungssperre für Ressource; serialisierbarer Aktualisierungsscan.
RangeI	Null	RangeI-N	Einfügungssperre für Bereich und NULL-Sperre für Ressource; wird verwendet, um Bereiche vor dem Einfügen eines neuen Schlüssels in einen Index zu testen.
RangeX	X	RangeX-X	Exklusive Sperren für Bereich und Ressource; wird beim Aktualisieren eines Schlüssels in einem Bereich verwendet.

Hinweis
Der interne NULL-Sperrmodus ist mit allen anderen Sperrmodi kompatibel.

Schlüsselbereichssperrmodi haben eine Kompatibilitätsmatrix, die zeigt, welche Sperren mit anderen Sperren, die für überlappende Schlüssel und Bereiche eingerichtet wurden, kompatibel sind. Die vollständige Matrix der Sperrkompatibilität finden Sie unter Kompatibilität von Sperren.

	Vorhandener erteilter Modus
Angeforderter Modus	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Shared (S)	Ja	Ja	Nein	Ja	Ja	Ja	Nein
Update (U)	Ja	Nein	Nein	Ja	Nein	Ja	Nein
Exclusive (X)	Nein	Nein	Nein	Nein	Nein	Ja	Nein
RangeS-S	Ja	Ja	Nein	Ja	Ja	Nein	Nein
RangeS-U	Ja	Nein	Nein	Ja	Nein	Nein	Nein
RangeI-N	Ja	Ja	Ja	Nein	Nein	Ja	Nein
RangeX-X	Nein	Nein	Nein	Nein	Nein	Nein	Nein

Konvertierungssperren

Konvertierungssperren werden erstellt, wenn eine Schlüsselbereichssperre eine andere Sperre überlappt.

Sperre 1	Sperre 2	Konvertierungssperre
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Konvertierungssperren lassen sich für eine kurze Zeitdauer unter verschiedenen komplexen Bedingungen beobachten, so gelegentlich bei der Ausführung gleichzeitiger Prozesse.

Serialisierbarer Bereichsscan, Singleton-Abruf, Löschen und Einfügen

Durch Schlüsselbereichssperren wird sichergestellt, dass folgende Vorgänge serialisierbar sind:

Bereichsscanabfrage
Singleton-Abruf einer nicht vorhandenen Zeile
Löschvorgang
Einfügungsvorgang

Folgende Bedingungen müssen erfüllt werden, ehe Schlüsselbereichssperren verwendet werden können:

Die Isolationsstufe der Transaktion muss auf SERIALIZABLE festgelegt sein.
Der Abfrageprozessor muss zum Implementieren des Bereichsfilterprädikäts verwendet werden. Von der WHERE-Klausel in einer SELECT-Anweisung könnte beispielsweise eine Bereichsbedingung mit diesem Prädikat eingerichtet werden: ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. Eine Schlüsselbereichssperre kann nur eingerichtet werden, wenn ColumnX durch einen Indexschlüssel abgedeckt ist.

Beispiele

Die nachfolgende Tabelle und der nachfolgende Index dienen als Grundlage für die Beispiele für Schlüsselbereichssperren, die nachfolgend aufgeführt sind.

Datenbanktabelle mit Index als B-Struktur

Bereichsscanabfrage

Um sicherzustellen, dass eine Bereichsscanabfrage serialisierbar ist, sollte dieselbe Abfrage immer dieselben Ergebnisse zurückgeben, wenn sie innerhalb derselben Transaktion ausgeführt wird. Neue Zeilen dürfen innerhalb der Bereichsscanabfrage nicht von anderen Transaktionen eingefügt werden, da diese sonst zu Phantomeinfügungen werden. In der nachfolgenden Abfrage werden beispielsweise die Tabelle und der Index in der obigen Abbildung verwendet:

SELECT name
    FROM mytable
    WHERE name BETWEEN 'A' AND 'C';

Es werden Schlüsselbereichssperren auf die Indexeinträge angewendet, die dem Datenzeilenbereich entsprechen, in dem der Name zwischen den Werten Adam und Dale liegt. Dadurch wird verhindert, dass neue Zeilen, die der vorhergehenden Abfrage entsprechen, hinzugefügt oder gelöscht werden. Obwohl Adam der erste Name in diesem Bereich ist, wird durch die Schlüsselbereichssperre mit dem Modus RangeS-S für diesen Indexeintrag sichergestellt, dass keine neuen Namen mit dem Anfangsbuchstaben A vor dem Namen Adam eingefügt werden können, beispielsweise Abigail. Entsprechend wird durch die Schlüsselbereichssperre mit dem Modus RangeS-S für den Indexeintrag für Dale sichergestellt, dass keine neuen Namen mit dem Anfangsbuchstaben C nach dem Namen Carlos eingefügt werden können, beispielsweise Clive.

Hinweis
Die Anzahl der aufrechterhaltenen Sperren vom Typ RangeS-S ist n+1; hierbei ist n die Anzahl der Zeilen, die der Abfrage entsprechen.

Singleton-Abruf nicht vorhandener Daten

Wenn eine Abfrage in einer Transaktion versucht, eine nicht vorhandene Zeile auszuwählen, muss die Abfrage, wenn sie zu einem späteren Zeitpunkt innerhalb derselben Transaktion erneut ausgegeben wird, zu demselben Ergebnis führen. Es darf für keine andere Transaktion zulässig sein, diese nicht vorhandene Zeile einzufügen. Angenommen, die folgende Abfrage wird ausgeführt:

SELECT name
    FROM mytable
    WHERE name = 'Bill';

Es wird eine Schlüsselbereichssperre für den Indexeintrag platziert, der dem Namensbereich von Ben bis Bing entspricht, da der Name Bill zwischen den beiden aufeinander folgenden Indexeinträgen eingefügt würde. Die Schlüsselbereichssperre mit dem Modus RangeS-S wird für den Indexeintrag Bing platziert. Dadurch wird verhindert, dass andere Transaktionen Werte, wie etwa Bill, zwischen die Indexeinträge Ben und Bing einfügen.

Löschvorgang

Wenn ein Wert in einer Transaktion gelöscht wird, muss der Bereich, in dem der Wert liegt, nicht für die gesamte Dauer der Transaktion, die den Löschvorgang ausführt, gesperrt werden. Die Serialisierbarkeit wird bereits dann aufrechterhalten, wenn der gelöschte Schlüsselwert bis zum Ende der Transaktion gesperrt wird. Angenommen, folgende DELETE-Anweisung wird ausgeführt:

DELETE mytable
    WHERE name = 'Bob';

Eine exklusive Sperre (X) wird für den Indexeintrag platziert, der dem Namen Bob entspricht. Andere Transaktionen können Werte vor oder nach dem gelöschten Wert Bob einfügen oder löschen. Eine Transaktion, die versucht, den Wert Bob zu lesen, einzufügen oder zu löschen, wird jedoch so lange blockiert, bis für die löschende Transaktion entweder ein Commit oder ein Rollback ausgeführt wird.

Das Löschen des Bereichs kann mithilfe von drei grundlegenden Sperrmodi ausgeführt werden: Zeilen-, Seiten- oder Tabellensperre. Die Verwendung der Zeilen-, Seiten- oder Tabellensperren wird vom Abfrageoptimierer festgelegt oder kann vom Benutzer über Optimierungshinweise, wie ROWLOCK, PAGLOCK oder TABLOCK, angegeben werden. Wenn PAGLOCK oder TABLOCK verwendet wird, hebt Database Engine (Datenbankmodul) umgehend die Zuordnung einer Indexseite auf, wenn sämtliche Zeilen dieser Seite gelöscht werden. Wenn hingegen ROWLOCK verwendet wird, werden sämtliche Zeilen lediglich als gelöscht markiert und zu einem späteren Zeitpunkt mithilfe eines Hintergrundtasks von der Indexseite entfernt.

Einfügungsvorgang

Wenn ein Wert in einer Transaktion eingefügt wird, muss der Bereich, in dem der Wert liegt, nicht für die gesamte Dauer der Transaktion, die den Einfügungsvorgang ausführt, gesperrt werden. Die Serialisierbarkeit wird bereits dann aufrechterhalten, wenn der eingefügte Schlüsselwert bis zum Ende der Transaktion gesperrt wird. Angenommen, folgende INSERT-Anweisung wird ausgeführt:

INSERT mytable VALUES ('Dan');

Für den Indexeintrag, der dem Namen David entspricht, wird die Schlüsselbereichssperre mit dem Modus RangeI-N platziert, um den Bereich zu testen. Wenn die Sperre erteilt wird, wird Dan eingefügt, und für den Wert Dan wird eine exklusive Sperre (X) platziert. Die Schlüsselbereichssperre mit dem Modus RangeI-N ist nur notwendig, um den Bereich zu testen, und wird nicht für die Dauer der Transaktion aufrechterhalten, die den Einfügungsvorgang ausführt. Andere Transaktionen können Werte vor oder nach dem eingefügten Wert Dan einfügen oder löschen. Eine Transaktion, die versucht, den Wert Dan zu lesen, einzufügen oder zu löschen, wird jedoch so lange gesperrt, bis für die einfügende Transaktion entweder ein Commit oder ein Rollback ausgeführt wird.