Anpassen der Sperren für einen Index
SQL Server Database Engine (Datenbankmodul) verwendet eine dynamische Sperrstrategie, die für Abfragen in den meisten Fällen automatisch die am besten geeignete Granularität der Sperren auswählt. Es empfiehlt sich, die Standardsperrebenen, die Seiten- und Zeilensperren aufweisen, nicht außer Kraft zu setzen, außer wenn Tabellen- oder Indexzugriffsmuster klar verständlich und konsistent sind und ein Ressourcenkonfliktproblem gelöst werden soll. Das Außerkraftsetzen einer Sperrebene kann den gleichzeitigen Zugriff auf eine Tabelle oder einen Index erheblich behindern. So kann zum Beispiel die Angabe von Sperren nur auf Tabellenebene in einer großen Tabelle, die häufig verwendet wird, Engpässe verursachen, da Benutzer vor dem Zugreifen auf die Tabelle auf die Aufhebung der Sperre auf Tabellenebene warten müssen.
In einigen Fällen kann das Untersagen von Seiten- oder Zeilensperren nützlich sein, wenn die Zugriffsmuster klar verständlich und konsistent sind. Ein Beispiel dafür ist eine Datenbankanwendung, die eine wöchentlich in einem Batchverarbeitungsprozess aktualisierte Nachschlagetabelle verwendet. Gleichzeitige Leser greifen auf die Tabelle mit einer freigegebenen Sperre (S) zu, und die wöchentliche Aktualisierung mithilfe der Batchverarbeitung greift auf die Tabelle mit einer exklusiven Sperre (X) zu. Das Ausschalten der Seiten- und Zeilensperre in der Tabelle reduziert den Sperraufwand der gesamten Woche, da Leser gleichzeitig über freigegebene Tabellensperren auf die Tabelle zugreifen können. Wenn der Stapelverarbeitungsauftrag ausgeführt wird, kann die Aktualisierung effizient abgeschlossen werden, da sie eine exklusive Tabellensperre erhält.
Die Deaktivierung der Seiten- und Zeilensperre kann akzeptabel sein, da die wöchentliche Aktualisierung mithilfe der Batchverarbeitung den Zugriff gleichzeitiger Leser auf die Tabelle während der Durchführung der Aktualisierung blockiert. Wenn im Stapelverarbeitungsauftrag nur einige Zeilen oder Seiten geändert werden, können Sie die Sperrebene ändern, um das Sperren auf Zeilen- oder Seitenebene zu ermöglichen. Dies ermöglicht anderen Sitzungen aus der Tabelle zu lesen, ohne blockiert zu werden. Wenn ein Stapelverarbeitungsauftrag viele Aktualisierungen enthält, ist eine exklusive Sperre der Tabelle eventuell die beste Möglichkeit, um sicherzustellen, dass der Stapelverarbeitungsauftrag effizient abgeschlossen wird.
Gelegentlich tritt ein Deadlock auf, wenn zwei gleichzeitige Vorgänge Zeilensperren in der gleichen Tabelle anfordern und dann blockieren, da beide die Seite sperren müssen. Das Untersagen von Zeilensperren zwingt einen der Vorgänge zu warten und vermeidet den Deadlock.
Die Granularität der Sperren für einen Index kann mithilfe der Anweisungen CREATE INDEX und ALTER INDEX festgelegt werden. Die Sperreinstellungen gelten für die Indexseiten und die Tabellenseiten. Außerdem können die Anweisungen CREATE TABLE und ALTER TABLE verwendet werden, um Granularität der Sperren von PRIMARY KEY- und UNIQUE-Einschränkungen festzulegen. Aus Gründen der Abwärtskompatibilität kann auch die gespeicherte Systemprozedur sp_indexoption zum Festlegen der Granularität verwendet werden. Zur Anzeige der aktuellen Sperroption für einen bestimmten Index verwenden Sie die INDEXPROPERTY-Funktion. Es ist möglich, Sperren auf Seitenebene, auf Zeilenebene oder eine Kombination von Sperren auf Seiten- und Zeilenebene für einen bestimmten Index nicht zuzulassen.
Nicht zugelassene Sperren |
Indexzugriff durch |
|---|---|
Seitenebene |
Sperren auf Zeilen- und Tabellenebene |
Zeilenebene |
Sperren auf Seiten- und Tabellenebene |
Seiten- und Zeilenebene |
Sperren auf Tabellenebene |