Handbuch zu Transaktionssperren und Zeilenversionsverwaltung

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

In jeder Datenbank führt die fehlerhafte Verwaltung von Transaktionen bei Systemen mit zahlreichen Benutzern häufig zu Konflikten und Leistungsproblemen. Mit steigender Anzahl von Benutzern, die auf die Daten zugreifen, wird der Einsatz von Anwendungen, die Transaktionen effizient verwenden, immer wichtiger. In diesem Leitfaden werden Mechanismen für Sperren und die Zeilenversionsverwaltung beschrieben, die von der SQL Server-Datenbank-Engine verwendet werden, um die physische Integrität jeder Transaktion sicherzustellen. Darüber hinaus erfahren Sie, wie Transaktionen von Anwendungen effizient gesteuert werden.

Hinweis

Optimierte Sperrung ist ein 2023 eingeführtes Feature der Datenbank-Engine, das den Speicherbedarf für Sperren und die Anzahl der für gleichzeitige Schreibvorgänge erforderlichen Sperren drastisch reduziert. Dieser Artikel wurde aktualisiert und beschreibt nun die SQL Server-Datenbank-Engine mit und ohne optimierte Sperrung. Derzeit ist die optimierte Sperrung nur in Azure SQL-Datenbank verfügbar.

Für die optimierte Sperrung wurden einige Abschnitte dieses Artikels umfangreich aktualisiert, darunter:

Grundlagen zu Transaktionen

Eine Transaktion ist eine Folge von Operationen, die als einzelne logische Arbeitseinheit ausgeführt wird. Eine logische Arbeitseinheit muss vier Eigenschaften aufweisen, die als ACID-Eigenschaften (Atomicity, Consistency, Isolation und Durability; Unteilbarkeit, Konsistenz, Isolation und Beständigkeit) bezeichnet werden, um als Transaktion zu gelten.

Unteilbarkeit
Eine Transaktion muss eine unteilbare Arbeitseinheit sein; entweder werden alle durch sie vorgesehenen Datenänderungen oder keine der Änderungen ausgeführt.

Konsistenz
Am Ende einer Transaktion müssen sich alle Daten in einem konsistenten Status befinden. In einer relationalen Datenbank müssen alle Regeln auf die Änderungen der Transaktion angewendet werden, um die Integrität aller Daten zu erhalten. Alle internen Datenstrukturen, wie B-Struktur-Indizes oder doppelt verknüpfte Listen, müssen am Ende der Transaktion richtig sein.

Hinweis

In der SQL Server-Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert SQL Server eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Isolation
Änderungen, die von gleichzeitigen Transaktionen ausgeführt werden, müssen von allen Änderungen, die von anderen gleichzeitigen Transaktionen ausgeführt werden, isoliert sein. Einer Transaktion stehen Daten entweder in dem Status zur Verfügung, in dem sie sich vor der Änderung durch eine andere gleichzeitige Transaktion befanden, oder in dem Status nach Beenden der zweiten Transaktion, jedoch nicht in einem Zwischenstatus. Dies wird als Serialisierbarkeit bezeichnet, da sich daraus die Fähigkeit ableitet, die Ausgangsdaten erneut zu laden und eine Reihe von Transaktionen erneut durchzuführen, um schließlich die Daten in dem Status zu erhalten, der vorlag, nachdem die ursprünglichen Transaktionen ausgeführt wurden.

Dauerhaftigkeit
Nach Abschluss einer voll beständigen Transaktion sind ihre Auswirkungen im System dauerhaft. Die Änderungen bleiben auch bei einem Systemfehler persistent. SQL Server 2014 (12.x) und höher unterstützt verzögerte dauerhafte Transaktionen. Für verzögerte dauerhafte Transaktionen wird ein Commit ausgeführt, bevor der Transaktionsprotokolldatensatz auf dem Datenträger beibehalten wird. Weitere Informationen zur Dauerhaftigkeit verzögerter Transaktionen finden Sie im Artikel Steuern der Transaktionsdauerhaftigkeit.

SQL-Programmierer sind dafür verantwortlich, Transaktionen an Punkten zu starten und zu beenden, die die logische Konsistenz der Daten erzwingen. Der Programmierer muss die Sequenz der Datenänderungen so definieren, dass die Daten hinsichtlich der Geschäftsregeln der Organisation in konsistentem Status bleiben. Daraufhin schließt der Programmierer diese Änderungsanweisungen in eine einzelne Transaktion ein, sodass die SQL Server-Datenbank-Engine die physische Integrität der Transaktion erzwingen kann.

Es ist die Aufgabe eines Unternehmensdatenbank-Systems, wie z. B. einer Instanz von der SQL Server-Datenbank-Engine, Mechanismen bereitzustellen, durch die die physische Integrität aller Transaktionen sichergestellt wird. Die SQL Server-Datenbank-Engine verfügt über:

  • Sperrvorrichtungen, durch die die Isolation jeder Transaktion erhalten bleibt.

  • Protokolliervorrichtungen stellen die Beständigkeit von Transaktionen sicher. Bei vollständig dauerhaften Transaktionen wird der Protokolldatensatz vor dem Transaktionscommit auf den Datenträger geschrieben. Bei einem Fehler der Serverhardware, des Betriebssystems oder der Instanz von der SQL Server-Datenbank-Engine selbst verwendet die Instanz nach dem Neustart die Transaktionsprotokolle, um automatisch einen Rollback für alle nicht beendeten Transaktionen auszuführen, der sie auf ihren Status vor dem Systemfehler zurücksetzt. Für verzögerte dauerhafte Transaktionen wird ein Commit ausgeführt, bevor der Transaktionsprotokolldatensatz auf dem Datenträger gespeichert wird. Solche Transaktionen gehen möglicherweise verloren, wenn ein Systemfehler auftritt, bevor die Protokolldatensätze auf dem Datenträger gespeichert werden. Weitere Informationen zur Dauerhaftigkeit verzögerter Transaktionen finden Sie im Artikel Steuern der Transaktionsdauerhaftigkeit.

  • Funktionen der Transaktionsverwaltung, die die Unteilbarkeit und Konsistenz der Transaktionen erzwingen. Nach Beginn einer Transaktion muss die Transaktion erfolgreich (mit einem Commit) beendet werden, da die SQL Server-Datenbank-Engine sonst alle Datenänderungen rückgängig macht, die seit Beginn der Transaktion ausgeführt wurden. Dieser Vorgang wird als Rollback einer Transaktion bezeichnet, da die Daten in den Zustand zurückversetzt werden, der vor den Änderungen gültig war.

Steuern von Transaktionen

Transaktionen werden von Anwendungen hauptsächlich durch Angeben der Zeitpunkte für Transaktionsbeginn und -ende gesteuert. Die Steuerung kann über Transact-SQL-Anweisungen oder Datenbank-API-Funktionen erfolgen. Das System muss auch in der Lage sein, Fehler richtig zu behandeln, die eine Transaktion vor deren Abschluss beenden. Weitere Informationen finden Sie unter Transaktionen, Ausführen von Transaktionen in ODBC und Transaktionen in SQL Server Native Client.

Standardmäßig werden Transaktionen auf der Verbindungsebene verwaltet. Wenn eine Transaktion über eine Verbindung gestartet wird, sind alle Transact-SQL-Anweisungen, die über diese Verbindung ausgeführt werden, Teil der Transaktion, bis diese endet. In einer Sitzung mit mehreren aktiven Resultsets (MARS) wird jedoch eine explizite oder implizite Transact-SQL-Transaktion zu einer Transaktion im Bereich des Batchs, die auf der Batchebene verwaltet wird. Wenn der Batch abgeschlossen wird, nimmt SQL Server automatisch einen Rollback vor, wenn für die Transaktion im Bereich des Batchs kein Commit oder Rollback erfolgt ist. Weitere Informationen finden Sie unter Verwenden von Multiple Active Result Sets (MARS).

Starten von Transaktionen

Mithilfe von API-Funktionen und Transact-SQL-Anweisungen können Sie Transaktionen in einer Instanz der SQL Server-Datenbank-Engine als explizite, implizite oder Autocommit-Transaktionen starten.

Explizite Transaktionen
Eine explizite Transaktion ist eine Transaktion, in der Sie sowohl den Beginn als auch das Ende der Transaktion über eine API-Funktion oder durch Ausgabe einer der Transact-SQL-Anweisungen BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK oder der Transact-SQL-Anweisungen ROLLBACK TRANSACTION oder ROLLBACK WORK explizit festlegen. Am Ende der Transaktion kehrt die Verbindung zu dem Transaktionsmodus zurück, in dem sie sich vor Beginn der expliziten Transaktion befand, also entweder zum impliziten oder zum Autocommitmodus.

Sie können alle Transact-SQL-Anweisungen in einer expliziten Transaktion verwenden; ausgenommen davon sind die folgenden Anweisungen:

  • CREATE DATABASE
  • ALTER DATABASE
  • DROP DATABASE
  • CREATE FULLTEXT CATALOG
  • ALTER FULLTEXT CATALOG
  • DROP FULLTEXT CATALOG
  • DROP FULLTEXT INDEX
  • ALTER FULLTEXT INDEX …
  • CREATE FULLTEXT INDEX
  • BACKUP
  • RESTORE
  • RECONFIGURE
  • Gespeicherte Volltext-Systemprozeduren
  • sp_dboption zum Festlegen von Datenbankoptionen oder einer beliebigen Systemprozedur, durch die die master-Datenbank in expliziten bzw. impliziten Transaktionen geändert wird.

Hinweis

UPDATE STATISTICS kann in einer expliziten Transaktion verwendet werden. UPDATE STATISTICS führt jedoch unabhängig von der einschließenden Transaktion einen Commit aus, und es kann kein Rollback ausgeführt werden.

Autocommit-Transaktionen
Der Autocommit-Modus ist der Standardmodus zur Transaktionsverwaltung der SQL Server-Datenbank-Engine. Für jede Transact-SQL-Anweisung wird beim Beenden ein Commit oder Rollback ausgeführt. Wenn eine Anweisung erfolgreich beendet wird, wird ein Commit ausgeführt; wenn hingegen Fehler auftreten, wird ein Rollback ausgeführt. Eine Verbindung mit einer Instanz der SQL Server-Datenbank-Engine befindet sich immer dann im Autocommit-Modus, wenn dieser Standardmodus nicht durch explizite oder implizite Transaktionen überschrieben wurde. Der Autocommit-Modus ist ebenfalls der Standardmodus für ADO, OLE DB, ODBC und DB-Library.

Implizite Transaktionen
Wenn sich eine Verbindung im impliziten Transaktionsmodus befindet, startet die Instanz der SQL Server-Datenbank-Engine automatisch eine neue Transaktion, nachdem für die aktuelle Transaktion ein Commit oder Rollback ausgeführt wurde. Die Kennzeichnung des Starts einer Transaktion entfällt; Sie führen nur einen Commit oder Rollback für die einzelnen Transaktionen aus. Im impliziten Transaktionsmodus wird eine fortlaufende Kette von Transaktionen generiert. Sie legen den impliziten Transaktionsmodus entweder durch eine API-Funktion oder durch die Transact-SQL-Anweisung SET IMPLICIT_TRANSACTIONS ON fest. Dieser Modus wird auch Autocommit OFF genannt. Weitere Informationen finden Sie unter setAutoCommit-Methode (SQLServerConnection).

Nachdem der implizite Transaktionsmodus für eine Verbindung aktiviert wurde, startet die SQL Server-Datenbank-Engine automatisch eine Transaktion, wenn eine dieser Anweisungen zum ersten Mal ausgeführt wird:

  • ALTER TABLE

  • CREATE

  • DELETE

  • DROP

  • FETCH

  • GRANT

  • INSERT

  • OPEN

  • REVOKE

  • SELECT

  • TRUNCATE TABLE

  • UPDATE

  • Transaktionen im Bereich des Batchs Trifft nur auf MARS (Multiple Active Result Sets) zu; eine explizite oder implizite Transact-SQL-Transaktion, die unter einer MARS-Sitzung gestartet wird, wird zu einer Transaktion im Batchbereich. Für eine Transaktion im Bereich des Batchs, für die nach Abschluss des Batches kein Commit oder Rollback ausgeführt wird, wird das Rollback automatisch durch SQL Server vorgenommen.

  • Verteilte Transaktionen Verteilte Transaktionen erstrecken sich auf mindestens zwei Server, die als Ressourcen-Manager bekannt sind. Die Verwaltung der Transaktionen muss zwischen den Ressourcen-Managern von einer Serverkomponente, dem Transaktions-Manager, koordiniert werden. Jede Instanz der SQL Server-Datenbank-Engine kann als Ressourcen-Manager in verteilten Transaktionen eingesetzt werden, die von Transaktions-Managern, wie Microsoft Distributed Transaction Coordinator (MS DTC) oder anderen Transaktions-Managern, die die Open Group XA-Spezifikation für die verteilte Transaktionsverarbeitung unterstützen, koordiniert werden. Weitere Informationen finden Sie in der MS DTC-Dokumentation.

    Bei einer Transaktion in einer einzelnen Instanz der SQL Server-Datenbank-Engine, die sich auf zwei oder mehr Datenbanken erstreckt, handelt es sich eigentlich um eine verteilte Transaktion. Die Instanz verwaltet die verteilte Transaktion jedoch intern; für den Benutzer entsteht der Eindruck, es handele sich um eine lokale Transaktion.

    Auf der Anwendungsebene wird eine verteilte Transaktion beinahe so wie eine lokale Transaktion verwaltet. Am Ende der Transaktion fordert die Anwendung die Transaktion auf, entweder einen Commit oder Rollback auszuführen. Ein verteilter Commit darf vom Transaktions-Manager nicht auf dieselbe Art verwaltet werden, um das Risiko zu minimieren, dass einige Ressourcen-Manager bei einem Netzwerkfehler den Commit erfolgreich ausführen, während andere für die Transaktion einen Rollback ausführen. Dies wird dadurch erreicht, dass der Commitvorgang in zwei Phasen verwaltet wird (die Vorbereitungsphase und die Commitphase), bekannt als Zweiphasencommit (2PC).

    • Vorbereitungsphase Wenn der Transaktions-Manager eine Anforderung für ein Commit erhält, sendet er einen Vorbereitungsbefehl an alle Ressourcen-Manager, die an der Transaktion beteiligt sind. Jeder Ressourcen-Manager trifft dann die notwendigen Vorbereitungen, um die Transaktion beständig zu machen, und alle Puffer, die Images von Protokollen für die Transaktion enthalten, werden auf den Datenträger geleert. Wenn die Ressourcen-Manager die Vorbereitungsphase beenden, geben sie jeweils eine Information über den Erfolg oder das Fehlschlagen der Vorbereitung an den Transaktions-Manager zurück. Mit SQL Server 2014 (12.x) wurde die verzögerte Transaktionsdauerhaftigkeit eingeführt. Für verzögerte dauerhafte Transaktionen wird ein Commit ausgeführt, bevor das Protokollimage auf den Datenträger geleert wird. Weitere Informationen zur Dauerhaftigkeit verzögerter Transaktionen finden Sie im Artikel Steuern der Transaktionsdauerhaftigkeit.

    • Commitphase Wenn der Transaktions-Manager von der erfolgreichen Vorbereitung aller Ressourcen-Manager in Kenntnis gesetzt wird, sendet er Commitbefehle an alle Ressourcen-Manager. Die Ressourcen-Manager können dann den Commit beenden. Wenn alle Ressourcen-Manager eine erfolgreiche Ausführung des Commits melden, sendet der Transaktions-Manager eine Benachrichtigung über die erfolgreiche Ausführung an die Anwendung. Wenn einer der Ressourcen-Manager einen Fehler bei der Vorbereitung ausgibt, sendet der Transaktions-Manager einen Rollbackbefehl an alle Ressourcen-Manager und benachrichtigt die Anwendung über die fehlgeschlagene Ausführung des Commits.

      SQL Server-Datenbank-Engine-Anwendungen können verteilte Transaktionen entweder über Transact-SQL oder die Datenbank-API verwalten. Weitere Informationen finden Sie unter BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Beenden von Transaktionen

Sie können Transaktionen entweder mit einer COMMIT-Anweisung oder ROLLBACK-Anweisung oder durch eine entsprechende API-Funktion beenden.

  • COMMIT Wenn eine Transaktion erfolgreich ist, führen Sie einen Commit aus. Durch eine COMMIT-Anweisung wird sichergestellt, dass alle Änderungen der Transaktion zum dauerhaften Bestandteil der Datenbank werden. Durch eine COMMIT-Anweisung werden auch von der Transaktion verwendete Ressourcen, wie etwa Sperren, freigegeben.

  • ROLLBACK Wenn ein Fehler in einer Transaktion auftritt, oder wenn ein Benutzer beschließt, die Transaktion abzubrechen, führen Sie einen Rollback aus. Durch eine ROLLBACK-Anweisung werden alle Änderungen, die während der Transaktion vorgenommen wurden, rückgängig gemacht, sodass die Daten in ihren Ausgangsstatus zurückversetzt werden. Durch das ROLLBACK werden auch Ressourcen freigegeben, die von der Transaktion beansprucht wurden.

Hinweis

Bei Verbindungen, die für die Unterstützung von MARS (Multiple Active Result Sets) aktiviert sind, kann für eine durch eine API-Funktion gestartete explizite Transaktion kein Commit ausgeführt werden, solange Ausführungsanforderungen anstehen. Jeder Versuch, ein Commit für eine derartige Transaktion auszuführen, für die noch ausstehende Vorgänge vorhanden sind, führt zu einem Fehler.

Fehler während der Transaktionsverarbeitung

Wenn eine Transaktion aufgrund eines Fehlers nicht erfolgreich beendet werden kann, führt SQL Server automatisch ein Rollback für die Transaktion aus und gibt alle Ressourcen frei, die von der Transaktion beansprucht wurden. Wenn die Netzwerkverbindung des Clients mit einer Instanz der SQL Server-Datenbank-Engine unterbrochen ist, wird für alle ausstehenden Transaktionen dieser Verbindung ein Rollback ausgeführt, sobald das Netzwerk die Instanz über die Unterbrechung benachrichtigt. Wenn die Clientanwendung einen Fehler erzeugt oder wenn der Clientcomputer heruntergefahren oder neu gestartet wird, kommt es ebenfalls zu einer Unterbrechung der Verbindung, und die Instanz der SQL Server-Datenbank-Engine führt ein Rollback für alle ausstehenden Verbindungen aus, sobald das Netzwerk es über die Unterbrechung benachrichtigt. Wenn sich der Client von der Anwendung abmeldet, wird für alle ausstehenden Transaktionen ein Rollback ausgeführt.

Wenn ein Anweisungsfehler zur Laufzeit (wie etwa eine Einschränkungsverletzung) in einem Batch auftritt, führt die SQL Server-Datenbank-Engine standardmäßig nur für die Anweisung ein Rollback aus, die den Fehler generiert hat. Sie können dieses Verhalten mithilfe der SET XACT_ABORT-Anweisung ändern. Nach dem Ausführen von SET XACT_ABORT ON führt jeder Anweisungsfehler zur Laufzeit dazu, dass automatisch ein Rollback für die aktuelle Transaktion ausgeführt wird. Kompilierungsfehler, wie z.B. Syntaxfehler, sind von SET XACT_ABORT nicht betroffen. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).

Für den Fall, dass Fehler auftreten, sollte in den Anwendungscode eine korrigierende Aktion (COMMIT oder ROLLBACK) aufgenommen werden. Ein effizientes Tool zur Fehlerbehandlung u. a. bei Fehlern in Transaktionen, ist das Transact-SQL-Konstrukt TRY...CATCH. Weitere Informationen mit Beispielen zu Transaktionen finden Sie unter TRY...CATCH (Transact-SQL). Ab SQL Server 2012 (11.x) kann die THROW-Anweisung verwendet werden, um eine Ausnahme auszulösen und die Ausführung an einen CATCH-Block eines TRY...CATCH-Konstrukts zu übergeben. Weitere Informationen finden Sie unter THROW (Transact-SQL).

Kompilierungs- und Laufzeitfehler im Autocommit-Modus

Im Autocommitmodus entsteht hin und wieder der Eindruck, dass eine Instanz der SQL Server-Datenbank-Engine ein Rollback für einen gesamten Batch und nicht nur für eine einzelne SQL-Anweisung ausgeführt hat. Dies passiert, wenn es sich beim aufgetretenen Fehler um einen Kompilierungsfehler und nicht um einen Laufzeitfehler handelt. Bei einem Kompilierungsfehler wird verhindert, dass die SQL Server-Datenbank-Engine einen Ausführungsplan erstellt; somit wird keine Anweisung im Batch ausgeführt. Obwohl der Eindruck entsteht, dass für alle Anweisungen vor derjenigen, die den Fehler generiert hat, ein Rollback ausgeführt wurde, hat der Fehler bereits verhindert, dass überhaupt eine Anweisung im Batch ausgeführt wurde. Im folgenden Beispiel wird aufgrund eines Kompilierungsfehlers keine der INSERT-Anweisungen im dritten Batch ausgeführt. Es entsteht der Eindruck, dass für die ersten zwei INSERT-Anweisungen ein Rollback ausgeführt wird, obwohl sie nie ausgeführt wurden.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

Im folgenden Beispiel generiert die dritte INSERT-Anweisung einen Laufzeitfehler aufgrund eines doppelten Primärschlüssels. Die ersten zwei INSERT-Anweisungen sind erfolgreich, sodass für sie ein Commit ausgeführt wird; sie bleiben somit nach dem Laufzeitfehler erhalten.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Die SQL Server-Datenbank-Engine verwendet die verzögerte Namensauflösung, bei der Objektnamen erst zur Ausführungszeit aufgelöst werden. Im folgenden Beispiel werden die ersten zwei INSERT-Anweisungen ausgeführt und mit einem Commit abgeschlossen; die entsprechenden beiden Zeilen bleiben in der TestBatch-Tabelle, nachdem die dritte INSERT-Anweisung einen Laufzeitfehler durch Verweisen auf eine nicht vorhandene Tabelle generiert.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Grundlagen zur Sperrung und Zeilenversionsverwaltung

Die SQL Server-Datenbank-Engine verwendet die folgenden Mechanismen, um die Integrität von Transaktionen sicherzustellen und die Konsistenz der Datenbanken beizubehalten, wenn mehrere Benutzer gleichzeitig auf Daten zugreifen:

  • Sperrung

    Jede Transaktion fordert Sperren verschiedener Typen für die Ressourcen (wie z. B. Zeilen, Seiten oder Tabellen) an, von denen die Transaktion abhängt. Diese Sperren verhindern, dass die Ressourcen durch andere Transaktionen in einer Weise geändert werden, die zu Problemen für die Transaktion führen würde, die die Sperre angefordert hat. Jede Transaktion hebt ihre Sperren wieder auf, sobald sie nicht mehr von den gesperrten Ressourcen abhängig ist.

  • Zeilenversionsverwaltung

    Wenn eine auf Zeilenversionsverwaltung basierende Isolationsstufe aktiviert ist, bewahrt die SQL Server-Datenbank-Engine Versionen jeder Zeile auf, an der Änderungen vorgenommen werden. Anwendungen können angeben, dass eine Transaktion die Zeilenversionen verwendet, um die Daten so anzuzeigen, wie sie zum Zeitpunkt des Transaktions- oder Abfragestarts vorgelegen haben, statt alle Lesevorgänge durch Sperren zu schützen. Durch Verwendung der Zeilenversionsverwaltung wird die Wahrscheinlichkeit, dass ein Lesevorgang zur Blockierung anderer Transaktionen führt, weitgehend reduziert.

Sperren und Zeilenversionsverwaltung verhindern, dass Benutzer Daten lesen, für die noch kein Commit ausgeführt wurde, und verhindern, dass viele Benutzer gleichzeitig versuchen, dieselben Daten zu ändern. Ohne Sperrung oder Zeilenversionsverwaltung könnten Abfragen, die für Daten ausgeführt werden, zu unerwarteten Ergebnissen führen, weil Daten zurückgegeben werden, für die in der Datenbank noch kein Commit ausgeführt wurde.

Anwendungen können Transaktionsisolationsstufen auswählen. Diese Stufen definieren, inwieweit die jeweilige Transaktion vor Datenänderungen durch andere Transaktionen geschützt ist. Für einzelne Transact-SQL-Anweisungen können Hinweise auf Tabellenebene angegeben werden, um das Verhalten noch weiter an die Anforderungen der Anwendung anzupassen.

Verwalten des parallelen Datenzugriffs

Wenn Benutzer zum selben Zeitpunkt auf eine Ressource zugreifen, wird das als paralleler Zugriff auf die Ressource bezeichnet. Der parallele Datenzugriff erfordert Mechanismen, mit denen negative Auswirkungen vermieden werden, wenn mehrere Benutzer versuchen, Ressourcen zu ändern, die von anderen Benutzern aktiv verwendet werden.

Parallelitätseffekte

Benutzer, die Daten ändern, können einen Konflikt mit anderen Benutzern verursachen, die die gleichen Daten zur gleichen Zeit lesen oder ändern. Durch diese Benutzer erfolgt ein gleichzeitiger Zugriff auf die Daten. Wenn ein Datenspeichersystem keine Steuerung für die Parallelität besitzt, können Benutzer die folgenden Auswirkungen feststellen:

  • Verlorene Updates

    Verlorene Updates treten auf, wenn mindestens zwei Transaktionen dieselbe Zeile auswählen und diese Zeile dann auf der Grundlage des ursprünglich ausgewählten Werts aktualisieren. Eine einzelne Transaktion ist nicht über die anderen Transaktionen informiert. Das letzte Update überschreibt Updates von anderen Transaktionen; dies führt zu Datenverlusten.

    Nehmen Sie beispielsweise an, dass zwei Redakteure eine elektronische Kopie desselben Dokuments erstellen. Jeder Redakteur ändert die eigene Kopie und speichert die geänderte Kopie anschließend, wodurch das Originaldokument überschrieben wird. Der Redakteur, der die Kopie zuletzt speichert, überschreibt die Änderungen des anderen Redakteurs. Das Problem könnte vermieden werden, wenn einer der Redakteure erst auf die Datei zugreifen kann, nachdem der andere Redakteur seine Arbeit beendet und ein Commit der Transaktion ausgeführt hat.

  • Abhängigkeit von Daten, für die kein Commit ausgeführt wurde (Dirty Read)

    Die Abhängigkeit von Daten, für die kein Commit ausgeführt wurde, tritt dann ein, wenn eine zweite Transaktion eine Zeile auswählt, die von einer anderen Transaktion aktualisiert wird. Die zweite Transaktion liest Daten, für die noch kein Commit ausgeführt wurde und die von der Transaktion, die die Zeile aktualisiert, noch geändert werden können.

    Angenommen, ein Redakteur nimmt Änderungen an einem elektronischen Dokument vor. Während die Änderungen vorgenommen werden, verteilt ein zweiter Redakteur eine Kopie des Dokuments mit allen bisherigen Änderungen an die Zielgruppe. Der erste Redakteur entscheidet dann, dass die bisherigen Änderungen falsch sind, löscht sie und speichert das Dokument. Das verteilte Dokument enthält nun Änderungen, die nicht mehr vorhanden sind und so behandelt werden müssten, als ob sie nie vorhanden gewesen wären. Dieses Problem könnte vermieden werden, wenn das geänderte Dokument erst dann gelesen werden könnte, wenn der erste Redakteur die endgültige Speicherung der Änderungen vorgenommen und ein Commit der Transaktion ausgeführt hat.

  • Inkonsistente Analyse (nicht wiederholbarer Lesevorgang)

    Die inkonsistente Analyse tritt dann ein, wenn eine zweite Transaktion mehrmals auf dieselbe Zeile zugreift und jedes Mal verschiedene Daten liest. Die inkonsistente Analyse ist vergleichbar mit der Abhängigkeit von Daten, für die kein Commit ausgeführt wurde, da auch in diesem Fall eine andere Transaktion die Daten ändert, die eine zweite Transaktion liest. Bei der inkonsistenten Analyse wurde jedoch für die von der zweiten Transaktion gelesenen Daten ein Commit von der Transaktion, die die Änderung vornahm, ausgeführt. Darüber hinaus umfasst die inkonsistente Analyse mehrere Lesevorgänge (mindestens zwei) derselben Zeile, wobei jedes Mal die Informationen von einer anderen Transaktion geändert wurden; der Begriff "Nicht wiederholbarer Lesevorgang" bezieht sich auf diesen Vorgang.

    Angenommen, ein Redakteur liest dasselbe Dokument zweimal, doch zwischen den einzelnen Lesedurchgängen schreibt der Verfasser das Dokument um. Wenn der Redakteur das Dokument zum zweiten Mal liest, unterscheidet es sich von der ersten Version. Der ursprüngliche Lesevorgang lässt sich nicht wiederholen. Dieses Problem könnte vermieden werden, wenn der Verfasser das Dokument erst ändern könnte, nachdem der Redakteur den letzten Lesevorgang beendet hat.

  • Phantomlesezugriffe

    Ein Phantomlesezugriff ist eine Situation, bei der zwei identische Abfragen ausgeführt werden, wobei die von der zweiten Abfrage zurückgegebene Zeilensammlung abweicht. Im unten stehenden Beispiel wird veranschaulicht, wie eine solche Situation auftreten kann. Angenommen, die beiden unten stehenden Transaktionen werden gleichzeitig ausgeführt. Die zwei SELECT-Anweisungen in der ersten Transaktion können ggf. andere Ergebnisse zurückgeben, da die INSERT-Anweisung in der zweiten Transaktion die von beiden verwendeten Daten ändert.

    --Transaction 1
BEGIN TRAN;
SELECT ID FROM dbo.employee
WHERE ID > 5 and ID < 10;
--The INSERT statement from the second transaction occurs here.
SELECT ID FROM dbo.employee
WHERE ID > 5 and ID < 10;
COMMIT;

    --Transaction 2
BEGIN TRAN;
INSERT INTO dbo.employee
  (Id, Name) VALUES(6 ,'New');
COMMIT;

  • Durch Zeilenupdates verursachte fehlende und doppelte Lesezugriffe

    • Übergehen einer aktualisierten Zeile oder mehrfaches Erkennen einer aktualisierten Zeile

      Transaktionen, die auf der READ UNCOMMITTED-Ebene ausgeführt werden, geben keine freigegebenen Sperren aus, die verhindern würden, dass andere Transaktionen Daten ändern, die von der aktuellen Transaktion gelesen werden. Transaktionen, die auf der READ COMMITTED-Ebene ausgeführt werden, geben freigegebene Sperren aus. Diese Zeilen- oder Seitensperren werden jedoch aufgehoben, nachdem die Zeile gelesen wurde. In beiden Fällen kann beim Scannen eines Index eine Zeile zwei Mal erscheinen, wenn ein anderer Benutzer die Indexschlüsselspalte ändert, während Sie sie lesen, und die Spalte durch die Schlüsseländerung an eine Position hinter der aktuellen Leseposition verschoben wird. Ebenso ist es möglich, dass die Zeile nicht erscheint, wenn die Spalte durch die Schlüsseländerung an eine Indexposition verschoben wird, die bereits gelesen wurde. Um dies zu vermeiden, verwenden Sie den SERIALIZABLE- oder HOLDLOCK-Hinweis oder die Zeilenversionsverwaltung. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

    • Übergehen von Zeilen, die nicht Ziel von Updates waren

      Wenn READ UNCOMMITTED angegeben wird und eine Abfrage Zeilen in der Speicherreservierungsreihenfolge (unter Verwendung der IAM-Seiten) liest, werden möglicherweise Zeilen übergangen, falls eine Seite durch eine andere Transaktion geteilt wird. Dieser Fall kann beim Einsatz von READ UNCOMMITTED nicht eintreten, weil während der Teilung einer Seite eine Tabellensperre aufrechterhalten wird. Es werden auch keine Zeilen übergangen, wenn die Tabelle nicht über einen gruppierten Index verfügt, da Updates keine Seitenteilungen verursachen.

Parallelitätstypen

Wenn viele Benutzer gleichzeitig versuchen, Daten in einer Datenbank zu ändern, muss ein Steuerungssystem implementiert werden, durch das sichergestellt wird, dass sich die von einem Benutzer vorgenommenen Änderungen nicht auf die Änderungen eines anderen Benutzers auswirken. Dies wird als Parallelitätssteuerung bezeichnet.

In der Theorie der Parallelitätssteuerung werden die Methoden zum Implementieren der Parallelitätssteuerung in zwei Gruppen klassifiziert:

  • Steuerung durch eingeschränkte Parallelität

    Durch ein System aus Sperren werden Benutzer daran gehindert, Daten so zu verändern, dass sich dies nachteilig auf die Arbeit anderer Benutzer auswirkt. Sobald ein Benutzer eine Aktion ausführt, die zum Anwenden einer Sperre führt, können andere Benutzer so lange keine Aktionen ausführen, die mit dieser Sperre in Konflikt stehen, bis die Sperre durch den Besitzer aufgehoben wird. Diese Vorgehensweise wird als Steuerung durch eingeschränkte Parallelität bezeichnet und wird vorwiegend in Umgebungen verwendet, in denen die Wahrscheinlichkeit von Konflikten beim Zugriff auf Daten sehr hoch ist. In diesen Umgebungen verursacht das Schützen der Daten mithilfe von Sperren weniger Kosten als das Ausführen von Rollbacks für Transaktionen, wenn Parallelitätskonflikte aufgetreten sind.

  • Steuerung für optimistische Parallelität

    Bei der Steuerung durch vollständige Parallelität werden keine Sperren für Daten eingerichtet, wenn diese von den Benutzern gelesen werden. Wenn ein Benutzer Daten aktualisiert, überprüft das System, ob ein anderer Benutzer die Daten geändert hat, nachdem sie gelesen wurden. Wenn die Daten bereits durch einen anderen Benutzer aktualisiert worden sind, wird ein Fehler ausgelöst. In der Regel führt der Benutzer, der die Fehlermeldung empfangen hat, einen Rollback für die Transaktion aus und beginnt mit der Bearbeitung von vorn. Diese Vorgehensweise wird als Steuerung durch vollständige Parallelität bezeichnet und wird vorwiegend in Umgebungen verwendet, in denen nur wenige Konflikte beim Zugriff auf Daten entstehen und die Kosten für das gelegentliche Ausführen von Rollbacks für eine Transaktion geringer sind als die Kosten für das Sperren der Daten, wenn sie gelesen werden.

SQL Server unterstützt verschiedene Typen der Parallelitätssteuerung. Benutzer geben den Typ der Parallelitätssteuerung an, indem sie Transaktionsisolationsstufen für Verbindungen oder Parallelitätsoptionen für Cursor angeben. Diese Attribute können mithilfe von Transact-SQL-Anweisungen definiert werden oder über die Eigenschaften und Attribute der Schnittstellen zur Anwendungsprogrammierung (APIs, Application Programming Interfaces) der Datenbank, wie z. B. ADO, ADO.NET, OLE DB und ODBC.

Isolationsstufen in der SQL Server-Datenbank-Engine

Transaktionen geben eine Isolationsstufe an, mit der definiert wird, bis zu welchem Ausmaß eine Transaktion von Ressourcen- oder Datenänderungen isoliert sein muss, die von anderen Transaktionen durchgeführt werden. Die einzelnen Isolationsstufen werden dahingehend beschrieben, welche Parallelitätsnebeneffekte (wie z. B. Dirty Reads oder Phantomlesezugriffe) zulässig sind.

Durch die Transaktionsisolationsstufen wird Folgendes gesteuert:

  • Ob beim Lesen von Daten Sperren eingerichtet werden, und welcher Sperrentyp angefordert wird.
  • Wie lange die Lesesperren aufrechterhalten werden.
  • Ob ein Lesevorgang, der auf Zeilen verweist, die durch eine andere Transaktion geändert wurden:
    • Blockiert wird, bis die exklusive Sperre für die Zeile aufgehoben wird.
    • Die durch einen Commit bestätigte Version der Zeile abruft, die zum Zeitpunkt des Anweisungs- oder Transaktionsstarts vorhanden war.
    • Die Datenänderung liest, für die noch kein Commit ausgeführt wurde.

Wichtig

Das Auswählen einer Transaktionsisolationsstufe hat keine Auswirkungen auf die Sperren, die zum Schutz der Datenänderung eingerichtet werden. Eine Transaktion erhält immer eine exklusive Sperre für alle von ihr geänderten Daten und hält diese Sperre bis zum Abschluss der Transaktion aufrecht, und zwar unabhängig davon, welche Isolationsstufe für diese Transaktion festgelegt wurde. Für Lesevorgänge wird durch die Transaktionsisolationsstufen in erster Linie der Grad des Schutzes vor den Auswirkungen der Änderungen definiert, die durch andere Transaktionen vorgenommen werden.

Eine niedrigere Isolationsstufe erhöht einerseits die Möglichkeit, dass viele Benutzer gleichzeitig auf Daten zugreifen können, führt aber gleichzeitig zum Anstieg der negativen Parallelitätseffekte (Dirty Reads oder verlorene Updates), mit denen die Benutzer rechnen müssten. Und umgekehrt schränkt eine höhere Isolationsstufe zwar die Typen der Parallelitätseffekte ein, mit denen Benutzer rechnen müssen, gleichzeitig werden dafür aber mehr Systemressourcen beansprucht, und die Wahrscheinlichkeit steigt, dass sich die Transaktionen untereinander blockieren. So muss bei jeder Auswahl der geeigneten Isolationsstufe eine Abwägung zwischen den Datenintegritätsanforderungen der Anwendungen einerseits und dem mit jeder Isolationsstufe verbundenen Aufwand andererseits erfolgen. Die höchste Isolationsstufe (Serializable) garantiert, dass eine Transaktion jedes Mal, wenn sie einen Lesevorgang wiederholt, genau dieselben Daten liest. Dies wird jedoch durch ein Ausmaß an Sperren erreicht, das in Systemen mit mehreren Benutzern wahrscheinlich zu negativen Auswirkungen für andere Benutzer führt. Mit der niedrigsten Isolationsstufe (Read Uncommitted) können Daten abgerufen werden, die von anderen Transaktionen geändert wurden, für die jedoch noch kein Commit ausgeführt wurde. In der Isolationsstufe Read Uncommitted können sämtliche denkbaren Parallelitätsnebeneffekte auftreten, dagegen werden keine Lesesperren und keine Versionsverwaltung verwendet, wodurch der Aufwand minimiert wird.

Isolationsstufen der Datenbank-Engine

Der ISO-Standard definiert die folgenden Isolationsstufen, die alle von der SQL Server-Datenbank-Engine unterstützt werden:

Isolationsstufe Definition
Read uncommitted Die niedrigste Isolationsstufe, bei der Transaktionen nur soweit isoliert werden, dass sichergestellt ist, dass keine physisch beschädigten Daten gelesen werden. Auf dieser Stufe sind Dirty Reads zulässig, d. h., eine Transaktion kann Änderungen verfolgen, die von anderen Transaktionen vorgenommen wurden und für die noch kein Commit ausgeführt wurde.
Read committed Ermöglicht einer Transaktion das Lesen von Daten, die zuvor von einer anderen Transaktion gelesen (nicht geändert) wurden, ohne warten zu müssen, bis die erste Transaktion abgeschlossen ist. Die SQL Server-Datenbank-Engine behält Schreibsperren (die für ausgewählte Daten eingerichtet wurden) bis zum Ende der Transaktion bei, Lesesperren werden jedoch bei Ausführung des SELECT-Vorgangs aufgehoben. Hierbei handelt es sich um die Standardstufe der SQL Server-Datenbank-Engine.
Repeatable read Die SQL Server-Datenbank-Engine behält Lese- und Schreibsperren, die für ausgewählte Daten eingerichtet wurden, bis zum Ende der Transaktion bei. Da Bereichssperren jedoch nicht verwaltet werden, können Phantomlesevorgänge auftreten.
Serializable Die höchste Stufe, auf der Transaktionen vollständig voneinander isoliert sind. Die SQL Server-Datenbank-Engine behält Lese- und Schreibsperren, die für ausgewählte Daten eingerichtet wurden, bis zur Aufhebung am Ende der Transaktion bei. Bereichssperren werden angefordert, wenn ein SELECT-Vorgang eine WHERE-Bereichsklausel verwendet. Dies dient vor allem der Vermeidung von Phantomlesevorgängen.

Hinweis: DDL-Vorgänge und -Transaktionen in replizierten Tabellen schlagen möglicherweise fehl, wenn die Isolationsstufe SERIALIZABLE angefordert wird. Das liegt daran, dass Replikationsabfragen Hinweise verwenden, die möglicherweise mit der serialisierbaren Isolationsstufe nicht kompatibel sind.

SQL Server unterstützt außerdem zwei zusätzliche Transaktionsisolationsstufen, bei denen die Zeilenversionsverwaltung verwendet wird. Eine davon ist eine Implementierung der READ COMMITTED-Isolation, die andere – Snapshot – ist eine Transaktionsisolationsstufe.

Isolationsstufe der Zeilenversionsverwaltung Definition
READ COMMITTED-Momentaufnahme (RCSI) Wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON festgelegt ist, verwendet die READ COMMITTED-Isolation die Zeilenversionsverwaltung, um eine Lesekonsistenz auf der Anweisungsebene zu gewährleisten. Lesevorgänge erfordern dabei lediglich SCH-S-Sperren auf der Tabellenebene und keine Seiten- oder Zeilensperren. Das heißt, die SQL Server-Datenbank-Engine verwendet die Zeilenversionsverwaltung, um jede Anweisung mit einer transaktionskonsistenten Momentaufnahme der Daten so darzustellen, wie sie zu Beginn der Anweisung vorhanden waren. Es werden keine Sperren verwendet, um die Daten vor Updates durch andere Transaktionen zu schützen. Eine benutzerdefinierte Funktion kann Daten zurückgeben, für die ein Commit ausgeführt wurde, nachdem die Anweisung mit dem UDF begonnen hat.

Wenn die Datenbankoption READ_COMMITTED_SNAPSHOT die Standardeinstellung OFF aufweist, verwendet die READ COMMITED-Isolation freigegebene Sperren, um zu verhindern, dass andere Transaktionen Zeilen ändern, während die aktuelle Transaktion einen Lesevorgang ausführt. Durch freigegebene Sperren wird außerdem verhindert, dass die Anweisung Zeilen, die von anderen Transaktionen geändert werden, erst nach Abschluss der anderen Transaktion lesen kann. Beide Implementierungen entsprechen der ISO-Definition der READ COMMITTED-Isolation.
Momentaufnahme Die Momentaufnahmeisolationsstufe verwendet die Zeilenversionsverwaltung, um die Lesekonsistenz auf der Transaktionsebene zu gewährleisten. Dabei werden durch Lesevorgänge keine Seiten- oder Zeilensperren eingerichtet, sondern lediglich SCH-S-Tabellensperren. Beim Lesen von Zeilen, die durch eine andere Transaktion geändert wurden, wird die Version der Zeile abgerufen, die zum Startzeitpunkt der Transaktion vorhanden war. Sie können die Momentaufnahmeisolation für eine Datenbank nur verwenden, wenn die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON festgelegt wurde. Standardmäßig ist diese Option für Benutzerdatenbanken auf OFF gesetzt.

Hinweis: Für SQL Server wird die Versionsverwaltung für Metadaten nicht unterstützt. Aus diesem Grund gibt es bezüglich der DDL-Vorgänge, die in einer unter Momentaufnahmeisolation ausgeführten expliziten Transaktion ausgeführt werden, Einschränkungen. Die folgenden DDL-Anweisungen sind nach einer BEGIN TRANSACTION-Anweisung unter Momentaufnahmeisolation nicht zulässig: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME sowie alle CLR (Common Language Runtime)-DDL-Anweisungen. Diese Anweisungen sind zulässig, wenn Sie die Momentaufnahmeisolation in impliziten Transaktionen verwenden. Eine implizite Transaktion ist definitionsgemäß eine einzelne Anweisung, mit der die Semantik der Momentaufnahmeisolation auch in DDL-Anweisungen erzwungen werden kann. Verstöße gegen dieses Prinzip können zu Fehler 3961 führen: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation..

Die folgende Tabelle veranschaulicht, welche Parallelitätsnebeneffekte in den einzelnen Isolationsstufen möglich sind.

Isolationsstufe Dirty Read Nonrepeatable Read Phantom
Read uncommitted Ja Ja Ja
Read committed Nein Ja Ja
Repeatable read Nein Nein Ja
Momentaufnahme Nein Nr. Nein
Serializable Nein Nr. No

Weitere Informationen zu den speziellen Sperrentypen sowie zur Unterstützung der Zeilenversionsverwaltung durch die einzelnen Transaktionsisolationsstufen finden Sie unter SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Die Transaktionsisolationsstufen können mithilfe von Transact-SQL oder über eine Datenbank-API festgelegt werden.

Transact-SQL
Transact-SQL-Skripts verwenden die SET TRANSACTION ISOLATION LEVEL-Anweisung.

ADO
ADO-Anwendungen legen die IsolationLevel-Eigenschaft des Connection-Objekts auf adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead oder adXactReadSerializable fest.

ADO.NET
ADO.NET-Anwendungen, die den verwalteten Namespace System.Data.SqlClient verwenden, können die SqlConnection.BeginTransaction-Methode aufrufen und die IsolationLevel-Option auf Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable oder Snapshot festlegen.

OLE DB
Beim Starten einer Transaktion rufen Anwendungen, die OLE DB verwenden, ITransactionLocal::StartTransaction auf, wobei isoLevel auf ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT oder ISOLATIONLEVEL_SERIALIZABLE festgelegt ist.

Wenn die Transaktionsisolationsstufe im Autocommitmodus angegeben wird, können OLE DB-Anwendungen die DBPROPSET_SESSION-Eigenschaft DBPROP_SESS_AUTOCOMMITISOLEVELS auf DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED oder DBPROPVAL_TI_SNAPSHOT festlegen.

ODBC
ODBC-Anwendungen rufen SQLSetConnectAttr auf, wobei Attribute auf SQL_ATTR_TXN_ISOLATION und ValuePtr auf SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ oder SQL_TXN_SERIALIZABLE festgelegt ist.

Für Momentaufnahmetransaktionen rufen Anwendungen SQLSetConnectAttr auf, wobei das Attribut auf SQL_COPT_SS_TXN_ISOLATION und ValuePtr auf SQL_TXN_SS_SNAPSHOT festgelegt ist. Eine Momentaufnahmetransaktion kann entweder über SQL_COPT_SS_TXN_ISOLATION oder SQL_ATTR_TXN_ISOLATION abgerufen werden.

Sperren in der Datenbank-Engine

Sperren beschreiben einen Mechanismus, der von der SQL Server-Datenbank-Engine zum Synchronisieren des gleichzeitigen Benutzerzugriffs auf die gleichen Daten verwendet wird.

Bevor eine Transaktion eine Abhängigkeit für den aktuellen Status von Daten abruft, z. B. durch Lesen oder Ändern der Daten, muss sie sich selbst vor den Auswirkungen schützen, die sich ergeben können, wenn eine andere Transaktion die gleichen Daten ändert. Die Transaktion fordert zu diesem Zweck eine Sperre für die betreffenden Daten an. Sperren besitzen verschiedene Modi, z. B. freigegeben oder exklusiv. Der Sperrmodus definiert den Grad der Abhängigkeit, den die Transaktion für die Daten eingerichtet hat. Keiner Transaktion wird eine Sperre gewährt, die einen Konflikt mit dem Modus einer Sperre verursachen würde, die einer anderen Transaktion bereits für die betreffenden Daten erteilt wurde. Wenn eine Transaktion einen Sperrmodus anfordert, der einen Konflikt mit einer Sperre verursacht, die bereits für die gleichen Daten erteilt wurde, hält die Instanz der SQL Server-Datenbank-Engine die anfordernde Transaktion an, bis die erste Sperre aufgehoben wird.

Wenn eine Transaktion Daten ändert, werden gewisse Sperren, die die Änderung schützen, aufrechterhalten, bis die Transaktion abgeschlossen ist. Die Zeitdauer der Aufrechterhaltung einer Sperre zum Schutz von Lesevorgängen durch eine Transaktion hängt von der Einstellung für die Transaktionsisolationsstufe ab und davon, ob die optimierte Sperrung aktiviert ist oder nicht.

  • Wenn die optimierte Sperrung nicht aktiviert ist, werden die für Schreibvorgänge erforderlichen Zeilen- und Seitensperren bis zum Ende der Transaktion beibehalten.

  • Wenn die optimierte Sperrung aktiviert ist, wird für die Dauer der Transaktion nur eine Sperre für die Transaktions-ID (TID) beibehalten. Unter der Standardisolationsstufe behalten Transaktionen die für Schreibvorgänge erforderlichen Zeilen- und Seitensperren nicht bis zum Ende der Transaktion bei. Dadurch verringert sich der Speicherbedarf für Sperren und die Notwendigkeit der Sperrenausweitung. Wenn die optimierte Sperrung aktiviert ist, wertet die Optimierung der Sperre nach der Qualifizierung (LAQ) Prädikate einer Abfrage in der zuletzt committeten Version der Zeile ohne Übernahme einer Sperre aus, wodurch sich die Nebenläufigkeit verbessert.

Alle Sperren, die von einer Transaktion aufrechterhalten werden, werden freigegeben, wenn die Transaktion abgeschlossen ist (d. h. ein Commit oder ein Rollback ausgeführt wurde).

Anwendungen fordern in der Regel Sperren nicht direkt an. Sperren werden intern durch eine Komponente der SQL Server-Datenbank-Engine verwaltet, die als Sperrenmanager bezeichnet wird. Wenn eine Instanz der SQL Server-Datenbank-Engine eine Transact-SQL-Anweisung verarbeitet, bestimmt der Abfrageprozessor der SQL Server Datenbank-Engine, auf welche Ressourcen zugegriffen werden soll. Der Abfrageprozessor ermittelt, welche Arten von Sperren zum Schützen der einzelnen Ressourcen basierend auf dem Zugriffstyp und der Einstellung für den Isolationsgrad der Transaktion erforderlich sind. Der Abfrageprozessor fordert dann die entsprechenden Sperren vom Sperrenmanager an. Der Sperrenmanager erteilt die Sperren, wenn keine Sperren von anderen Transaktionen aufrechterhalten werden, die einen Konflikt verursachen.

Sperrengranularität und -hierarchien

Die SQL Server-Datenbank-Engine verwendet multigranulare Sperren, die das Sperren unterschiedlicher Ressourcentypen durch eine Transaktion ermöglichen. Um die Kosten für das Sperren zu minimieren, sperrt die SQL Server-Datenbank-Engine automatisch Ressourcen auf einer für die Aufgabe geeigneten Stufe. Bei Verwendung von Sperren mit differenzierterer Granularität, z. B. Sperren für Zeilen, steigt die Parallelität, aber der Verwaltungsaufwand ist größer, da mehr Sperren aufrechterhalten werden müssen, wenn viele Zeilen gesperrt werden. Die Verwendung von Sperren mit gröberer Granularität, z. B. Sperren für Tabellen, wirkt sich nachteilig auf die Parallelität aus, da durch das Sperren einer gesamten Tabelle der Zugriff auf alle Teile der Tabelle für andere Transaktionen eingeschränkt wird. Der Verwaltungsaufwand nimmt jedoch ab, da weniger Sperren aufrechterhalten werden müssen.

Die SQL Server-Datenbank-Engine muss häufig Sperren auf einer höheren Granularitätsebene einrichten, um eine Ressource vollständig zu schützen. Diese Gruppe von Sperren auf mehreren Granularitätsebenen wird als Sperrenhierarchie bezeichnet. Um z. B. das Lesen eines Indexes vollständig zu schützen, muss eine Instanz der SQL Server-Datenbank-Engine gegebenenfalls freigegebene Sperren für Spalten und beabsichtigt-freigegebene Sperren für die Seiten und Tabellen einrichten.

Die folgende Tabelle zeigt die Ressourcen, die von der SQL Server-Datenbank-Engine gesperrt werden können.

Resource Beschreibung
RID Ein Zeilenbezeichner, der verwendet wird, um eine einzelne Zeile in einem Heap zu sperren.
SCHLÜSSEL Eine Zeilensperre in einem Index, die verwendet wird, um Schlüsselbereiche in serialisierbaren Transaktionen zu schützen.
PAGE Eine 8-KB-Seite in einer Datenbank, z. B. Daten- oder Indexseiten.
EXTENT Eine zusammenhängende Gruppe von acht Seiten, z. B. Datenseiten oder Indexseiten.
HoBT 1 Ein Heap oder eine B-Struktur. Eine Sperre, die eine B-Struktur (Index) oder den Heap von Datenseiten in einer Tabelle schützt, die keinen gruppierten Index besitzt.
TABLE 1 Die vollständige Tabelle mit sämtlichen Daten und Indizes.
FILE Eine Datenbankdatei.
APPLICATION Eine von der Anwendung angegebene Ressource.
METADATA Metadatensperren.
ALLOCATION_UNIT Eine Zuordnungseinheit.
DATABASE Die gesamte Datenbank.
XACT 2 Transaktions-ID (TID)-Sperre, die bei der optimierten Sperrung verwendet wird. Siehe Transaktions-ID (TID)-Sperrung.

1 HoBT- und TABLE-Sperren können durch die LOCK_ESCALATION-Option von ALTER TABLE beeinflusst werden.

2 Weitere Sperrressourcen sind für XACT-Sperrressourcen verfügbar, siehe Diagnose-Ergänzungen für die optimierte Sperrung.

Sperrmodi

Die SQL Server-Datenbank-Engine sperrt Ressourcen mithilfe unterschiedlicher Sperrmodi, die bestimmen, wie gleichzeitige Transaktionen auf Ressourcen zugreifen können.

Die folgende Tabelle zeigt die Ressourcen-Sperrmodi, die von der SQL Server-Datenbank-Engine verwendet werden.

Sperrmodus Beschreibung
Freigegebene Sperre (Shared, S) Wird für Lesevorgänge verwendet, die Daten nicht ändern oder aktualisieren, wie z. B. SELECT-Anweisungen.
Updatesperre (U) Wird für Ressourcen verwendet, die aktualisiert werden können. Verhindert eine gängige Form des Deadlocks, die auftritt, wenn mehrere Sitzungen Ressourcen lesen, sperren und anschließend möglicherweise aktualisieren.
Exklusive Sperre (X) Wird bei Datenänderungen wie INSERT-, UPDATE- oder DELETE-Vorgängen verwendet. Stellt sicher, dass nicht mehrere Updates an derselben Ressource gleichzeitig vorgenommen werden können.
Absicht Wird verwendet, um eine Sperrhierarchie zu erstellen. Es gibt folgende Typen von beabsichtigten Sperren: beabsichtigte freigegebene Sperre (Intent Shared, IS), beabsichtigte exklusive Sperre (Intent Exclusive, IX) und freigegebene Sperre mit beabsichtigter exklusiver Sperre (Shared With Intent Exclusive, SIX).
Schema Wird beim Ausführen von Vorgängen verwendet, die vom Schema einer Tabelle abhängen. Es gibt folgende Typen von Schemasperren: Schemaänderungssperre (Sch-M) und Schemastabilitätssperre (Sch-S).
Massenaktualisierung (Bulk Update, BU) Wird beim Massenkopieren von Daten in eine Tabelle verwendet, wenn der TABLOCK-Hinweis angegeben ist.
Schlüsselbereich Schützt den von einer Abfrage gelesenen Zeilenbereich, wenn die serialisierbare Transaktionsisolationsstufe verwendet wird. Stellt sicher, dass keine anderen Transaktionen Zeilen einfügen können, die von den Abfragen der serialisierbaren Transaktion berücksichtigt werden können, falls diese erneut ausgeführt würden.

Freigegebene Sperren

Freigegebene Sperren (S) ermöglichen, dass Transaktionen eine Ressource gleichzeitig lesen können (SELECT), wenn die Steuerung durch eingeschränkte Parallelität aktiviert ist. Andere Transaktionen können die Daten nicht ändern, während freigegebene Sperren (S) für die Ressource eingerichtet sind. Freigegebene Sperren (S) einer Ressource werden aufgehoben, sobald der Lesevorgang abgeschlossen ist, es sei denn, die Isolationsstufe der Transaktion wird auf REPEATABLE READ oder höher festgelegt oder ein Sperrhinweis wird verwendet, um freigegebene Sperren (S) für die Dauer der Transaktion beizubehalten.

Updatesperren

Die Datenbank-Engine platziert Updatesperren (U), wenn sie die Ausführung eines Updates vorbereitet. U-Sperren sind mit S-Sperren kompatibel, aber bei einer bestimmten Ressource kann es immer nur für eine Transaktion eine U-Sperre geben. Das ist entscheidend – bei einer Ressource können viele Transaktionen gleichzeitig S-Sperren haben, aber nur eine Transaktion kann eine U-Sperre haben. Updatesperren (U) werden letztlich auf exklusive Sperren (X) für das Aktualisieren einer Zeile hochgestuft..

Updatesperren (U) können auch von Abfragen verwendet werden, die kein UPDATE ausführen, wenn der Tabellenhinweis UPDLOCK in der Abfrage angegeben ist. Es ist üblich, dass Anwendungen nach dem Muster „eine Zeile auswählen, dann die Zeile aktualisieren“ vorgehen, bei dem Lese- und Schreibvorgänge innerhalb der Transaktion explizit voneinander getrennt sind. Wenn in diesem Fall die Isolationsstufe „Repeatable read“ oder Serializable ist, ist damit zu rechnen, dass gleichzeitige Updates einen Deadlock verursachen. Stattdessen könnten Anwendungen nach dem Muster „Eine Zeile mit dem Hinweis UPDLOCK auswählen, dann die Zeile aktualisieren" vorgehen.

  • Bei REPEATABLE READ- oder SERIALIZABLE-Transaktionen liest die Transaktion Daten, wozu sie eine freigegebene Sperre (S) für die Ressource einrichtet, und ändert anschließend die Daten, was eine Umwandlung der Sperre in eine exklusive Sperre (X) erfordert. Wenn zwei Transaktionen freigegebene Sperren (S) für eine Ressource einrichten und anschließend versuchen, Daten gleichzeitig zu aktualisieren, versucht die erste Transaktion, die Sperre in einer exklusiven Sperre (X) umzuwandeln. Diese Umwandlung von freigegebener zu exklusiver Sperre muss aufgeschoben werden, da die exklusive Sperre der einen Transaktion nicht kompatibel mit der freigegebenen Sperre (S) der anderen Transaktion ist. Es ergibt sich ein Sperrenwartevorgang. Die zweite Transaktion versucht nun ebenfalls, eine exklusive Sperre (X) für das Update einzurichten. Da beide Transaktionen das Umwandeln in eine exklusive Sperre (X) versuchen und darauf warten, dass die andere Transaktion die freigegebene Sperre (S) aufhebt, kommt es zu einem Deadlock.

  • In der standardmäßigen Isolationsstufe Read commited sind S-Sperren kurze Dauer und werden bei Gebrauch umgehend aufgehoben. Es ist unwahrscheinlich, dass Sperren mit kurzer Dauer zu Deadlocks führen.

  • Wenn in einem Schreibvorgang der Hinweis UPDLOCK verwendet wird, muss die Transaktion Zugriff auf die aktuelle Version der Zeile haben. Wenn die aktuelle Version nicht mehr sichtbar ist, sollte es möglich sein, Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict zu empfangen, wenn die SNAPSHOT-Isolation verwendet wird. Ein Beispiel finden Sie unter Arbeiten mit der Momentaufnahmeisolation.

Exklusive Sperren

Exklusive Sperren (X) verhindern, dass Transaktionen gleichzeitig auf eine Ressource zugreifen. Eine exklusive Sperre (X) bewirkt, dass keine andere Transaktion Daten ändern kann. Lesevorgänge können nur mithilfe des NOLOCK-Hinweises oder der READ UNCOMMITTED-Isolationsstufe ausgeführt werden.

Datenänderungsanweisungen wie INSERT, UPDATE und DELETE setzen sowohl Änderungs- als auch Lesevorgänge voraus. Die Anweisung führt zunächst Lesevorgänge aus, um die Daten einzulesen, und anschließend die erforderlichen Änderungsvorgänge. Daher machen Datenänderungsanweisungen normalerweise sowohl freigegebene als auch exklusive Sperren erforderlich. Eine UPDATE-Anweisung kann beispielsweise Zeilen einer Tabelle ändern, die auf einem Join mit einer anderen Tabelle basieren. In diesem Fall fordert die UPDATE-Anweisung freigegebene Sperren für die Zeilen in der verknüpften Tabelle an, sowie exklusive Sperren für die zu aktualisierenden Zeilen.

Beabsichtigte Sperren

Die SQL Server-Datenbank-Engine verwendet beabsichtigte Sperren, um das Platzieren einer freigegebenen (S) oder exklusiven Sperre (X) auf eine Ressource zu schützen, die sich weiter unten in der Sperrhierarchie befinden. Beabsichtige Sperren heißen „beabsichtigte Sperren“, weil sie vor Sperren auf untergeordneten Ebenen eingerichtet werden und damit die Absicht ausdrücken, Sperren auf untergeordneten Ebenen zu platzieren.

Beabsichtigte Sperren werden aus zwei Gründen verwendet:

  • Um zu verhindern, dass andere Transaktionen Ressourcen übergeordneter Ebenen ändern und damit die Sperren untergeordneter Ebenen ungültig werden.
  • Um die Effizienz der SQL Server-Datenbank-Engine beim Erkennen von Sperrkonflikten auf einer höheren Granularitätsebene zu steigern.

Eine beabsichtigte freigegebene Sperre auf Tabellenebene wird also beispielsweise angefordert, bevor freigegebene Sperren (S) für Seiten oder Zeilen in dieser Tabelle angefordert werden. Durch Festlegen einer beabsichtigten Sperre auf Tabellenebene wird verhindert, dass andere Transaktionen anschließend eine exklusive Sperre (X) für die Tabelle einrichten können, die diese Seite enthält. Beabsichtigte Sperren tragen zur Leistungsverbesserung bei, da die SQL Server-Datenbank-Engine beabsichtige Sperren nur auf Tabellenebene überprüft, um zu bestimmen, ob eine Transaktion für diese Tabelle problemlos eine Sperre einrichten kann. Dadurch ist es nicht mehr erforderlich, jede Zeilen- oder Seitensperre in der Tabelle zu überprüfen, um zu ermitteln, ob eine Transaktion die gesamte Tabelle sperren kann.

Beabsichtigte Sperren umfassen beabsichtigte freigegebene (Intent Shared, IS), beabsichtigte exklusive (Intent Exclusive, IX) und freigegebene mit beabsichtigten exklusiven (Shared With Intent Exclusive, SIX) Sperren.

Sperrmodus Beschreibung
Beabsichtigte freigegebene Sperre (Intent Shared, IS) Schützt angeforderte oder eingerichtete freigegebene Sperren bestimmter (aber nicht aller) Ressourcen untergeordneter Ebenen in der Hierarchie.
Beabsichtigte exklusive Sperre (Intent Exclusive, IX) Schützt angeforderte oder eingerichtete exklusive Sperren bestimmter (aber nicht aller) Ressourcen untergeordneter Ebenen in der Hierarchie. IX ist eine Obermenge der beabsichtigten freigegebenen Sperre und schützt auch vor Anforderung freigegebener Sperren auf Ressourcen untergeordneter Ebenen in der Hierarchie.
Freigegebene Sperre mit beabsichtigter exklusiver Sperre (Shared With Intent Exclusive, SIX) Schützt angeforderte oder eingerichtete freigegebene Sperren aller Ressourcen untergeordneter Ebenen in der Hierarchie sowie beabsichtigte exklusive Sperren bestimmter (aber nicht aller) Ressourcen untergeordneter Ebenen in der Hierarchie. Gleichzeitige beabsichtigte freigegebene Sperren auf der Ressource der obersten Ebene sind zugelassen. So werden beispielsweise bei einer Sperre des Typs SIX für eine Tabelle auch beabsichtigte exklusive Sperren für die zu ändernden Seiten sowie exklusive Sperren für die zu ändernden Zeilen eingerichtet. Es kann jeweils nur eine Sperre des Typs SIX pro Ressource eingerichtet werden, durch die Updates an der Ressource durch andere Transaktionen verhindert werden. Dennoch können andere Transaktionen Ressourcen, die sich weiter unten in der Hierarchie befinden, lesen, indem sie beabsichtigte freigegebene Sperren auf Tabellenebene einrichten.
Beabsichtigte Aktualisierungssperre (IU) Schützt angeforderte oder eingerichtete Updatesperren aller Ressourcen untergeordneter Hierarchieebenen. IU-Sperren werden nur mit Seitenressourcen verwendet. IU-Sperren werden zu IX-Sperren konvertiert, wenn ein Updatevorgang ausgeführt wird.
Gemeinsame Sperre mit beabsichtigter Aktualisierungssperre (SIU) Eine Kombination der Sperren vom Typ S und IU, die sich aus der separaten Einrichtung dieser Sperren und dem gleichzeitigen Beibehalten beider Sperren ergibt. Nehmen Sie beispielsweise an, eine Transaktion führt eine Abfrage mit dem PAGLOCK-Hinweis und anschließend einen Updatevorgang aus. Die Abfrage mit dem PAGLOCK-Hinweis richtet also die S-Sperre ein, wohingegen der Updatevorgang die IU-Sperre einrichtet.
Aktualisierungssperre mit beabsichtigter exklusiver Sperre (UIX) Eine Kombination der Sperren vom Typ U und IX, die sich aus dem separaten Einrichten dieser Sperren und dem gleichzeitigen Beibehalten beider Sperren ergibt.

Schemasperren

Sperren des Typs Sch-M (Schema Modification, Schemaänderung) werden von der SQL Server-Datenbank-Engine verwendet, wenn für eine Tabelle ein DDL-Vorgang (Data Definition Language, Datendefinitionssprache) ausgeführt wird, wie etwa das Hinzufügen einer Spalte oder Löschen einer Tabelle. Während die Sch-M-Sperre besteht, werden gleichzeitige Zugriffe auf die Tabelle verhindert. Dies bedeutet, dass die Sch-M-Sperre alle externen Vorgänge blockiert, bis die Sperre aufgehoben wird.

Einige DML-Vorgänge (Data Manipulation Language), z. B. das Abschneiden von Tabellen, verhindern mithilfe von Sch-M-Sperren, dass gleichzeitige Vorgänge auf die betroffenen Tabellen zugreifen.

Die SQL Server-Datenbank-Engine verwendet Sperren des Typs Sch-S (Schemastabilität) beim Kompilieren und Ausführen von Abfragen. Sch-S-Sperren blockieren keine Transaktionssperren, auch keine exklusive Sperren (X). Daher können während der Kompilierung einer Abfrage andere Transaktionen, einschließlich Transaktionen mit exklusiven Sperren (X) auf Tabellenebene, weiterhin ausgeführt werden. Gleichzeitige DDL-Vorgänge und gleichzeitige DML-Vorgänge, die Sch-M-Sperren abrufen, können für die Tabelle jedoch nicht ausgeführt werden.

Massenupdatesperren

Massenupdatesperren (BU) werden verwendet, damit mehrere Threads gleichzeitig Daten in dieselbe Tabelle laden können, während sie zugleich anderen Prozessen, die keine Daten massenkopieren, keinen Zugriff auf die Tabelle gewähren. Die SQL Server-Datenbank-Engine verwendet Massenupdatesperren (Bulk Update, BU), wenn die folgenden Bedingungen zutreffen.

  • Zum Massenkopieren von Daten in eine Tabelle verwenden Sie die Transact-SQL BULK INSERT-Anweisung oder die OPENROWSET(BULK)-Funktion. Sie können auch einen der Masseneinfügungs-API-Befehle wie .NET SqlBulkCopy, OLEDB-FastLoad-APIs oder die ODBC-APIs für das Massenkopieren verwenden.
  • Es wird entweder der TABLOCK-Hinweis angegeben oder die Tabellenoption table lock on bulk load mithilfe von sp_tableoption festgelegt.

Tipp

Im Gegensatz zur BULK INSERT-Anweisung, die eine weniger restriktive Massenupdatesperre (Bulk Update, BU) enthält, weist INSERT INTO…SELECT mit dem TABLOCK-Hinweis eine beabsichtigte exklusive Sperre (Intent Exclusive, IX) für die Tabelle auf. Das bedeutet, dass Sie keine Zeilen mit parallelen Einfügevorgängen einfügen können.

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 Transaktionsisolationsstufe SERIALIZABLE. Durch Schlüsselbereichssperren werden Phantomlesezugriffe verhindert. Indem die Schlüsselbereiche zwischen Zeilen geschützt werden, wird auch verhindert, dass beim Zugreifen von Transaktionen auf Recordsets Phantomeinfügungen oder -löschungen erfolgen.

Kompatibilität von Sperren

Durch die Kompatibilität von Sperren wird gesteuert, ob mehrere Transaktionen gleichzeitig Sperren für dieselbe Ressource einrichten können. Wenn eine Ressource bereits durch eine andere Transaktion gesperrt wurde, kann eine erneute Sperranforderung nur gewährt werden, wenn der Modus der angeforderten Sperre mit dem Modus der vorhandenen Sperre kompatibel ist. Wenn der Modus der angeforderten Sperre nicht mit dem Modus der vorhandenen Sperre kompatibel ist, wartet die Transaktion, von der die neue Sperre angefordert wird, bis die vorhandene Sperre aufgehoben wird oder bis das Timeoutintervall der Sperre abgelaufen ist. So sind z. B. keine anderen Sperrmodi mit exklusiven Sperren kompatibel. Wenn eine exklusive Sperre (X) eingerichtet ist, kann eine andere Transaktion eine Sperre jeglicher Art (freigegeben, Update oder exklusiv) für die Ressource erst dann einrichten, wenn die exklusive Sperre (X) am Ende der ersten Transaktion aufgehoben wird. Falls hingegen eine freigegebene Sperre (Shared, S) auf eine Ressource angewendet wurde, können andere Transaktionen ebenfalls eine freigegebene Sperre oder eine Updatesperre (Update, U) auf dieses Element anwenden, selbst wenn die erste Transaktion noch nicht beendet ist. Andere Transaktionen können jedoch eine exklusive Sperre erst dann einrichten, wenn die freigegebene Sperre aufgehoben wurde.

Die folgende Tabelle stellt die Kompatibilität der am häufigsten auftretenden Sperrmodi dar.

Vorhandener erteilter Modus IS S U IX SIX X
Angeforderter Modus
Beabsichtigte freigegebene Sperre (Intent Shared, IS) Ja Ja Ja Ja Ja No
Freigegebene Sperre (Shared, S) Ja Ja Ja Nr. Nr. No
Updatesperre (U) Ja Ja Nr. Nr. Nr. No
Beabsichtigte exklusive Sperre (Intent Exclusive, IX) Ja Nr. Nein Ja Nr. No
Freigegebene Sperre mit beabsichtigter exklusiver Sperre (Shared With Intent Exclusive, SIX) Ja Nr. Nr. Nr. Nr. No
Exklusive Sperre (X) No Nr. Nr. Nr. Nr. Nein

Hinweis

Eine beabsichtigte exklusive Sperre (IX) ist mit einem Sperrmodus des Typs IX kompatibel, da IX nur die Absicht zum Aktualisieren einiger statt aller Zeilen anzeigt. Andere Transaktionen, die versuchen, einige der Zeilen zu lesen oder zu aktualisieren, werden ebenfalls zugelassen, sofern es sich nicht um dieselben Zeilen handelt, die von anderen Transaktionen aktualisiert werden. Wenn zwei Transaktionen versuchen, dieselbe Zeile zu aktualisieren, wird beiden Transaktionen eine IX-Sperre auf Tabellen- und Seitenebene erteilt. Bei nur einer Transaktion wird jedoch eine X-Sperre auf Zeilenebene erteilt. Die andere Transaktion muss warten, bis die Sperre auf Zeilenebene aufgehoben wird.

Verwenden Sie die folgende Tabelle, um die Kompatibilität aller in SQL Server verfügbaren Sperrmodi zu ermitteln.

A table showing a matrix of lock conflicts and compatibility.

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 Transaktionsisolationsstufe SERIALIZABLE. Für die Isolationsstufe SERIALIZABLE 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 alle Zeilen liest, deren Schlüsselwerte mit der Bedingung BETWEEN 'AAA' AND 'CZZ' übereinstimmen. 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 Bereichssperre, freigegebene Ressourcensperre; serialisierbarer Bereichsscan.
RangeS U RangeS-U Freigegebene Sperre für Bereich und Updatesperre für Ressource; serialisierbarer Updatescan.
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.

Vorhandener erteilter Modus S U X RangeS-S RangeS-U RangeI-N RangeX-X
Angeforderter Modus
Freigegebene Sperre (Shared, S) Ja Ja Keine Ja Ja Ja No
Updatesperre (U) Ja Nr. Nein Ja Keine Ja No
Exklusive Sperre (X) No Nr. Nr. Nr. Nein Ja No
RangeS-S Ja Ja Keine Ja Ja Nr. No
RangeS-U Ja Nr. Nein Ja Nr. Nr. No
RangeI-N Ja Ja Ja Nr. Nein Ja No
RangeX-X No Nr. Nr. Nr. Nr. Nr. No

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.

A diagram of a sample of a Btree.

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“ entspricht 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 ohne optimierte Sperrung

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. (Die Datenbankoption READ_COMMITTED_SNAPSHOT und die SNAPSHOT-Isolationsebene lassen auch Lesevorgänge aus einer Zeilenversion des vorherigen Commitzustands zu.)

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 Abfrageoptimierungshinweise, wie ROWLOCK, PAGLOCK oder TABLOCK, angegeben werden. Wenn PAGLOCK oder TABLOCK verwendet wird, hebt die SQL Server-Datenbank-Engine 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.

Löschvorgang mit optimierter Sperrung

Beim Löschen eines Werts innerhalb einer Transaktion werden die Zeilen- und Seitensperren inkrementell eingerichtet und aufgehoben und nicht für die Dauer der Transaktion beibehalten. Angenommen, folgende DELETE-Anweisung wird ausgeführt:

DELETE mytable
WHERE name = 'Bob';

Eine TID-Sperre wird für die Dauer der Transaktion auf allen geänderten Zeilen platziert. Für die TID der Indexeinträge, die dem Namen Bob entsprechen, wird eine Sperre eingerichtet. Bei optimierter Sperrung werden Seiten- und Zeilensperren weiterhin für Updates eingerichtet, aber jede Seiten- und Zeilensperre wird unmittelbar nach der Aktualisierung jeder Zeile wieder aufgehoben. Die TID-Sperre schützt die Zeilen bis zum Abschluss der Transaktion vor einer Aktualisierung. Eine Transaktion, die versucht, den Wert Bob zu lesen, einzufügen oder zu löschen, wird so lange blockiert, bis für die löschende Transaktion entweder ein Commit oder ein Rollback ausgeführt wird. (Die Datenbankoption READ_COMMITTED_SNAPSHOT und die SNAPSHOT-Isolationsebene lassen auch Lesevorgänge aus einer Zeilenversion des vorherigen Commitzustands zu.)

Ansonsten sind die Sperrmechanismen eines Löschvorgangs gleich wie ohne optimierte Sperrung.

Einfügevorgang ohne optimierte Sperrung

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.

Einfügevorgang mit optimierter Sperrung

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. Zeilen- und Seitensperren werden nur selten eingerichtet. Sie werden nur dann eingerichtet, wenn ein Onlineindex in Bearbeitung ist oder wenn es in der Instanz serialisierbare Transaktionen gibt. Wenn Zeilen- und Seitensperren eingerichtet werden, werden sie schnell wieder aufgehoben und nicht für die Dauer der Transaktion beibehalten. Die Serialisierbarkeit wird bereits dann aufrechterhalten, wenn der eingefügte Schlüsselwert bis zum Ende der Transaktion mit einer exklusiven TID-Sperre versehen wird. Angenommen, folgende INSERT-Anweisung wird ausgeführt:

INSERT mytable VALUES ('Dan');

Bei optimierter Sperrung wird nur dann eine RangeI-N-Sperre eingerichtet, wenn es mindestens eine Transaktion gibt, die die SERIALIZABLE-Isolationsebene in der Instanz verwendet. 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.

Sperrenausweitung

Die Sperrenausweitung ist der Prozess der Umwandlung vieler differenzierter Sperren in wenige undifferenzierte Sperren, wodurch sich der Systemaufwand verringert und die Wahrscheinlichkeit von Parallelitätskonflikten erhöht.

Die Sperrenausweitung verhält sich je nachdem, ob die optimierte Sperrung aktiviert ist, unterschiedlich.

Sperrenausweitung ohne optimierte Sperrung

Wenn die SQL Server-Datenbank-Engine Sperren auf niedriger Ebene eingerichtet, werden auch beabsichtigte Sperren für die Objekte eingerichtet, die die Objekte der niedrigen Ebene enthalten:

  • Beim Sperren von Zeilen oder Indexschlüsselbereichen richtet die Datenbank-Engine eine beabsichtigte Sperre für die Seiten ein, die diese Zeilen oder Schlüssel enthalten.
  • Beim Sperren von Seiten richtet die Datenbank-Engine 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 Seitensperren für die folgenden Objekte angefordert:
    • Seiten auf Blattebene von nicht gruppierten Indizes
    • Datenseiten von gruppierten Indizes
    • Heap-Datenseiten

Die Datenbank-Engine 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 die Datenbank-Engine z. B. 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 die Datenbank-Engine, 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 die versuchte Sperrenausweitung erfolgreich ist und die vollständige Tabellensperre eingerichtet wird, werden alle von der Transaktion im Heap oder Index geführten Sperren für Heap- oder B-Struktur, Seite (PAGE) oder Zeilenebene (Row-Level) aufgehoben. Wenn die vollständige Sperre nicht erreicht wird, erfolgt keine Sperrenausweitung, und die Datenbank-Engine richtet weiterhin Zeilen-, Schlüssel- oder Seitensperren ein.

Die Datenbank-Engine weitet keine Zeilen- oder Schlüsselbereichssperren zu Seitensperren aus, sondern weitet diese direkt zu Tabellensperren aus. Ebenso werden Seitensperren immer zu Tabellensperren ausgeweitet. Das Sperren von partitionierten Tabellen kann auf die HoBT-Ebene für die zugehörige Partition statt auf die Tabellensperre ausgeweitet werden. Eine Sperre auf HoBT-Ebene sperrt nicht zwangsläufig die angeglichenen HoBTs für die Partition.

Hinweis

Sperren auf HoBT-Ebene erhöhen in der Regel die Parallelität, aber auch die Wahrscheinlichkeit von Deadlocks, wenn Transaktionen, die unterschiedliche Partitionen sperren, jeweils ihre exklusiven Sperren auf die anderen Partitionen ausweiten möchten. In seltenen Fällen kann die Granularität der TABLE-Sperrung besser sein.

Wenn beim Versuch zur Sperrenausweitung ein Fehler erzeugt wird, weil von gleichzeitigen Transaktionen miteinander im Konflikt stehende Sperren gehalten werden, versucht die Datenbank-Engine 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 die Datenbank-Engine 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 beginnt, bevor die Anweisung auf eine Tabelle zugegriffen hat, wird nicht versucht, die Sperren auf diese Tabelle auszuweiten. Wenn die Sperrenausweitung erfolgreich ist, werden alle Sperren, die von der Transaktion in einer vorherigen Anweisung eingerichtet wurden und bei Beginn des Ereignisses noch vorhanden sind, ausgeweitet, wenn die Tabelle mit einem Verweis der aktuellen Anweisung in das Ausweitungsereignis einbezogen wird.

Nehmen wir beispielsweise an, eine Sitzung:

  • beginnt eine Transaktion,
  • Aktualisiert TableA. Dadurch werden exklusive Zeilensperren in TableA generiert, die bis zum Abschluss der Transaktion beibehalten werden.
  • Aktualisiert TableB. Dadurch werden exklusive Zeilensperren in TableB generiert, die bis zum Abschluss der Transaktion beibehalten werden.
  • 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. Dazu gehören sowohl die freigegebenen Sperren aus der SELECT-Anweisung als auch die exklusiven Sperren aus der vorherigen UPDATE-Anweisung. 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.

Sperrenausweitung mit optimierter Sperrung

Mit der optimierten Sperrung lässt sich der Speicherbedarf für Sperren reduzieren, da nur sehr wenige Sperren für die Dauer der Transaktion beibehalten werden. Wenn die SQL Server-Datenbank-Engine Zeilen- und Seitensperren einrichtet, kann es auch zur Sperrenausweitung kommen, aber weitaus seltener. Bei der optimierten Sperrung kann die Sperrenausweitung normalerweise erfolgreich vermieden werden, was die Anzahl der Sperren und den Umfang des Speicherbedarfs für Sperren verringert.

Wenn die optimierte Sperrung aktiviert und in der Standardisolationsebene READ COMMITED wirksam ist, hebt die Datenbank-Engine Zeilen- und Seitensperren auf, sobald der Schreibvorgang abgeschlossen ist. Abgesehen von einer einzigen Transaktions-ID-Sperre (TID) werden keine Zeilen- und Seitensperren für die Dauer der Transaktion beibehalten. Das verringert die Wahrscheinlichkeit einer Sperrenausweitung.

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 der Datenbank-Engine überschreitet den Arbeitsspeicher oder die Konfigurationsschwellenwerte.

Wenn die Sperrenausweitung aufgrund von Sperrkonflikten nicht möglich ist, löst die Datenbank-Engine die Sperrenausweitung in regelmäßigen Abständen aus, sobald jeweils 1.250 neue Sperren eingerichtet werden.

Ausweitungsschwellenwert für eine Transact-SQL-Anweisung

Wenn die Datenbank-Engine alle 1.250 neu abgerufenen Sperren auf mögliche Ausweitungen prüft, erfolgt eine Sperrenausweitung nur dann, wenn eine Transact-SQL-Anweisung mindestens 5.000 Sperren für einen einzelnen Verweis einer Tabelle abgerufen hat. Die Sperrenausweitung wird ausgelöst, wenn eine Transact-SQL-Anweisung mindestens 5.000 Sperren für einen einzelnen Verweis einer Tabelle abruft. Die Sperrenausweitung wird beispielsweise nicht ausgelöst, wenn eine Anweisung 3.000 Sperren in einem Index und 3.000 Sperren in einem anderen Index der gleichen Tabelle einrichtet. Ebenso wird die Sperrenausweitung nicht ausgelöst, wenn eine Anweisung über eine Selbstverknüpfung in einer Tabelle verfügt und jeder Verweis auf die Tabelle nur 3.000 Sperren in der Tabelle einrichtet.

Die Sperrenausweitung ergibt sich nur für Tabellen, auf die zu dem Zeitpunkt zugegriffen wurde, zu dem die Ausweitung ausgelöst wurde. Angenommen, eine einzelne SELECT-Anweisung ist ein Join, der 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 die Datenbank-Engine 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 kleiner als 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 der Datenbank-Engine

Immer wenn die Anzahl der Sperren den Speicherschwellenwert für die Sperrenausweitung überschreitet, löst die Datenbank-Engine die Sperrenausweitung aus. Der Speicherschwellenwert richtet sich nach der Einstellung der Konfigurationsoption „locks“:

  • Wenn die locks-Option auf die Standardeinstellung 0 festgelegt ist, wird der Schwellenwert der Sperrenausweitung erreicht, wenn der von Sperrobjekten belegte Speicheranteil 24 % des von der Datenbank-Engine verwendeten Speichers (ausschließlich AWE-Speicher) beträgt. Die Datenstruktur zum Darstellen einer Sperre hat eine Länge von ca. 100 Byte. Dieser Schwellenwert ist dynamisch, da die Datenbank-Engine 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).

Die Datenbank-Engine 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

Im Fall einer Sperrenausweitung ist die für den Heap oder Index ausgewählte Sperre stark genug, um die Anforderungen der restriktivsten Sperre auf niedrigerer Ebene zu erfüllen.

Nehmen wir beispielsweise an, 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 diese 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 andere für die Tabelle.

Die SELECT-Anweisung richtet diese Sperren ein:

  • Freigegebene Sperren (S) für alle gelesenen Datenzeilen, sofern die Zeile nicht bereits durch eine X-Sperre aus der UPDATE-Anweisung geschützt ist.
  • Beabsichtigte freigegebene Sperren für alle gruppierten Indexseiten, die diese Zeilen enthalten, sofern die Seite nicht bereits durch eine IX-Sperre geschützt ist.
  • Keine Sperre für den gruppierten Index oder die gruppierte Tabelle, da sie bereits durch IX-Sperren geschützt sind.

Wenn die SELECT-Anweisung genügend Sperren einrichtet, um die Sperrenausweitung auszulösen und die Ausweitung erfolgreich verläuft, wird die IX-Sperre der Tabelle in eine X-Sperre umgewandelt, und alle Zeilen-, Seiten- und Indexsperren werden aufgehoben. Durch die X-Sperre der Tabelle werden sowohl Updates als auch die Lesevorgänge geschützt.

Verringern von Sperrung und Ausweitung

In den meisten Fällen erzielt die Datenbank-Engine die beste Leistung, wenn sie mit ihren Standardeinstellungen zur Sperrung und Sperrenausweitung arbeitet.

  • Nutzen Sie die Vorteile der optimierten Sperrung.

    • Die optimierte Sperrung verfügt über einen verbesserten Sperrmechanismus, der den Speicherbedarf für Sperren und das Blockieren von gleichzeitigen Transaktionen reduziert. Wenn die optimierte Sperrung aktiviert ist, wird es sehr viel unwahrscheinlicher, dass die Sperrenausweitung jemals ausgelöst wird.
    • Vermeiden Sie die Verwendung von Tabellenhinweisen mit optimierter Sperrung. Tabellenhinweise können die Effektivität der optimierten Sperrung verringern.
    • Aktivieren Sie READ_COMMITTED_SNAPSHOT in der Datenbank, um aus der optimierten Sperrung den größten Nutzen zu ziehen. Das ist die Standardisolationsstufe in Azure SQL-Datenbank.
    • Für die optimierte Sperrung muss die beschleunigte Datenbankwiederherstellung (ADR) für die Datenbank aktiviert sein.

Wenn eine Instanz der Datenbank-Engine jedoch viele Sperren generiert und sich häufige Sperrenausweitungen ergeben, sollten Sie nach den folgenden Strategien versuchen, das Ausmaß der Sperrung zu verringern:

  • Verwenden Sie eine Isolationsstufe, die keine freigegebenen Sperren für Lesevorgänge erzeugt:

    • READ COMMITTED-Isolationsstufe, wenn die Datenbankoption READ_COMMITTED_SNAPSHOT auf ON festgelegt 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 der Datenbank-Engine aus.

  • Verwenden Sie die Tabellenhinweise PAGLOCK oder TABLOCK, damit die Datenbank-Engine Seiten-, Heap- oder Indexsperren anstelle von Sperren auf Zeilenebene verwendet. Diese Option vergrößert jedoch das Problem, dass Benutzer andere Benutzer blockieren, die versuchen, auf dieselben Daten zuzugreifen, und sollte nicht in Systemen mit mehr als nur einigen wenigen gleichzeitigen Benutzern verwendet werden.

  • Verwenden Sie bei nicht aktivierter optimierter Sperrung 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.

  • Teilen Sie eine große Anzahl von Vorgängen in mehrere kleinere Vorgänge auf. Angenommen, Sie führen die folgende Abfrage aus, um mehrere hunderttausend alte Datensätze aus einer Überwachungstabelle zu entfernen, und stellen dann fest, dass sie eine Sperrenausweitung verursacht, die andere Benutzer blockiert:

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

    Wenn Sie einige Hundert dieser Datensätze auf einmal entfernen, können Sie die Anzahl der Sperren, die pro Transaktion anfallen, drastisch reduzieren und eine Sperrenausweitung verhindern. Zum Beispiel:

    SET ROWCOUNT 500
delete_more:
  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

  • Verringern Sie den Umfang von Abfragesperren, indem Sie die Abfrage so effizient wie möglich gestalten. Große Scans oder eine große Anzahl von Lesezeichenlookups erhöhen möglicherweise die Wahrscheinlichkeit einer Sperrenausweitung. Außerdem erhöht sich dadurch die Wahrscheinlichkeit von Deadlocks, und in der Regel gibt es negative Auswirkungen auf Parallelität und Leistung. Nachdem Sie die Abfrage ermittelt haben, die die Sperrenausweitung verursacht, suchen Sie nach Möglichkeiten, neue Indizes zu erstellen oder Spalten zu einem vorhandenen Index hinzuzufügen, um Index- oder Tabellenscans zu entfernen und die Effizienz der Indexsuchvorgänge zu maximieren. Verwenden Sie ggf. den Datenbankoptimierungsratgeber, um eine automatische Indexanalyse für die Abfrage auszuführen. Weitere Informationen finden Sie im Tutorial: Datenbankoptimierungsratgeber. Ein Ziel dieser Optimierung besteht darin, dass Indexsuchvorgänge so wenige Zeilen wie möglich zurückgeben, um die Kosten von Lesezeichenlookups zu minimieren (Maximieren der Selektivität des Indexes für eine bestimmte Abfrage). Wenn die Datenbank-Engine davon ausgeht, dass ein logischer Operator für den Lesezeichen-Lookupvorgang möglicherweise viele Zeilen zurückgibt, kann ein PREFETCH-Vorgang verwendet werden, um den Lesezeichen-Nachschlagevorgang auszuführen. Wenn die Datenbank-Engine PREFETCH für einen Lesezeichen-Lookupvorgang verwendet, muss Sie die Transaktionsisolationsstufe eines Teils der Abfrage für einen Teil der Abfrage auf wiederholbare Lesevorgänge erhöhen. Das bedeutet, dass das, was auf einer Isolationsebene mit Lesecommit ähnlich wie eine SELECT-Anweisung aussehen kann, viele Tausende von Schlüsselsperren (sowohl für den gruppierten Index als auch für einen nicht gruppierten Index) abrufen kann, was möglicherweise dazu führt, dass eine solche Abfrage die Sperrenausweitungs-Schwellenwerte überschreitet. Dies ist insbesondere dann wichtig, wenn Sie feststellen, dass es sich bei der ausgeweiteten Sperre um eine freigegebene Tabellensperre handelt, die jedoch bei der standardmäßigen Isolationsebene mit Lesecommit nicht häufig auftritt.

    Wenn ein Lesezeichen-Nachschlagevorgang MIT PREFETCH-Klausel die Ausweitung verursacht, sollten Sie in Erwägung ziehen, zusätzliche Spalten zum nicht gruppierten Index hinzuzufügen, der im logischen Operator „Index Seek“ oder „Index Scan“ unter dem logischen Operator „Bookmark Lookup“ im Abfrageplan enthalten ist. Unter Umständen ist es möglich, einen abdeckenden Index zu erstellen (einen Index, der alle Spalten in einer Tabelle enthält, die in der Abfrage verwendet wurden) oder zumindest einen Index, der die Spalten abdeckt, die für Verknüpfungskriterien oder in der WHERE-Klausel verwendet wurden, wenn es nicht praktikabel ist, alles in die Liste zum Auswählen von Spalten aufzunehmen. Bei einem Nested Loop-Join kann ebenfalls PREFETCH verwendet werden. Dies führt zu demselben Sperrverhalten.

  • Die Sperrenausweitung kann nicht auftreten, wenn eine andere SPID derzeit eine nicht kompatible Tabellensperre aufrecht erhält. Die Sperrenausweitung wird immer zu einer Tabellensperre ausgeweitet und niemals zu Seitensperren. Wenn der Versuch einer Sperrenausweitung fehlschlägt, weil eine andere SPID eine nicht kompatible TAB-Sperre enthält, wird außerdem die Abfrage, die die Ausweitung versucht hat, beim Warten auf eine TAB-Sperre nicht blockiert. Stattdessen werden Sperren weiterhin auf der ursprünglichen, präziseren Ebene (Zeile, Schlüssel oder Seite) abgerufen, und es erfolgen zusätzliche Ausweitungsversuche in regelmäßigen Abständen. Daher besteht eine Methode zum Verhindern der Sperrenausweitung für eine bestimmte Tabelle im Abrufen und Halten einer Sperre für eine andere Verbindung, die mit dem ausgweiteten Sperrentyp nicht kompatibel ist. Eine IX-Sperre (Intent Exclusive) auf Tabellenebene sperrt keine Zeilen oder Seiten, ist aber immer noch nicht mit einer ausgeweiteten S- (freigegeben) oder X-TAB-Sperre (exklusiv) kompatibel. Nehmen Sie beispielsweise an, dass Sie einen Batchauftrag ausführen müssen, der eine große Anzahl von Zeilen in der mytable-Tabelle ändert und eine Blockierung verursacht hat, die aufgrund von Sperrenausweitung auftritt. Wenn dieser Auftrag immer in weniger als einer Stunde abgeschlossen ist, können Sie einen Transact-SQL-Auftrag erstellen, der den folgenden Code enthält, und den neuen Auftrag so planen, dass er mehrere Minuten vor der Startzeit des Batchauftrags gestartet wird:

    BEGIN TRAN
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

    Mit dieser Abfrage wird eine IX-Sperre für mytable für eine Stunde abgerufen und aufrecht erhalten, wodurch die Sperrenausweitung für die Tabelle während dieser Zeit verhindert wird. Mit diesem Batch werden keine Daten geändert oder andere Abfragen blockiert (es sei denn, die andere Abfrage erzwingt eine Tabellensperre mit dem TABLOCK-Hinweis, oder ein Administrator hat Seiten- oder Zeilensperren mithilfe einer gespeicherten sp_indexoption-Prozedur deaktiviert).

  • Sie können auch mit den Ablaufverfolgungsflags 1211 und 1224 alle oder einige Sperrenausweitungen deaktivieren. Diese Ablaufverfolgungsflags deaktivieren jedoch die gesamte Sperrenausweitung global für die gesamte Datenbank-Engine. Sperrenausweitung ist in der Datenbank-Engine sehr nützlich, indem die Effizienz von Abfragen maximiert wird, die andernfalls durch den Mehraufwand für das Abrufen und Freigeben mehrerer Tausend Sperren verlangsamt würden. Sperrenausweitung trägt auch dazu bei, den erforderlichen Arbeitsspeicher zu minimieren, um Sperren nachzuverfolgen. Der Arbeitsspeicher, den die Datenbank-Engine dynamisch für Sperrstrukturen zuordnen kann, ist begrenzt. Wenn Sie also Sperrenausweitung deaktivieren und der Speicherbedarf für Sperren groß genug wird, können Versuche fehlschlagen, zusätzliche Sperren für eine beliebige Abfrage zuzuweisen, und der folgende Fehler tritt auf: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

    Hinweis

    Wenn der Fehler MSSQLSERVER_1204 auftritt, wird die Verarbeitung der aktuellen Anweisung beendet und ein Rollback der aktiven Transaktion ausgelöst. Wenn Sie den Datenbankdienst neu starten, können Benutzer durch das Rollback selbst blockiert werden, oder es tritt möglicherweise eine lange Datenbankwiederherstellungszeit auf.

    Hinweis

    Wenn Sie einen Sperrhinweis wie ROWLOCK verwenden, wird nur der anfängliche Sperrplan geändert. Sperrhinweise verhindern keine Sperrenausweitung.

Überwachen der Sperrenausweitung

Überwachen Sie die Sperrenausweitung mithilfe des erweiterten Ereignisses lock_escalation (xEvent). Das folgende Beispiel zeigt dies:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.    username))
ADD TARGET package0.histogram(SET source=N'sqlserver.database_id')
GO

Wichtig

Das erweiterte lock_escalation-Ereignis (xEvent) sollte anstelle der Ereignisklasse Lock:Escalation in SQL Trace oder SQL Profiler verwendet werden.

Dynamische Sperren

Wenn Sie Sperren auf niedriger Ebene verwenden, z. B. Zeilensperren, wird die Parallelität erhöht, da die Wahrscheinlichkeit geringer ist, dass zwei Transaktionen gleichzeitig Sperren für die gleichen Daten anfordern. Das Verwenden von Sperren auf niedriger Ebene erhöht außerdem die Anzahl der Sperren sowie der Ressourcen, die für deren Verwaltung erforderlich sind. Wenn Sie Tabellen- oder Seitensperren auf hoher Ebene verwenden, wird der Aufwand zwar gesenkt, jedoch auf Kosten der Parallelität.

A graph of locking cost vs. concurrency cost.

Die SQL Server-Datenbank-Engine legt Sperren dynamisch fest, um die kosteneffektivsten Sperren zu bestimmen. Die SQL Server-Datenbank-Engine bestimmt beim Ausführen einer Abfrage automatisch, welche Sperren, basierend auf den Merkmalen des Schemas und der Abfrage, am sinnvollsten sind. Um beispielsweise den Aufwand für die Sperren zu senken, kann der Abfrageoptimierer festlegen, dass beim Ausführen eines Indexscans Sperren auf Seitenebene für einen Index eingerichtet werden.

Dynamische Sperren bieten die folgenden Vorteile:

  • Vereinfachte Datenbankverwaltung. Datenbankadministratoren müssen die Sperreneskalationsschwellen nicht anpassen.
  • Gesteigerte Leistung. Die SQL Server-Datenbank-Engine minimiert den Aufwand des Systems mithilfe von Sperren, die speziell auf die Aufgabe zugeschnitten sind.
  • Anwendungsentwickler können sich auf die Entwicklung konzentrieren. Die SQL Server-Datenbank-Engine passt die Sperren automatisch an.

In SQL Server 2008 (10.0.x) und höheren Versionen hat sich das Verhalten der Sperrenausweitung mit der Einführung der LOCK_ESCALATION-Option geändert. Weitere Informationen finden Sie unter der LOCK_ESCALATION-Option von ALTER TABLE.

Sperrenpartitionierung

In großen Computersystemen können Sperren für häufig referenzierte Objekte einen Leistungsengpass darstellen, weil die Anforderung und Freigabe von Sperren zu Konflikten bei den internen Sperrenressourcen führt. Die Sperrenpartitionierung verbessert die Sperrenleistung, indem eine einzelne Sperrenressource in mehrere Sperrenressourcen aufgeteilt wird. Diese Funktion ist nur für Systeme mit 16 oder mehr CPUs verfügbar, wird automatisch aktiviert und kann nicht deaktiviert werden. Es können nur Objektsperren partitioniert werden. Objektsperren mit einem Untertyp werden nicht partitioniert. Weitere Informationen finden Sie unter sys.dm_tran_locks (Transact-SQL).

Grundlegendes zur Sperrenpartitionierung

Sperrtasks greifen auf verschiedene freigegebene Ressourcen zu, von denen zwei durch die Sperrenpartitionierung optimiert werden:

  • Spinlock: Diese Ressource steuert den Zugriff auf eine Sperrenressource wie z. B. eine Zeile oder Tabelle.

    Ohne die Sperrenpartitionierung verwaltet ein Spinlock alle Sperrenanforderungen für eine einzelne Sperrenressource. Bei Systemen mit umfangreicher Aktivität kann es zu Konflikten kommen, wenn Sperrenanforderungen darauf warten, dass das Spinlock verfügbar wird. Unter diesen Umständen kann die Anforderung von Sperren zu einem Engpass werden und sich negativ auf die Leistung auswirken.

    Um Konflikte bei einer einzelnen Sperrenressource zu verringern, teilt die Sperrenpartitionierung eine einzelne Sperrenressource in mehrere Sperrenressourcen auf, um die Auslastung auf mehrere Spinlocks zu verteilen.

  • Arbeitsspeicher: Wird zum Speichern der Strukturen von Sperrenressourcen verwendet.

    Sobald das Spinlock aktiviert wurde, werden die Sperrenstrukturen im Arbeitsspeicher gespeichert, und anschließend erfolgt der Zugriff auf diese Strukturen, und sie werden möglicherweise geändert. Die Verteilung des Sperrenzugriffs auf mehrere Ressourcen senkt die Notwendigkeit zur Übertragung von Arbeitsspeicherblöcken zwischen CPUs, was zu einer verbesserten Leistung führt.

Implementieren und Überwachen der Sperrenpartitionierung

Die Sperrenpartitionierung wird bei Systemen mit mindestens 16 CPUs standardmäßig aktiviert. Wenn die Sperrenpartitionierung aktiviert ist, wird eine Informationsmeldung im SQL Server-Fehlerprotokoll gespeichert.

Beim Aktivieren von Sperren für eine partitionierte Ressource gelten folgende Grundsätze:

  • Für eine einzelne Partition werden nur die Sperrmodi NL, SCH-S, IS, IU und IX aktiviert.

  • Freigegebene Sperren (S), exklusive Sperren (X) und andere Sperren in anderen Modi als NL, SCH-S, IS, IU und IX müssen für alle Partitionen aktiviert werden, beginnend mit der Partitions-ID 0 und nachfolgend in der Partitions-ID-Reihenfolge. Diese Sperren für eine partitionierte Ressource beanspruchen mehr Arbeitsspeicher als Sperren im selben Modus für eine nicht partitionierte Ressource, weil jede Partition effektiv eine separate Sperre ist. Der erhöhte Arbeitsspeicherbedarf richtet sich nach der Anzahl der Partitionen. Die Sperren-Leistungsindikatoren von SQL Server im Windows-Systemmonitor zeigen Informationen zum Arbeitsspeicher an, der von partitionierten und nicht partitionierten Sperren verwendet wird.

Beim Start einer Transaktion wird der Transaktion eine Partition zugewiesen. Bei der Transaktion verwenden alle Sperranforderungen, die partitioniert werden können, die der Transaktion zugewiesene Partition. Durch diese Methode wird der Zugriff auf Sperrenressourcen desselben Objekts durch unterschiedliche Transaktionen auf verschiedene Partitionen verteilt.

Die resource_lock_partition-Spalte in der dynamischen Verwaltungssicht (DMV, Dynamic Management View) von sys.dm_tran_locks stellt die Sperrenpartitions-ID für eine sperrenpartitionierte Ressource bereit. Weitere Informationen finden Sie unter sys.dm_tran_locks (Transact-SQL).

Arbeiten mit der Sperrenpartitionierung

Die folgenden Codebeispiele veranschaulichen die Verwendung der Sperrenpartitionierung. In den Beispielen werden zwei Transaktionen in zwei verschiedenen Sitzungen ausgeführt, um das Verhalten der Sperrenpartitionierung in einem Computersystem mit 16 CPUs zu zeigen.

Mit diesen These Transact-SQL-Anweisungen werden Testobjekte erstellt, die in den folgenden Beispielen verwendet werden.

-- Create a test table.
CREATE TABLE TestTable  (col1 int);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable
    ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable VALUES (1);
GO

Beispiel A

Sitzung 1:

Im Rahmen einer Transaktion wird eine SELECT-Anweisung ausgeführt. Aufgrund des HOLDLOCK-Sperrhinweises aktiviert und hält diese Anweisung eine beabsichtigte freigegebene Sperre für die Tabelle (in dieser Veranschaulichung werden Zeilen- und Seitensperren ignoriert). Die beabsichtigte freigegebene Sperre wird nur für die Partition aktiviert, die der Transaktion zugewiesen ist. In diesem Beispiel wird vorausgesetzt, dass die beabsichtigte freigegebene Sperre für die Partitions-ID 7 aktiviert wird.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sitzung 2:

Eine Transaktion wird gestartet, und die im Rahmen dieser Transaktion ausgeführte SELECT-Anweisung aktiviert und hält eine freigegebene Sperre (S) für die Tabelle. Die S-Sperre wird für alle Partitionen aktiviert, was mehrere Tabellensperren ergibt, und zwar eine für jede Partition. Auf einem System mit 16 CPUs werden z. B. 16 S-Sperren für die Sperrpartitions-IDs 0 bis 15 aktiviert. Da die S-Sperre mit der beabsichtigten freigegebenen Sperre kompatibel ist, die von der Transaktion in Sitzung 1 für die Partitions-ID 7 gehalten wird, kommt es zu keiner Blockierung zwischen den Transaktionen.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCK, HOLDLOCK);

Sitzung 1:

Die folgende SELECT-Anweisung wird unter der Transaktion ausgeführt, die unter Sitzung 1 immer noch aktiv ist. Aufgrund des exklusiven (X) Tabellenblockhinweises versucht die Transaktion, eine X-Sperre für die Tabelle zu aktivieren. Allerdings blockiert die S-Sperre, die durch die Transaktion in Sitzung 2 gehalten wird, die X-Sperre für die Partitions-ID 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Beispiel B

Sitzung 1:

Im Rahmen einer Transaktion wird eine SELECT-Anweisung ausgeführt. Aufgrund des HOLDLOCK-Sperrhinweises aktiviert und hält diese Anweisung eine beabsichtigte freigegebene Sperre für die Tabelle (in dieser Veranschaulichung werden Zeilen- und Seitensperren ignoriert). Die beabsichtigte freigegebene Sperre wird nur für die Partition aktiviert, die der Transaktion zugewiesen ist. In diesem Beispiel wird vorausgesetzt, dass die beabsichtigte freigegebene Sperre für die Partitions-ID 6 aktiviert wird.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sitzung 2:

Im Rahmen einer Transaktion wird eine SELECT-Anweisung ausgeführt. Aufgrund des TABLOCKX-Sperrhinweises versucht die Transaktion, eine exklusive Sperre (X) für die Tabelle zu aktivieren. Denken Sie daran, dass die X-Sperre für alle Partitionen beginnend mit der Partitions-ID 0 aktiviert werden muss. Die X-Sperre wird für alle Partitions-IDs von 0 bis 5 aktiviert, sie wird jedoch von der für Partitions-ID 6 aktivierten Sperre blockiert.

Für die Partitions-IDs 7 bis 15, die die X-Sperre noch nicht erreicht hat, können andere Transaktionen weiterhin Sperren aktivieren.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

Auf Zeilenversionsverwaltung basierende Isolationsstufen in der SQL Server-Datenbank-Engine

Ab SQL Server 2005 (9.x) führt die SQL Server-Datenbank-Engine eine Implementierung der vorhandenen Transaktionsisolationsstufe READ COMMITTED ein, die mithilfe der Zeilenversionsverwaltung eine Momentaufnahme auf Anweisungsebene bereitstellt. Die SQL Server-Datenbank-Engine bietet außerdem die Transaktionsisolationsstufe SNAPSHOT, die ebenfalls die Zeilenversionsverwaltung verwendet, um Momentaufnahmen auf Transaktionsebene bereitzustellen.

Die Zeilenversionsverwaltung ist ein allgemeines Framework in SQL Server, das beim Ändern oder Löschen einer Zeile einen "Kopie-bei-Schreibvorgang"-Mechanismus aufruft. Das setzt bei einer ausgeführten Transaktion voraus, dass die alte Zeilenversion für Transaktionen verfügbar sein muss, die einen früheren transaktionskonsistenten Zustand erfordern. Die Zeilenversionsverwaltung wird zur Unterstützung folgender Funktionen verwendet:

  • Erstellen der inserted- und deleted-Tabellen in Triggern. Für alle durch den Trigger geänderten Zeilen wird die Versionsverwaltung verwendet. Das schließt die Zeilen ein, die durch die Anweisung geändert wurden, mit der der Start des Triggers erfolgte, sowie alle vom Trigger bewirkten Datenänderungen.
  • Unterstützen von Multiple Active Result Sets (MARS). Wenn eine MARS-Sitzung eine Datenänderungsanweisung (z.B. INSERT, UPDATE oder DELETE) ausgibt, während es ein aktives Resultset gibt, wird für die von der Änderungsanweisung betroffenen Zeilen die Versionsverwaltung verwendet.
  • Unterstützen von Indexvorgängen, die die ONLINE-Option angeben.
  • Unterstützen von auf der Zeilenversionsverwaltung basierenden Transaktionsisolationsstufen:
    • Eine neue Implementierung der READ COMMITTED-Isolationsstufe, die die Zeilenversionsverwaltung verwendet, um die Lesekonsistenz auf Anweisungsebene zu gewährleisten.
    • Eine neue Isolationsstufe – Momentaufnahme, um die Lesekonsistenz auf der Transaktionsebene zu gewährleisten.

Die tempdb-Datenbank muss über ausreichend Speicherplatz verfügen, um die Versionen speichern zu können. Wenn tempdb voll ist, brechen Updatevorgänge die Versionsverwaltung ab und können fortgesetzt werden. Lesevorgänge können hingegen einen Fehler erzeugen, weil eine bestimmte Zeilenversion, die benötigt wird, nicht mehr vorhanden ist. Das wirkt sich auf Vorgänge wie Trigger, MARS und Onlineindizierung aus.

Das Verwenden der Zeilenversionsverwaltung für Read Committed- und Momentaufnahme-Transaktionen umfasst zwei Schritte:

  1. Festlegen von einer oder beider Datenbankoptionen READ_COMMITTED_SNAPSHOT und ALLOW_SNAPSHOT_ISOLATION auf ON.

  2. Festlegen der entsprechenden Transaktionsisolationsstufe in einer Anwendung:

    • Wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON gesetzt ist, verwenden Transaktionen, die die READ COMMITTED-Isolationsstufe festlegen, die Zeilenversionsverwaltung.
    • Wenn die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON gesetzt ist, können Transaktionen die Momentaufnahme-Isolationsstufe festlegen.

Wenn eine der beiden Datenbankoptionen READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION auf ON gesetzt ist, weist die SQL Server-Datenbank-Engine jeder Transaktion, die Daten bearbeitet, mithilfe der Zeilenversionsverwaltung eine Transaktionssequenznummer (XSN, Transaction Sequence Number) zu. Die Transaktionen starten zu dem Zeitpunkt, wenn eine BEGIN TRANSACTION-Anweisung ausgeführt wird. Allerdings startet die Transaktionssequenznummer mit dem ersten Lese- oder Schreibvorgang nach der BEGIN TRANSACTION-Anweisung. Die Transaktionssequenznummer wird bei jeder Zuweisung um eins erhöht.

Wenn entweder die Datenbankoption READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION auf ON gesetzt ist, werden logische Kopien (Versionen) für alle in der Datenbank erfolgten Datenänderungen aufbewahrt. Jedes Mal, wenn eine Zeile durch eine bestimmte Transaktion geändert wird, speichert die Instanz der SQL Server-Datenbank-Engine eine Version des zuvor durch ein Commit bestätigten Images der Zeile in tempdb. Jede Version wird mit der Transaktionssequenznummer der Transaktion markiert, von der die Änderung vorgenommen wurde. Die Versionen der geänderten Zeilen werden mithilfe einer Linkliste verkettet. Der neueste Zeilenwert wird immer in der aktuellen Datenbank gespeichert und mit den im Versionsspeicher von tempdb gespeicherten Zeilenversionen verkettet.

Hinweis

Beim Ändern großer Objekte (LOBs, Large Objects) wird nur das geänderte Fragment in den Versionsspeicher in tempdb kopiert.

Die Zeilenversionen werden lang genug aufbewahrt, um den Anforderungen von Transaktionen gerecht zu werden, die unter auf der Zeilenversionsverwaltung basierenden Isolationsstufen ausgeführt werden. Die SQL Server-Datenbank-Engine verfolgt die früheste nützliche Transaktionssequenznummer und löscht in regelmäßigen Abständen alle Zeilenversionen, die mit Transaktionssequenznummern versehen sind, die unterhalb der frühesten nützlichen Sequenznummer liegen.

Wenn beide Datenbankoptionen auf OFF gesetzt sind, werden nur die durch Trigger oder MARS-Sitzungen geänderten Zeilen oder die durch ONLINE-Indizierungsvorgänge gelesenen Zeilen in die Versionsverwaltung einbezogen. Diese Zeilenversionen werden jedoch freigegeben, sobald sie nicht mehr benötigt werden. Ein im Hintergrund ausgeführter Thread entfernt in regelmäßigen Abständen alle veralteten Zeilenversionen.

Hinweis

Für Transaktionen von kurzer Dauer kann eine Version einer geänderten Zeile im Pufferpool zwischengespeichert werden, ohne dass sie in die Datenträgerdateien der tempdb-Datenbank geschrieben wird. Wenn nur ein kurzfristiger Bedarf für die versionsverwaltete Zeile besteht, wird sie einfach aus dem Pufferpool gelöscht und verursacht dadurch nicht unbedingt E/A-Aufwand.

Verhalten beim Lesen von Daten

Wenn unter auf der Zeilenversionsverwaltung basierenden Isolationsstufen ausgeführte Transaktionen Daten lesen, fordern sie keine freigegebenen Sperren (S) für die gelesenen Daten an und blockieren deshalb keine Transaktionen, bei denen Daten geändert werden. Außerdem wird der Aufwand für das Sperren von Ressourcen minimiert, weil nur eine reduzierte Anzahl von Sperren angefordert wird. Die Read Committed-Isolation mit Zeilenversionsverwaltung und die Momentaufnahmeisolation wurden entwickelt, um die Lesekonsistenz der versionsbasierten Daten auf Anweisungsebene bzw. auf Transaktionsebene zu gewährleisten.

Alle Abfragen, einschließlich Transaktionen, die in auf der Zeilenversionsverwaltung basierenden Isolationsstufen ausgeführt werden, richten Sperren vom Typ Sch-S (Schemastabilität) während der Kompilierung und der Ausführung ein. Daher werden Abfragen gesperrt, wenn eine gleichzeitige Transaktion eine Schemaänderungssperre (Sch-M) für die Tabelle aufrechterhält. Beispielsweise aktiviert ein DDL-Vorgang (Data Definition Language, Datendefinitionssprache) eine Sch-S-Sperre, bevor die Schemainformationen für die Tabelle geändert werden. Abfragetransaktionen, einschließlich der Transaktionen, die eine auf der Zeilenversionsverwaltung basierende Isolationsstufe verwenden, werden beim Anfordern einer Sperre vom Typ Sch-S blockiert. Umgekehrt blockiert eine Abfrage, die eine Sch-S-Sperre aufrechterhält, eine gleichzeitige Transaktion, die versucht, eine Sch-M-Sperre zu errichten.

Wenn eine Transaktion mithilfe der Momentaufnahmeisolationsstufe gestartet wird, zeichnet die Instanz der SQL Server-Datenbank-Engine alle aktuell aktiven Transaktionen auf. Wenn die Momentaufnahmetransaktion eine Zeile liest, die über eine Versionskette verfügt, verfolgt die SQL Server-Datenbank-Engine diese Kette und ruft die Zeile dort ab, wo sich die Transaktionssequenznummer befindet:

  • Am nächsten zur Sequenznummer der Momentaufnahmetransaktion, die die Zeile liest, jedoch unterhalb dieser Sequenznummer.

  • Nicht in der Liste der beim Start der Momentaufnahmetransaktion aktiven Transaktionen.

Die von einer Momentaufnahmetransaktion ausgeführten Lesevorgänge rufen die letzte Version jeder Zeile ab, für die zum Startzeitpunkt der Momentaufnahmetransaktion ein Commit erfolgt war. Damit wird ein transaktionskonsistente Momentaufnahme der Daten bereitgestellt, wie sie beim Start der Transaktion vorlagen.

Read Committed-Transaktionen mit Zeilenversionsverwaltung funktionieren auf sehr ähnliche Weise. Der Unterschied besteht darin, dass die Read Committed-Transaktion beim Auswählen der Zeilenversionen nicht ihre eigene Transaktionssequenznummer verwendet. Jedes Mal, wenn eine Anweisung gestartet wird, liest die Read Committed-Transaktion die letzte Transaktionssequenznummer, die für diese Instanz der SQL Server-Datenbank-Engine ausgegeben wurde. Das ist die Transaktionssequenznummer, die zum Auswählen der richtigen Zeilenversionen für diese Anweisung verwendet wird. Dadurch können Read Committed-Transaktionen eine Momentaufnahme der Daten sehen, wie sie beim Start jeder Anweisung vorgelegen haben.

Hinweis

Obwohl Read Committed-Transaktionen mit Zeilenversionsverwaltung eine im Hinblick auf Transaktionen konsistente Sicht der Daten auf Anweisungsebene bereitstellen, bleiben die von diesem Transaktionstyp generierten Zeilenversionen bzw. die Zeilenversionen, auf die dieser Transaktionstyp zugreift, bis zum Ende der Transaktion erhalten.

Verhalten beim Ändern von Daten

Das Verhalten von Datenschreibvorgängen weist mit und ohne optimierte Sperrung erhebliche Unterschiede auf.

Ändern von Daten ohne optimierte Sperrung

In einer Read Committed-Transaktion mit Zeilenversionsverwaltung erfolgt das Auswählen der zu aktualisierenden Zeilen durch Verwenden eines Blockierungsscans, bei dem eine Updatesperre (U) für die Daten beim Lesen der Datenwerte eingerichtet wird. Das ist dasselbe Verhalten wie bei Read Committed-Transaktionen ohne Zeilenversionsverwaltung. Wenn die Datenzeile nicht dem Updatekriterium entspricht, wird die Updatesperre für diese Zeile aufgehoben, und die nächste Zeile wird gesperrt und gescannt.

Transaktionen, die mit der Momentaufnahmeisolationsstufe ausgeführt werden, verwenden eine optimistische Vorgehensweise bei der Datenänderung, indem Sperren für Daten aktiviert werden, bevor die Änderung vorgenommen wird, damit Einschränkungen erzwungen werden. Andernfalls werden erst dann Sperren für Daten aktiviert, wenn die Daten geändert werden sollen. Wenn eine Datenzeile dem Updatekriterium entspricht, überprüft die Momentaufnahmetransaktion, dass die Datenzeile nicht durch eine parallele Transaktion geändert wurde, für die nach dem Start der Momentaufnahmetransaktion ein Commit erfolgte. Wenn die Datenzeile außerhalb der Momentaufnahmetransaktion geändert wurde, tritt ein Updatekonflikt auf, und die Momentaufnahmetransaktion wird beendet. Der Updatekonflikt wird von der SQL Server-Datenbank-Engine behandelt, und es gibt keinerlei Möglichkeit, die Erkennung von Updatekonflikten zu deaktivieren.

Hinweis

Updatevorgänge, die mit der Momentaufnahmeisolationsstufe gestartet werden, werden unter der READ COMMITTED-Isolation ausgeführt, wenn die Momentaufnahmetransaktion auf eines der folgenden Elemente zugreift:

Eine Tabelle mit einer FOREIGN KEY-Einschränkung.

Eine Tabelle, auf die in der FOREIGN KEY-Einschränkung einer anderen Tabelle verwiesen wird.

Eine indizierte Sicht, die auf mehrere Tabellen verweist.

Allerdings wird der Updatevorgang selbst unter diesen Bedingungen fortgesetzt, um zu überprüfen, dass die Daten nicht durch eine andere Transaktion geändert wurden. Wenn die Daten durch eine andere Transaktion geändert wurden, erkennt die Momentaufnahmetransaktion einen Updatekonflikt und wird beendet. Updatekonflikte müssen von der Anwendung behandelt und manuell wiederholt werden.

Ändern von Daten mit optimierter Sperrung

Mit aktivierter optimierter Sperrung und aktivierter Datenbankoption READ_COMMITTED_SNAPSHOT (RCSI) und bei Verwendung der standardmäßigen READ COMMITTED-Isolationsstufe werden für Leser keine Sperren eingerichtet, und für Writer werden anstelle von Sperren, die am Ende der Transaktion ablaufen, kurzzeitige Sperren auf niedriger Ebene eingerichtet.

Die Aktivierung von RCSI wird für größtmögliche Effizienz bei der optimierten Sperrung empfohlen. Bei Verwendung strengerer Isolationsstufen wie „Repeatable read“ oder Serializable muss die Datenbank-Engine Zeilen- und Seitensperren sowohl für Leser als auch für Writer bis zum Ende der Transaktion beibehalten, was den Umfang an Blockierungen und den Speicherbedarf für Sperren vergrößert.

Bei aktiviertem RCSI und bei Verwendung der Standardisolationsstufe READ COMMITTED qualifizieren Writer Zeilen über das Prädikat basierend auf der neuesten Commitversion der Zeile, ohne dass U-Sperren eingerichtet werden. Eine Abfrage wartet nur, wenn die Zeile qualifiziert ist und wenn es eine aktive Schreibtransaktion für diese Zeile oder Seite gibt. Das Qualifizieren basierend auf der neuesten Commitversion und das Sperren nur der qualifizierten Zeilen verringert die Blockierung und erhöht die Parallelität.

Wenn Updatekonflikte mit RCSI und in der Standardisolationsstufe READ COMMITTED festgestellt werden, werden sie automatisch behandelt, und es wird erneut versucht, das Update vorzunehmen, ohne dass die Kunden-Workloads dadurch beeinträchtigt werden.

Bei aktivierter optimierter Sperrung und Verwendung der SNAPSHOT-Isolationsstufe ergibt sich das gleiche Verhalten bei Updatekonflikten. Updatekonflikte müssen von der Anwendung behandelt und manuell wiederholt werden.

Hinweis

Weitere Informationen zu Verhaltensänderungen mit der Funktion für die Sperre nach der Qualifizierung (LAQ) für die optimierte Sperrung finden Sie unter Abfragen von Verhaltensänderungen bei optimierter Sperrung und RCSI.

Gesamtverhalten

In der folgenden Tabelle werden die Unterschiede zwischen der Momentaufnahmeisolation und der READ COMMITTED-Isolation mit Zeilenversionsverwaltung zusammengefasst.

Eigenschaft Read Committed-Isolationsstufe mit Zeilenversionsverwaltung Momentaufnahmeisolationsstufe
Die Datenbankoption, die auf ON gesetzt sein muss, um die erforderliche Unterstützung zu aktivieren. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Wie eine Sitzung den speziellen Typ der Zeilenversionsverwaltung anfordert. Verwenden Sie die standardmäßige Read Committed-Isolationsstufe, oder führen Sie die SET TRANSACTION ISOLATION LEVEL-Anweisung aus, um die READ COMMITTED-Isolationsstufe anzugeben. Das kann nach dem Start der Transaktion durchgeführt werden. Erfordert, dass SET TRANSACTION ISOLATION LEVEL zum Angeben der MOMENTAUFNAHMEN-Isolationsstufe vor dem Start der Transaktion ausgeführt wird.
Die von den Anweisungen gelesene Datenversion. Alle Daten, für die vor dem Start jeder Anweisung ein Commit erfolgte. Alle Daten, für die vor dem Start jeder Transaktion ein Commit erfolgte.
Wie Updates behandelt werden. Ohne optimierte Sperrung: Kehrt von den Zeilenversionen zu den tatsächlichen Daten zurück, um die zu aktualisierenden Zeilen auszuwählen, und verwendet Updatesperren für die ausgewählten Datenzeilen. Aktiviert exklusive Sperren für die tatsächlichen Datenzeilen, die geändert werden sollen. Keine Erkennung von Updatekonflikten.

Mit optimierter Sperrung: Zeilen werden basierend auf der letzten Commitversion ausgewählt, ohne dass Sperren eingerichtet werden. Wenn Zeilen für das Update qualifiziert sind, werden exklusive Zeilen- oder Seitensperren eingerichtet. Wenn Updatekonflikte festgestellt werden, werden sie automatisch behandelt und es wird automatisch erneut versucht, das Update vorzunehmen.		 Verwendet die Zeilenversionen zum Auswählen der zu aktualisierenden Zeilen. Versucht, eine exklusive Sperre für die tatsächliche Datenzeile zu aktivieren, die geändert werden soll. Wenn die Daten durch eine andere Transaktion geändert wurden, tritt ein Updatekonflikt auf, und die Momentaufnahmetransaktion wird beendet.
Erkennung von Updatekonflikten Ohne optimierte Sperrung: Keine.

Mit optimierter Sperrung: Wenn Updatekonflikte festgestellt werden, werden sie automatisch behandelt und es wird automatisch erneut versucht, das Update vorzunehmen.		 Integrierte Unterstützung. Kann nicht deaktiviert werden.

Ressourcenverwendung bei der Zeilenversionsverwaltung

Das Framework für die Zeilenversionsverwaltung unterstützt die folgenden in SQL Server: verfügbaren Funktionen:

  • Auslöser
  • Multiple Active Results Sets (MARS)
  • Online-Indizierung

Das Framework für die Zeilenversionsverwaltung unterstützt zudem die folgenden auf der Zeilenversionsverwaltung basierenden Transaktionsisolationsstufen, die standardmäßig nicht aktiviert sind:

  • Wenn für die Datenbankoption READ_COMMITTED_SNAPSHOT der Wert ON festgelegt ist, stellen READ_COMMITTED-Transaktionen bei Verwendung der Zeilenversionsverwaltung eine Lesekonsistenz auf Anweisungsebene bereit.
  • Wenn für die Datenbankoption ALLOW_SNAPSHOT_ISOLATION der Wert ON festgelegt ist, stellen SNAPSHOT-Transaktionen bei Verwendung der Zeilenversionsverwaltung eine Lesekonsistenz auf Transaktionsebene bereit.

Durch die auf der Zeilenversionsverwaltung basierenden Isolationsstufen wird die Anzahl der von der Transaktion abgerufenen Sperren dadurch reduziert, dass keine freigegebenen Sperren für Lesevorgänge verwendet werden. Auf diese Weise wird die Systemleistung erhöht, da die Anzahl der für die Verwaltung der Sperren verwendeten Ressourcen reduziert wird. Die Leistung wird zudem dadurch erhöht, dass die Anzahl von Sperrungen einer Transaktion durch von anderen Transaktionen angeforderte Sperren verringert wird.

Auf der Zeilenversionsverwaltung basierende Isolationsstufen erhöhen die von Datenänderungen benötigten Ressourcen. Bei Aktivierung dieser Optionen werden für alle Datenänderungen für die Datenbank Versionen angegeben. Eine Kopie der Daten in dem Zustand vor der Änderung wird in tempdb gespeichert. Dies ist auch dann der Fall, wenn keine aktiven Transaktionen die auf der Zeilenversionsverwaltung basierende Isolation verwenden. Die Daten nach der Änderung enthalten einen Verweis auf die in tempdb gespeicherten Daten, die über eine Versionsangabe verfügen. Im Fall von großen Objekten wird nur ein Teil des geänderten Objekts in tempdb gespeichert.

In tempdb verwendeter Speicherplatz

tempdb muss für jede Instanz der SQL Server-Datenbank-Engine über genügend Speicherplatz für die Zeilenversionen verfügen, die für sämtliche Datenbanken in der Instanz generiert wurden. Der Datenbankadministrator muss sicherstellen, dass tempdb über genügend Speicherplatz verfügt, um den Versionsspeicher zu unterstützen. In tempdb befinden sich zwei Versionsspeicher:

  • Der Onlineindexerstellungs-Versionsspeicher wird für Onlineindexerstellungen in allen Datenbanken verwendet.
  • Der allgemeine Versionsspeicher wird für alle anderen Datenänderungsvorgänge in sämtlichen Datenbanken verwendet.

Zeilenversionen müssen so lange gespeichert werden, wie eine aktive Transaktion darauf zugreifen muss. Einmal pro Minute entfernt ein Hintergrundthread nicht mehr benötigte Zeilenversionen und gibt so Versionsspeicherplatz in tempdb frei. Eine Transaktion mit langer Ausführungszeit verhindert, dass der Speicherplatz im Versionsspeicher freigegeben werden kann, wenn sie eine der folgenden Bedingungen erfüllt:

  • Sie verwendet die auf der Zeilenversionsverwaltung basierende Isolation.
  • Sie verwendet Trigger, MARS oder Onlineindexerstellungs-Vorgänge.
  • Sie generiert Zeilenversionen.

Hinweis

Wenn innerhalb einer Transaktion ein Trigger aufgerufen wird, werden die vom Trigger generierten Zeilenversionen bis zum Ende der Transaktion beibehalten, auch wenn die Zeilenversionen nach Abschluss des Triggers nicht mehr benötigt werden. Dies gilt auch für Read Committed-Transaktionen, die Zeilenversionsverwaltung verwenden. Bei diesem Transaktionstyp wird nur für die einzelnen Anweisungen in der Transaktion eine im Hinblick auf Transaktionen konsistente Sicht der Datenbank benötigt. Dies bedeutet, dass die für eine Anweisung in der Transaktion erstellten Zeilenversionen nach Abschluss der Anweisung nicht mehr benötigt werden. Die von den einzelnen Anweisungen in der Transaktion erstellten Zeilenversionen werden jedoch bis zum Abschluss der Transaktion beibehalten.

Wenn tempdb nicht mehr über genügend Speicherplatz verfügt, erzwingt die SQL Server-Datenbank-Engine eine Verkleinerung der Versionsspeicher. Während des Verkleinerungsprozesses werden die Transaktionen mit der längsten Ausführungszeit, die noch keine Zeilenversionen generiert haben, als Opfer gekennzeichnet. Die Meldung 3967 wird im Fehlerprotokoll für jede Opfertransaktion generiert. Wenn eine Transaktion als Opfer gekennzeichnet ist, kann sie die Zeilenversionen im Versionsspeicher nicht mehr lesen. Wenn die Transaktion versucht, Zeilenversionen zu lesen, wird die Meldung 3966 generiert, und es wird ein Rollback für die Transaktion ausgeführt. Ist die Verkleinerung des Prozesses erfolgreich, wird Speicherplatz in tempdb verfügbar. Anderenfalls ist in tempdb nicht mehr genügend Speicherplatz vorhanden, und folgender Fehler tritt auf:

  • Schreibvorgänge werden weiterhin ausgeführt, generieren jedoch keine Versionen. Eine Informationsmeldung (3959) wird im Fehlerprotokoll angezeigt. Die Transaktion, die Daten schreibt, ist jedoch nicht betroffen.

  • Transaktionen, die versuchen, auf Zeilenversionen zuzugreifen, die aufgrund eines vollständigen tempdb-Rollbacks nicht generiert wurden, werden beendet, und der Fehler 3958 wird ausgegeben.

In Datenzeilen verwendeter Speicherplatz

Jede Datenbankzeile kann am Ende der Zeile bis zu 14 Byte für Zeilenversionsverwaltungs-Informationen nutzen. Zu den Zeilenversionsverwaltungs-Informationen zählen die Transaktionssequenznummer der Transaktion, die den Commit für die Version ausgeführt hat, sowie der Zeiger auf die Zeile mit Versionsangabe. Diese 14 Byte werden hinzugefügt, wenn die Zeile zum ersten Mal geändert wird oder wenn unter einer der folgenden Bedingungen eine neue Zeile eingefügt wird:

  • Die Option READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION ist auf ON festgelegt.
  • Die Tabelle verfügt über einen Trigger.
  • Multiple Active Results Sets (MARS) wird verwendet.
  • Onlineindexerstellungs-Vorgänge werden derzeit für die Tabelle ausgeführt.

Diese 14 Byte werden aus der Datenbankzeile entfernt, wenn die Zeile zum ersten Mal unter allen der folgenden Bedingungen geändert wird:

  • Die Optionen READ_COMMITTED_SNAPSHOT und ALLOW_SNAPSHOT_ISOLATION sind auf OFF festgelegt.
  • Der Trigger ist nicht mehr für die Tabelle vorhanden.
  • MARS wird nicht verwendet.
  • Es werden derzeit keine Onlineindexerstellungs-Vorgänge ausgeführt.

Der Datenbank sollte so viel Speicherplatz zugeordnet werden, dass sie 14 Bytes pro Datenbankzeile aufnehmen kann, falls eine der Funktionen zur Zeilenversionsverwaltung verwendet wird. Das Hinzufügen von Zeilenversionsverwaltungs-Informationen kann Indexseitenteilungen oder die Zuordnung einer neuen Datenseite zur Folge haben, falls auf der aktuellen Seite nicht genügend Speicherplatz verfügbar ist. Beispiel: Wenn die durchschnittliche Zeilenlänge 100 Bytes beträgt, wächst eine vorhandene Tabelle durch die zusätzlichen 14 Bytes um 14 Prozent.

Durch Verringern des Füllfaktors kann die Fragmentierung der Indexseiten reduziert oder verhindert werden. Zum Anzeigen der Fragmentierungsinformationen für die Daten und Indizes einer Tabelle oder Sicht können Sie sys.dm_db_index_physical_stats verwenden.

In großen Objekten verwendeter Speicherplatz

Die SQL Server-Datenbank-Engine unterstützt sechs Datentypen, die lange Zeichenfolgen von bis zu 2 Gigabyte (GB) Länge aufnehmen können: nvarchar(max), varchar(max), varbinary(max), ntext, text und image. Lange Zeichenfolgen, die mithilfe dieser Datentypen gespeichert werden, werden in einer Reihe von Datenfragmenten gespeichert, die mit der Datenzeile verknüpft sind. Zeilenversionsverwaltungs-Informationen werden in sämtlichen Fragmenten gespeichert, die zum Speichern dieser langen Zeichenfolgen verwendet werden. Datenfragmente stellen eine Sammlung von Seiten dar, die für große Objekte in einer Tabelle dediziert sind.

Wenn einer Datenbank neue große Werte hinzugefügt werden, werden diese mithilfe von maximal 8.040 Byte an Daten pro Fragment zugeordnet. In früheren Versionen der SQL Server-Datenbank-Engine wurden bis zu 8.080 Byte an ntext-, text- oder image-Daten pro Fragment gespeichert.

Vorhandene ntext-, text- und image-Daten großer Objekte (LOB, Large Objects) werden nicht aktualisiert, um Speicherplatz für die Zeilenversionsverwaltungs-Informationen freizugeben, wenn ein Upgrade einer Datenbank von einer früheren Version von SQL Server auf SQL Server durchgeführt wird. Wenn die LOB-Daten jedoch zum ersten Mal geändert werden, wird mit ihnen ein dynamisches Upgrade durchgeführt, um das Speichern von Versionsinformationen zu ermöglichen. Dies ist auch dann der Fall, wenn keine Zeilenversionen generiert werden. Nachdem ein Upgrade mit den LOB-Daten durchgeführt wurde, wird die maximale Byteanzahl, die pro Fragment gespeichert wird von 8.080 auf 8.040 reduziert. Der Upgradeprozess ist dem Löschen des LOB-Werts und dem erneuten Einsetzen desselben Werts gleichwertig. Ein Upgrade der LOB-Daten wird auch dann durchgeführt, wenn nur 1 Byte geändert wird. Es handelt sich hierbei um einen einmaligen Vorgang für jede ntext-, text-, oder image-Spalte. Durch jeden Vorgang wird jedoch je nach dem Umfang der LOB-Daten eine hohe Menge an Seitenzuordnungen und E/A-Aktivitäten generiert. Es können zudem viele Protokollierungsaktivitäten generiert werden, sofern die Änderung vollständig protokolliert wird. WRITETEXT- und UPDATETEXT-Vorgänge werden minimal protokolliert, wenn das Datenbankwiederherstellungsmodell nicht auf den Wert FULL festgelegt ist.

Die nvarchar(max)-, varchar(max)- und varbinary(max)-Datentypen sind in früheren Versionen von SQL Server nicht verfügbar. Aus diesem Grund weisen sie keine Upgradeprobleme auf.

Es sollte genügend Speicherplatz zugeordnet werden, um dieser Anforderung gerecht zu werden.

Überwachen der Zeilenversionsverwaltung und des Versionsspeichers

Für die Überwachung von Zeilenversionsverwaltungs-, Versionsspeicher- und Momentaufnahmeisolationsprozessen in Bezug auf die Leistung und Probleme stellt SQL Server Tools in Form von dynamische Verwaltungssichten (DMVs, Dynamic Management Views) und Leistungsindikatoren im Systemmonitor von Windows zur Verfügung.

DMVs

Die folgenden DMVs stellen Informationen zu den aktuellen Systemstatus von tempdb und den Versionsspeicher sowie die Transaktionen bereit, die die Zeilenversionsverwaltung verwenden.

  • sys.dm_db_file_space_usage. Gibt Informationen zur Speicherverwendung aller Dateien in der Datenbank zurück. Weitere Informationen finden Sie unter sys.dm_db_file_space_usage (Transact-SQL).

  • sys.dm_db_session_space_usage. Gibt Aktivität für die Seitenzuordnung und die Zuordnungsaufhebung nach Sitzung für die Datenbank zurück. Weitere Informationen finden Sie unter sys.dm_db_session_space_usage (Transact-SQL).

  • sys.dm_db_task_space_usage. Gibt für die Datenbank Aktivitäten zu Seitenzuordnungen und aufgehobenen Seitenzuordnungen nach Tasks zurück. Weitere Informationen finden Sie unter sys.dm_db_task_space_usage (Transact-SQL).

  • sys.dm_tran_top_version_generators. Gibt eine virtuelle Tabelle für die Objekte zurück, die die meisten Versionen im Versionsspeicher erzeugen. Hierbei werden die ersten 256 aggregierten Datensatzlängen nach database_id und rowset_id gruppiert. Mithilfe dieser Funktion können Sie die größten Consumer des Versionsspeichers finden. Weitere Informationen finden Sie unter sys.dm_tran_top_version_generators (Transact-SQL).

  • sys.dm_tran_version_store. Gibt eine virtuelle Tabelle zurück, die alle Versionsdatensätze im allgemeinen Versionsspeicher anzeigt. Weitere Informationen finden Sie unter sys.dm_tran_version_store (Transact-SQL).

  • sys.dm_tran_version_store_space_usage. Gibt eine virtuelle Tabelle zurück, die den gesamten in tempdb verwendeten Speicherplatz der Versionsspeicherdatensätze für jede Datenbank anzeigt. Weitere Informationen finden Sie unter sys.dm_tran_version_store_space_usage (Transact-SQL).

    Hinweis

    Die Systemobjekte sys.dm_tran_top_version_generators und sys.dm_tran_version_store sind potenziell sehr kostspielige in der Ausführung, da beide den gesamten Versionsspeicher abfragen, der sehr groß sein könnte. sys.dm_tran_version_store_space_usage ist dagegen effizient und nicht kostspielig in der Ausführung, da es nicht durch die einzelnen Versionsspeicherdatensätze navigiert, sondern stattdessen den aggregierten in tempdb belegten Versionsspeicherplatz pro Datenbank zurückgibt.

  • sys.dm_tran_active_snapshot_database_transactions. Gibt eine virtuelle Tabelle für alle aktiven Transaktionen in sämtlichen Datenbanken in der SQL Server-Instanz zurück, die die Zeilenversionsverwaltung verwenden. Systemtransaktionen werden in dieser DMV nicht angezeigt. Weitere Informationen finden Sie unter sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

  • sys.dm_tran_transactions_snapshot. Gibt eine virtuelle Tabelle zurück, die Momentaufnahmen anzeigt, die von den einzelnen Transaktionen erstellt wurden. Die Momentaufnahme enthält die Sequenznummer der aktiven Transaktionen, die die Zeilenversionsverwaltung verwenden. Weitere Informationen finden Sie unter sys.dm_tran_transactions_snapshot (Transact-SQL).

  • sys.dm_tran_current_transaction. Gibt eine einzelne Zeile zurück, die auf die Zeilenversionsverwaltung bezogene Statusinformationen der Transaktion in der aktuellen Sitzung anzeigt. Weitere Informationen finden Sie unter sys.dm_tran_current_transaction (Transact-SQL).

  • sys.dm_tran_current_snapshot. Gibt eine virtuelle Tabelle zurück, die alle aktiven Transaktionen zum Zeitpunkt des Startens der aktuellen Momentaufnahmeisolation aufführt. Wenn die aktuelle Transaktion die Momentaufnahmeisolation verwendet, gibt diese Funktion keine Zeilen zurück. Der DMV sys.dm_tran_current_snapshot ist ähnlich wie sys.dm_tran_transactions_snapshot, gibt aber nur die aktiven Transaktionen für die aktuelle Momentaufnahme zurück. Weitere Informationen finden Sie unter sys.dm_tran_current_snapshot (Transact-SQL).

Leistungsindikatoren

SQL Server-Leistungsindikatoren stellen Informationen zur Auswirkung von SQL Server-Prozessen auf die Systemleistung zur Verfügung. Die folgenden Leistungsindikatoren überwachen tempdb und den Versionsspeicher sowie Transaktionen mithilfe der Zeilenversionsverwaltung. Die Leistungsindikatoren sind im SQLServer:Transaktionen-Leistungsobjekt enthalten.

  • Freier Speicherplatz in tempdb (KB): Überwacht die Menge des freien Speicherplatzes in Kilobyte (KB), der in der tempdb-Datenbank zur Verfügung steht. Es muss genügend freier Speicherplatz in tempdb zur Verfügung stehen, um den Versionsspeicher zu bearbeiten, der die Momentaufnahmeisolation unterstützt.

    Die folgende Formel ermöglicht eine grobe Schätzung der Größe des Versionsspeichers. Bei lange andauernden Transaktionen kann es sich als sinnvoll erweisen, die Generierungs- und Cleanuprate zu überwachen, um die maximale Größe des Versionsspeichers einzuschätzen.

    [size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

    Die längste Ausführungszeit von Transaktionen sollte Onlineindexerstellungs-Vorgänge nicht einschließen. Da diese Vorgänge bei sehr großen Tabellen viel Zeit in Anspruch nehmen können, verwenden Onlineindexerstellungs-Vorgänge einen separaten Versionsspeicher. Die ungefähre Größe des Onlineindexerstellungs-Versionsspeichers entspricht der Menge der in der Tabelle geänderten Daten, einschließlich aller Indizes, während die Onlineindexerstellung aktiviert ist.

  • Versionsspeichergröße (KB): Überwacht die Größe in KB aller Versionsspeicher. Mithilfe dieser Informationen können Sie die Menge des Speicherplatzes bestimmen, die in der tempdb-Datenbank für den Versionsspeicher benötigt wird. Das Überwachen dieser Indikatoren über einen gewissen Zeitraum ermöglicht eine hilfreiche Schätzung des zusätzlich für tempdb benötigten Speicherplatzes.

  • Versionsgenerierungsrate (KB/s). Überwacht die Versionsgenerierungsrate, in KB pro Sekunde, in allen Versionsspeichern.

  • Versionscleanuprate (KB/s). Überwacht die Versionscleanuprate, in KB pro Sekunde, in allen Versionsspeichern.

    Hinweis

    Die Informationen aus Versionsgenerierungsrate (KB/s) und Versionscleanuprate (KB/s) können zur Vorhersage von Speicherplatzanforderungen für tempdb verwendet werden.

  • Anzahl der Versionsspeichereinheiten: Überwacht die Anzahl der Versionsspeichereinheiten.

  • Erstellen von Versionsspeichereinheiten: Überwacht die Gesamtzahl der Versionsspeichereinheiten, die für das Speichern von Zeilenversionen erstellt wurden, seitdem die Instanz gestartet wurde.

  • Abschneiden von Versionsspeichereinheiten: Überwacht die Gesamtzahl der Versionsspeichereinheiten, die abgeschnitten wurden, seitdem die Instanz gestartet wurde. Eine Versionsspeichereinheit wird abgeschnitten, wenn SQL Server bestimmt, dass keine der Versionszeilen, die in der Versionsspeichereinheit gespeichert sind, für die Ausführung aktiver Transaktionen benötigt wird.

  • Updatekonfliktquote: Überwacht die Quote von Update-Momentaufnahmetransaktionen, die Updatekonflikte aufweisen, im Verhältnis zur Gesamtzahl der Update-Momentaufnahmetransaktionen.

  • Längste Transaktionsausführungszeit: Überwacht die längste Ausführungszeit in Sekunden aller Transaktionen, die die Zeilenversionsverwaltung verwenden. Hiermit kann bestimmt werden, ob eine Transaktion über eine nicht vertretbare Zeitdauer ausgeführt wird.

  • Transaktionen: Überwacht die Gesamtzahl aktiver Transaktionen. Dieser Leistungsindikator schließt keine Systemtransaktionen ein.

  • Momentaufnahmetransaktionen. Überwacht die Gesamtzahl aktiver Momentaufnahmetransaktionen.

  • Update-Momentaufnahmetransaktionen. Überwacht die Gesamtzahl aktiver Momentaufnahmetransaktionen, die Updatevorgänge ausführen.

  • NonSnapshot-Versionstransaktionen. Überwacht die Gesamtzahl aktiver Nichtmomentaufnahme-Transaktionen, die Versionsdatensätze generieren.

    Hinweis

    Die Summe von Update-Momentaufnahmetransaktionen und NonSnapshot-Versionstransaktionen stellt die Gesamtzahl der Transaktionen dar, die an der Versionsgenerierung teilnehmen. Die Differenz zwischen Momentaufnahmetransaktionen und Update-Momentaufnahmetransaktionen gibt die Anzahl der schreibgeschützten Momentaufnahmetransaktionen an.

Beispiel für eine auf der Zeilenversionsverwaltung basierende Isolationsstufe

Die folgenden Beispiele zeigen die Unterschiede im Verhalten zwischen Momentaufnahmeisolationstransaktionen und Transaktionen, bei denen ein Commit vor dem Lesevorgang ausgeführt werden muss, und die die Zeilenversionsverwaltung verwenden.

A. Arbeiten mit der Momentaufnahme-Isolation

In diesem Beispiel liest eine Transaktion, die unter Momentaufnahmeisolation ausgeführt wird, Daten, die anschließend von einer anderen Transaktion geändert werden. Die Momentaufnahmetransaktion blockiert nicht den Updatevorgang, der von der anderen Transaktion ausgeführt wird, liest auch weiterhin Daten aus der versionsspezifischen Zeile und ignoriert die Datenänderung. Wenn die Momentaufnahmetransaktion jedoch versucht, die Daten zu ändern, die bereits von der anderen Transaktion geändert wurden, generiert die Momentaufnahmetransaktion einen Fehler und wird beendet.

Für Sitzung 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under snapshot isolation shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 1:

    -- Reissue the SELECT statement - this shows
    -- the employee having 48 vacation hours. The
    -- snapshot transaction is still reading data from
    -- the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Für Sitzung 1:

    -- Reissue the SELECT statement - this still
    -- shows the employee having 48 vacation hours
    -- even after the other transaction has committed
    -- the data modification.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- Because the data has been modified outside of the
    -- snapshot transaction, any further data changes to
    -- that data by the snapshot transaction will cause
    -- the snapshot transaction to fail. This statement
    -- will generate a 3960 error and the transaction will
    -- terminate.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION
GO

B. Arbeiten mit Read-committed unter Verwendung der Zeilenversionsverwaltung

In diesem Beispiel wird eine Transaktion, bei der ein Commit vor dem Lesevorgang ausgeführt werden muss und die Zeilenversionsverwaltung verwendet wird, gleichzeitig mit einer anderen Transaktion ausgeführt. Die Transaktion, bei der ein Commit vor dem Lesevorgang ausgeführt werden muss, verhält sich anders als eine Momentaufnahmetransaktion. Ebenso wie eine Momentaufnahmetransaktion liest die Transaktion, bei der ein Commit vor dem Lesevorgang ausgeführt werden muss, versionsspezifische Zeilen, nachdem die andere Transaktion Daten geändert hat. Im Gegensatz zu einer Momentaufnahmetransaktion gilt für die Transaktion, bei der ein Commit vor dem Lesevorgang ausgeführt werden muss, jedoch Folgendes:

  • Sie liest die geänderten Daten, nachdem die andere Transaktion ein Commit der Datenänderungen vorgenommen hat.
  • Sie ist in der Lage, die von der anderen Transaktion bearbeiteten Daten zu aktualisieren, was der Momentaufnahmetransaktion nicht möglich ist.

Für Sitzung 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under read-committed using row versioning shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 1:

    -- Reissue the SELECT statement - this still shows
    -- the employee having 48 vacation hours. The
    -- read-committed transaction is still reading data
    -- from the versioned row and the other transaction
    -- has not committed the data changes yet.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Für Sitzung 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Für Sitzung 1:

    -- Reissue the SELECT statement which now shows the
    -- employee having 40 vacation hours. Being
    -- read-committed, this transaction is reading the
    -- committed data. This is different from snapshot
    -- isolation which reads from the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- This statement, which caused the snapshot transaction
    -- to fail, will succeed with read-committed using row versioning.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Aktivieren von auf Zeilenversionsverwaltung basierenden Isolationsstufen

Datenbankadministratoren steuern die Einstellungen für die Zeilenversionsverwaltung auf Datenbankebene über die Datenbankoptionen READ_COMMITTED_SNAPSHOT und ALLOW_SNAPSHOT_ISOLATION in der ALTER DATABASE-Anweisung.

Wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON festgelegt ist, werden die zur Unterstützung der Option verwendeten Mechanismen unmittelbar aktiviert. Wenn die READ_COMMITTED_SNAPSHOT-Option festgelegt wird, wird in der Datenbank nur die Verbindung zugelassen, die den ALTER DATABASE-Befehl ausführt. So lange ALTER DATABASE nicht abgeschlossen ist, darf keine andere offene Verbindung in der Datenbank bestehen. Die Datenbank muss sich nicht im Einzelbenutzermodus befinden.

Die folgende Transact-SQL-Anweisung aktiviert READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

Wenn für die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption der Wert ON festgelegt ist, generiert die Instanz der SQL Server-Datenbank-Engine so lange keine Zeilenversionen für geänderte Daten, bis alle aktiven Transaktionen abgeschlossen sind, durch die Daten in der Datenbank geändert werden. Wenn aktive Änderungstransaktionen vorhanden sind, legt SQL Server den Status der Option auf PENDING_ON fest. Wenn alle Änderungstransaktionen abgeschlossen sind, wird der Status der Option zu ON geändert. Die Benutzer können keine Momentaufnahmetransaktion in dieser Datenbank starten, bis die Option vollständig ON ist. Die Datenbank übergibt einen PENDING_OFF-Status, wenn der Datenbankadministrator die ALLOW_SNAPSHOT_ISOLATION-Option auf OFF festlegt.

Die folgende Transact-SQL-Anweisung aktiviert ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

In der folgenden Tabelle werden die Statusmöglichkeiten der ALLOW_SNAPSHOT_ISOLATION-Option aufgeführt und beschrieben. Der Zugriff von Benutzern auf Daten in der Datenbank wird durch das Verwenden von ALTER DATABASE mit der ALLOW_SNAPSHOT_ISOLATION-Option nicht blockiert.

Status der Momentaufnahmeisolationsumgebung der aktuellen Datenbank Beschreibung
OFF Die Unterstützung von Momentaufnahme-Isolationstransaktionen ist nicht aktiviert. Momentaufnahmeisolationtransaktionen sind nicht zulässig.
PENDING_ON Die Unterstützung von Momentaufnahmeisolationstransaktionen befindet sich in einem Übergangsstatus (von OFF nach ON). Offene Transaktionen müssen abgeschlossen werden.

Momentaufnahmeisolationtransaktionen sind nicht zulässig.
ON Die Unterstützung von Momentaufnahmeisolationstransaktionen ist aktiviert.

Momentaufnahmeisolationtransaktionen sind zulässig.
PENDING_OFF Die Unterstützung von Momentaufnahmeisolationstransaktionen befindet sich in einem Übergangsstatus (von ON nach OFF).

Momentaufnahmetransaktionen, die nach diesem Zeitpunkt gestartet werden, können nicht auf die Datenbank zugreifen. Updatetransaktionen sind ist in dieser Datenbank noch durch die Versionsverwaltung eingeschränkt. Vorhandene Momentaufnahmetransaktionen können immer noch problemlos auf die Datenbank zugreifen. Der PENDING_OFF-Status wird erst OFF, wenn alle Momentaufnahmetransaktionen abgeschlossen sind, die zu dem Zeitpunkt, als der Momentaufnahmeisolationsstatus der Datenbank ON war, aktiviert waren.

Verwenden Sie die sys.databases-Katalogsicht, um den Status der beiden Datenbankoptionen zur Zeilenversionsverwaltung zu bestimmen.

Alle Updates von Benutzertabellen sowie bestimmte Updates von Systemtabellen, die in master und msdb gespeichert sind, generieren Zeilenversionen.

Die ALLOW_SNAPSHOT_ISOLATION-Option wird in den Datenbanken master und msdb automatisch auf ON festgelegt und kann nicht deaktiviert werden.

Benutzer können die READ_COMMITTED_SNAPSHOT-Option in master, tempdb oder msdb nicht auf ON festlegen.

Verwenden von auf Zeilenversionsverwaltung basierenden Isolationsstufen

Das Framework für die Zeilenversionsverwaltung ist in SQL Server immer aktiviert und wird von mehreren Funktionen verwendet. Es stellt nicht nur auf Zeilenversionsverwaltung basierende Isolationsstufen bereit, sondern wird auch zur Unterstützung von Änderungen verwendet, die an Triggern und MARS-Sitzungen (Multiple Active Result Sets) vorgenommen werden; außerdem dient es zur Unterstützung von Datenlesevorgängen für ONLINE-Indexvorgänge.

Auf der Zeilenversionsverwaltung basierende Isolationsstufen werden auf der Datenbankebene aktiviert. Alle Anwendungen, die auf Objekte aus aktivierten Datenbanken zugreifen, können mithilfe der folgenden Isolationsstufen Abfragen ausführen:

  • READ COMMITTED (Commit vor dem Lesevorgang) mit Zeilenversionsverwaltung, indem die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON (wie im folgenden Codebeispiel gezeigt) festgelegt wird:

    ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

    Wenn die Datenbank für READ_COMMITTED_SNAPSHOT aktiviert ist, verwenden alle Abfragen, die unter der Isolationsstufe READ COMMITTED ausgeführt werden, Zeilenversionsverwaltung. Dies bedeutet, dass die Lesevorgänge die Updatevorgänge nicht blockieren.

  • Die Momentaufnahmeisolation durch Festlegen der Datenbankoption ALLOW_SNAPSHOT_ISOLATION auf ON, wie im folgenden Codebeispiel gezeigt:

    ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

    Eine Transaktion, die unter Momentaufnahmeisolation ausgeführt wird, kann auf Tabellen in der Datenbank zugreifen, die für die Snapshotfunktion aktiviert wurden. Wenn auf Tabellen zugegriffen werden soll, die nicht für Momentaufnahmen aktiviert wurden, muss die Isolationsstufe geändert werden. Das folgende Codebeispiel zeigt z. B. eine SELECT-Anweisung, die während der Ausführung unter einer Momentaufnahmetransaktion zwei Tabellen verknüpft. Eine der Tabellen gehört zu einer Datenbank, in der Momentaufnahmeisolation nicht aktiviert ist. Wenn die SELECT-Anweisung unter Momentaufnahmeisolation ausgeführt wird, ist die Ausführung nicht erfolgreich.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

    Das folgende Codebeispiel zeigt die gleiche SELECT-Anweisung, die so bearbeitet wurde, dass die Transaktionsisolationsstufe in READ COMMITTED geändert wurde. Durch diese Änderung wird die SELECT-Anweisung erfolgreich ausgeführt.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        WITH (READCOMMITTED)
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

Einschränkungen von Transaktionen, die auf Zeilenversionsverwaltung basierende Isolationsstufen verwenden

Berücksichtigen Sie die folgenden Einschränkungen, wenn Sie mit auf Zeilenversionsverwaltung basierenden Isolationsstufen arbeiten:

  • READ_COMMITTED_SNAPSHOT kann in tempdb, msdb, oder master nicht aktiviert werden.

  • Globale temporäre Tabellen werden in tempdb gespeichert. Wenn auf globale temporäre Tabellen in einer Momentaufnahmetransaktion zugegriffen wird, muss einer der folgenden Vorgänge erfolgen:

    • Legen Sie die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption in tempdb auf ON fest.
    • Verwenden eines Isolationshinweises zum Ändern der Isolationsstufe für die Anweisung.

  • Momentaufnahmetransaktionen erzeugen einen Fehler, wenn Folgendes zutrifft:

    • Eine Datenbank erhält nach dem Start der Momentaufnahmetransaktion, jedoch vor dem Zugriff auf die Datenbank durch die Momentaufnahmetransaktion einen Schreibschutz.
    • Beim Zugriff auf Objekte aus mehreren Datenbanken wurde ein Datenbankstatus so geändert, dass die Datenbankwiederherstellung nach dem Start einer Momentaufnahmetransaktion aufgetreten ist, jedoch vor dem Zugriff auf die Datenbank durch die Momentaufnahmetransaktion. Beispiel: Die Datenbank wurde auf OFFLINE und dann auf ONLINE festgelegt, auf automatisches Schließen und Öffnen oder auf Trennen und Anfügen.

  • Verteilte Transaktionen, z. B. Abfragen in verteilten partitionierten Datenbanken, werden unter Momentaufnahmeisolation nicht unterstützt.

  • SQL Server speichert nicht mehrere Versionen von Systemmetadaten. DDL-Anweisungen (Data Definition Language) für Tabellen und andere Datenbankobjekte (Indizes, Sichten, Datentypen, gespeicherte Prozeduren und CLR-Funktionen) verändern Metadaten. Wenn eine DDL-Anweisung ein Objekt ändert, bewirkt jeder gleichzeitige Verweis auf das Objekt unter Momentaufnahmeisolation, dass die Momentaufnahmetransaktion einen Fehler erzeugt. Für READ COMMITTED-Transaktionen gilt diese Einschränkung nicht, wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON festgelegt wurde.

    Ein Datenbankadministrator führt z. B. die folgende ALTER INDEX-Anweisung aus.

    USE AdventureWorks2022;
GO
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO

    Für alle Momentaufnahmetransaktionen, die während der Ausführung der ALTER INDEX-Anweisung aktiviert sind, wird ein Fehler ausgegeben, wenn versucht wird, auf die HumanResources.Employee-Tabelle zu verweisen, nachdem die ALTER INDEX-Anweisung ausgeführt wurde. READ COMMITTED-Transaktionen, die Zeilenversionsverwaltung verwenden, sind nicht betroffen.

    Hinweis

    BULK INSERT-Operationen können Änderungen an den Metadaten der Zieltabelle verursachen (z. B. beim Deaktivieren von Einschränkungsprüfungen). Sollte dies der Fall sein, schlagen gleichzeitige Momentaufnahmeisolationstransaktion fehl, die auf Tabellen mit BULK INSERT zugreifen.

Anpassen von Sperren und Zeilenversionsverwaltung

Anpassen des Timeouts für Sperren

Wenn eine Instanz der Microsoft SQL Server-Datenbank-Engine einer Transaktion keine Sperre für eine Ressource erteilen kann, da eine andere Transaktion bereits eine widersprüchliche Sperre für diese Ressource besitzt, wird die erste Transaktion blockiert, während sie darauf wartet, dass die vorhandene Sperre aufgehoben wird. Standardmäßig gibt es keinen obligatorischen Timeoutzeitraum und keine Möglichkeit, im Voraus zu testen, ob eine Ressource gesperrt ist, außer zu versuchen, auf die Daten zuzugreifen (und eventuell auf unbestimmte Zeit blockiert zu werden).

Hinweis

Verwenden Sie in SQL Server die dynamische Verwaltungssicht sys.dm_os_waiting_tasks, um zu bestimmen, ob und wodurch ein Prozess blockiert wird. Verwenden Sie die gespeicherte Systemprozedur sp_who in früheren Versionen von SQL Server. Weitere Informationen und Beispiele finden Sie unter Verstehen und Beheben von Problemen durch Blockierungen in SQL Server.

Mithilfe der LOCK_TIMEOUT-Einstellung kann eine Anwendung eine Zeitspanne festlegen, die angibt, wie lange eine Anweisung maximal auf eine blockierte Ressource wartet. Wenn eine Anweisung länger wartet als in der LOCK_TIMEOUT-Einstellung angegeben, wird die blockierte Anweisung automatisch abgebrochen und die Fehlermeldung 1222 (Lock request time-out period exceeded) an die Anwendung zurückgegeben. Für eine Transaktion, in der diese Anweisung enthalten ist, wird jedoch von SQL Server kein Rollback ausgeführt, und sie wird nicht abgebrochen. Die Anwendung muss daher über einen Fehlerhandler verfügen, der die Fehlermeldung 1222 identifizieren kann. Ist dies nicht der Fall, kann die Anwendung fortfahren, ohne zu erkennen, dass eine einzelne Anweisung in einer Transaktion abgebrochen wurde, und Fehler können auftreten, da nachfolgende Anweisungen in der Transaktion möglicherweise von der nicht ausgeführten Anweisung abhängen.

Durch das Implementieren eines Fehlerhandlers, der die Fehlermeldung 1222 auffängt, kann eine Anwendung die Timeoutbedingung bearbeiten und Abhilfemaßnahmen ergreifen, wie etwa die vormals blockierte Anforderung automatisch erneut zu senden oder für die gesamte Transaktion einen Rollback auszuführen.

Führen Sie die @@LOCK_TIMEOUT-Funktion aus, um die aktuelle LOCK_TIMEOUT-Einstellung zu bestimmen:

SELECT @@lock_timeout;
GO

Anpassen der Transaktionsisolationsstufe

Die Standardisolationsstufe für die Microsoft SQL Server-Datenbank-Engine ist READ COMMITTED. Wenn für eine bestimmte Anwendung eine andere Isolationsstufe erforderlich ist, kann eine der folgenden Methoden verwendet werden, um die entsprechende Isolationsstufe anzugeben:

  • Ausführen der SET TRANSACTION ISOLATION LEVEL-Anweisung.
  • ADO.NET-Anwendungen, die den verwalteten Namespace System.Data.SqlClient verwenden, können eine IsolationLevel-Option mithilfe der SqlConnection.BeginTransaction-Methode angeben.
  • Anwendungen, die ADO verwenden, können die Autocommit Isolation Levels-Eigenschaft festlegen.
  • Beim Starten einer Transaktion können Anwendungen, für die OLE DB verwendet wird, ITransactionLocal::StartTransaction aufrufen, wobei isoLevel auf die gewünschte Transaktionsisolationsstufe festgelegt ist. Beim Angeben der Isolationsstufe im Autocommitmodus können Anwendungen, von denen OLE DB verwendet wird, die DBPROPSET_SESSION-Eigenschaft DBPROP_SESS_AUTOCOMMITISOLEVELS auf die gewünschte Transaktionsisolationsstufe festlegen.
  • Anwendungen, die ODBC verwenden, können das SQL_COPT_SS_TXN_ISOLATION-Attribut mithilfe von SQLSetConnectAttr festlegen.

Wenn eine Isolationsstufe angegeben ist, richtet sich das Sperrverhalten aller Abfragen und DML-Anweisungen (Data Manipulation Language, Datenbearbeitungssprache) der SQL Server-Sitzung nach dieser Isolationsstufe. Die Isolationsstufe bleibt gültig, bis die Sitzung beendet wird oder bis eine andere Isolationsstufe festgelegt wird.

Im folgenden Beispiel wird die SERIALIZABLE-Isolationsstufe festgelegt:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

Die Isolationsstufe kann, falls notwendig, für einzelne Abfrage- oder DML-Anweisungen überschrieben werden, indem ein Hinweis auf Tabellenebene angegeben wird. Das Angeben eines Hinweises auf Tabellenebene wirkt sich nicht auf andere Anweisungen in der Sitzung aus. Es wird empfohlen, dass Hinweise auf Tabellenebene zum Ändern des Standardverhaltens nur dann verwendet werden, wenn dies absolut notwendig ist.

Die SQL Server-Datenbank-Engine muss möglicherweise beim Lesen von Metadaten selbst dann Sperren einrichten, wenn die Isolationsstufe auf eine Stufe festgelegt ist, für die zum Lesen von Daten keine freigegebenen Sperren erforderlich sind. Eine in der READ UNCOMMITTED-Isolationsstufe ausgeführte Transaktion richtet beim Lesen von Daten beispielsweise keine freigegebenen Sperren ein, kann jedoch zu einem gewissen Zeitpunkt Sperren anfordern, wenn eine Systemkatalogsicht gelesen wird. Dies bedeutet, dass eine READ UNCOMMITTED-Transaktion beim Abfragen einer Tabelle Blockierungen verursachen kann, wenn eine andere Transaktion gleichzeitig die Metadaten der Tabelle ändert.

Wenn Sie die derzeit für die Transaktion festgelegte Isolationsstufe ermitteln möchten, verwenden Sie die DBCC USEROPTIONS-Anweisung, wie im nachfolgenden Beispiel gezeigt. Das hier aufgeführte Resultset weicht möglicherweise von dem auf Ihrem System angezeigten Resultset ab.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Hier ist das Resultset.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sperrhinweise

Sperrhinweise können für einzelne Tabellenverweise in den SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen angegeben werden. Diese Hinweise geben den Typ der Sperre oder die Zeilenversionsverwaltung an, die die Instanz der SQL Server-Datenbank-Engine für die Tabellendaten verwendet. Sperrhinweise auf Tabellenebene können verwendet werden, wenn eine präzisere Steuerung der Sperrentypen für ein Objekt notwendig wird. Diese Sperrhinweise überschreiben die aktuelle Transaktionsisolationsstufe für diese Sitzung.

Hinweis

Die Verwendung von Sperrhinweisen ist nicht zu empfehlen, wenn die optimierte Sperrung aktiviert ist. Tabellen- und Abfragehinweise werden zwar geschätzt, verringern aber den Vorteil der optimierten Sperrung. Weitere Informationen finden Sie unter Vermeiden von Sperrhinweisen bei optimierter Sperrung.

Weitere Informationen zu bestimmten Sperrhinweisen und ihrem Verhalten finden Sie unter Tabellenhinweise (Transact-SQL).

Hinweis

Die SQL Server-Datenbank-Engine wählt so gut wie immer die richtige Sperrebene aus. Es wird empfohlen, dass Sperrhinweise auf Tabellenebene zur Änderung des Standardsperrverhaltens nur dann verwendet werden, wenn dies notwendig ist. Wenn eine Sperrstufe nicht zugelassen wird, kann dies negative Auswirkungen auf die Parallelität haben.

Die SQL Server-Datenbank-Engine muss möglicherweise beim Lesen von Metadaten selbst dann Sperren aktivieren, wenn eine SELECT-Anweisung mit einem Sperrhinweis verarbeitet wird, der beim Lesen von Daten Anforderungen für freigegebene Sperren verhindert. Eine SELECT-Anweisung, die den NOLOCK-Hinweis verwendet, aktiviert beim Lesen von Daten z.B. keine freigegebenen Sperren, kann jedoch manchmal Sperren anfordern, wenn eine Systemkatalogsicht gelesen wird. Dies bedeutet, dass es möglich ist, eine SELECT-Anweisung zu blockieren, die NOLOCK verwendet.

Wenn – wie im folgenden Beispiel gezeigt – die Isolationsstufe für Transaktionen als SERIALIZABLE festgelegt wurde und der NOLOCK-Sperrhinweis auf Tabellenebene mit der SELECT-Anweisung verwendet wird, werden keine Schlüsselbereichssperren eingerichtet, die in der Regel zur Aufrechterhaltung der Serialisierbarkeit von Transaktionen verwendet werden.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT
        resource_type,
        resource_subtype,
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

Die einzige Sperre, die eingerichtet wird und auf HumanResources.Employee verweist, ist eine Sperre des Typs Sch-S (Schemastabilität). In diesem Fall kann die Serialisierbarkeit nicht mehr garantiert werden.

In SQL Server kann die LOCK_ESCALATION-Option von ALTER TABLE Tabellensperren als unerwünscht festlegen und HoBT-Sperren für partitionierte Tabellen aktivieren. Diese Option ist kein Sperrhinweis, kann jedoch verwendet werden, um die Sperrenausweitung zu reduzieren. Weitere Informationen finden Sie unter ALTER TABLE (Transact-SQL).

Anpassen der Sperren für einen Index

Die SQL Server-Datenbank-Engine 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 Standardeinstellungen der Sperrebenen, in denen die Seiten- und Zeilensperre aktiviert ist, nicht zu überschreiben, es sei denn, die Zugriffsmuster für Tabellen oder Indizes sind bekannt und konsistent und es liegt ein Ressourcenkonflikt vor, der behoben werden muss. Das Überschreiben einer Sperrebene kann den gleichzeitigen Zugriff auf eine Tabelle oder einen Index signifikant einschränken. Wenn beispielsweise in einer großen Tabelle, auf die viele Benutzer zugreifen, nur Sperren auf Tabellenebene angegeben werden, kann dies zu Engpässen führen, da die Benutzer die Aufhebung der Sperre auf Tabellenebene abwarten müssen, bevor sie auf die Tabelle zugreifen können.

Wenn die Zugriffsmuster gut bekannt und konsistent sind, kann das Untersagen von Seiten- oder Zeilensperren in einigen Fällen sinnvoll sein. So verwendet beispielsweise eine Datenbankanwendung eine Nachschlagetabelle, die wöchentlich in einem Batchverarbeitungsprozess aktualisiert wird. Gleichzeitige Leser greifen mit einer freigegebenen Sperre (S) auf die Tabelle zu. Das wöchentliche Batchupdate greift mit einer exklusiven Sperre (X) auf die Tabelle zu. Durch das Deaktivieren der Seiten- und Zeilensperrung für die Tabelle wird der Sperraufwand unter der Woche reduziert, indem Leser mithilfe freigegebener Tabellensperren gleichzeitig auf die Tabelle zugreifen können. Wenn der Batchauftrag ausgeführt wird, kann er das Update effizient ausführen, da er eine exklusive Tabellensperre erhält.

Die Deaktivierung der Seiten- und Zeilensperre kann, muss jedoch nicht akzeptiert werden, da das wöchentliche Batchupdate die gleichzeitigen Leser während des Updates daran hindert, auf die Tabelle zuzugreifen. Wenn durch den Batchauftrag nur einige Zeilen oder Seiten geändert werden, können Sie die Sperrebene ändern, sodass Sperren auf Zeilen- oder Seitenebene zugelassen werden. Dadurch können andere Sitzungen aus der Tabelle lesen, ohne diese zu sperren. Wenn der Batchauftrag sehr viele Updates enthält, ist es möglicherweise die beste Methode, eine exklusive Sperre für die Tabelle zu setzen, um sicherzustellen, dass der Auftrag effizient ausgeführt wird.

Gelegentlich kann es zu einem Deadlock kommen, wenn zwei gleichzeitig ausgeführte Vorgänge Sperren für die gleiche Tabelle abrufen und dann blockieren, da beide die Seite sperren müssen. Wenn keine Zeilensperren zugelassen werden, wird erzwungen, dass einer der Vorgänge wartet, um den Deadlock zu vermeiden. Weitere Informationen zu Deadlocks finden Sie im Leitfaden zu Deadlocks.

Die Granularität der Sperren für einen Index kann mithilfe der Anweisungen CREATE INDEX und ALTER INDEX festgelegt werden. Die Einstellungen für die Sperre werden sowohl auf die Indexseiten als auch auf die Tabellenseiten angewendet. Darüber hinaus können die Anweisungen CREATE TABLE und ALTER TABLE dazu verwendet werden, die Granularität der Sperren für 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. Verwenden Sie zum Anzeigen der aktuellen Sperroption für einen bestimmten Index 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

Weiterführende Themen zu Transaktionen

Geschachtelte Transaktionen

Explizite Transaktionen können geschachtelt werden. Auf diese Weise sollen in erster Linie Transaktionen in gespeicherten Prozeduren unterstützt werden, die sowohl von einem Prozess, der sich bereits in einer Transaktion befindet, als auch von Prozessen, die keine aktiven Transaktionen aufweisen, aufgerufen werden können.

Im folgenden Beispiel wird dargestellt, wie geschachtelte Transaktionen verwendet werden sollten. Die TransProc-Prozedur erzwingt eine Transaktion, unabhängig vom Transaktionsmodus des Prozesses, der die Prozedur ausführt. Wird TransProc aufgerufen, wenn eine Transaktion aktiv ist, wird die geschachtelte Transaktion in TransProc überwiegend ignoriert, und für ihre INSERT-Anweisungen wird ein Commit oder Rollback ausgeführt, je nachdem, welche endgültige Aktion für die äußere Transaktion eingerichtet wurde. Wenn TransProc von einem Prozess ausgeführt wird, der keine ausstehende Transaktion aufweist, führt COMMIT TRANSACTION am Ende der Prozedur letztendlich einen Commit für die INSERT-Anweisungen aus.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

Die SQL Server-Datenbank-Engine ignoriert das Ausführen von Commits für innere Transaktionen. Für die Transaktion wird entweder ein Commit oder Rollback ausgeführt, je nachdem, welche Aktion am Ende der äußersten Transaktion eingerichtet wird. Bei der Ausführung eines Commits für die äußere Transaktion wird für die inneren geschachtelten Transaktionen ebenfalls ein Commit ausgeführt. Bei der Ausführung eines Rollbacks für die äußere Transaktion wird auch für alle inneren Transaktionen ein Rollback ausgeführt, unabhängig davon, ob für jede einzelne der inneren Transaktionen ein Commit ausgeführt wurde oder nicht.

Jeder Aufruf von COMMIT TRANSACTION oder COMMIT WORK gilt für die zuletzt ausgeführte BEGIN TRANSACTION. Wenn die BEGIN TRANSACTION-Anweisungen geschachtelt sind, bezieht sich eine COMMIT-Anweisung nur auf die letzte geschachtelte Transaktion, also die innerste Transaktion. Selbst wenn sich eine COMMIT TRANSACTION transaction_name-Anweisung in einer geschachtelten Transaktion auf den Transaktionsnamen der äußeren Transaktion bezieht, wird der Commit ausschließlich für die innerste Transaktion ausgeführt.

Es ist nicht zulässig, dass der transaction_name-Parameter einer ROLLBACK TRANSACTION-Anweisung auf die inneren Transaktionen einer Reihe von benannten geschachtelten Transaktionen verweist. transaction_name kann nur auf den Transaktionsnamen der äußersten Transaktion verweisen. Wenn eine ROLLBACK TRANSACTION-transaction_name-Anweisung, die den Namen der äußeren Transaktion verwendet, auf einer beliebigen Ebene einer Reihe geschachtelter Transaktionen ausgeführt wird, wird für alle geschachtelten Transaktionen ein Rollback ausgeführt. Wenn eine ROLLBACK WORK- oder ROLLBACK TRANSACTION-Anweisung ohne Angabe des transaction_name-Parameters auf einer beliebigen Ebene einer Reihe von geschachtelten Transaktionen ausgeführt wird, wird für alle geschachtelten Transaktionen, einschließlich der äußersten Transaktion, ein Rollback ausgeführt.

Die @@TRANCOUNT-Funktion zeichnet die aktuelle Schachtelungsebene der Transaktion auf. Jede BEGIN TRANSACTION-Anweisung erhöht @@TRANCOUNT um den Wert 1. Jede COMMIT TRANSACTION- oder COMMIT WORK-Anweisung verringert @@TRANCOUNT um den Wert 1. Bei einer ROLLBACK WORK- oder ROLLBACK TRANSACTION-Anweisung ohne Transaktionsnamen wird für alle geschachtelten Transaktionen ein Rollback ausgeführt und @@TRANCOUNT auf 0 reduziert. Bei einer ROLLBACK TRANSACTION-Anweisung, die den Transaktionsnamen der äußersten Transaktion in einer Reihe geschachtelter Transaktionen verwendet, wird ein Rollback für alle geschachtelten Transaktionen ausgeführt und @@TRANCOUNT auf 0 reduziert. Wenn Sie nicht sicher sind, ob eine Transaktion bereits begonnen hat, können Sie mit SELECT @@TRANCOUNT ermitteln, ob der Wert 1 oder höher beträgt. Wenn @@TRANCOUNT gleich 0 ist, hat noch keine Transaktion begonnen.

Verwenden von gebundenen Sitzungen

Gebundene Sitzungen vereinfachen die Koordination zwischen zahlreichen Aktionen, die auf demselben Server ausgeführt werden. Sie ermöglichen, dass mehrere Sitzungen gemeinsam dieselben Transaktionen und Sperren nutzen und ohne Sperrkonflikte mit denselben Daten arbeiten können. Gebundene Sitzungen können aus mehreren Sitzungen in derselben Anwendung oder aus mehreren Anwendungen mit getrennten Sitzungen erstellt werden.

Soll eine Sitzung an einer gebundenen Sitzung beteiligt werden, muss sie sp_getbindtoken oder srv_getbindtoken (über Open Data Services) aufrufen, um ein Bindungstoken abzurufen. Ein Bindungstoken ist eine Zeichenfolge, die jede gebundene Transaktion eindeutig kennzeichnet. Das Bindungstoken wird dann an die anderen Sitzungen gesendet, die an die aktuelle Sitzung gebunden werden sollen. Die anderen Sitzungen werden durch Aufrufen von sp_bindsession mithilfe des Bindungstokens, das sie von der ersten Sitzung empfangen haben, an die Transaktion gebunden.

Hinweis

Eine Sitzung muss über eine aktive Benutzertransaktion verfügen, damit sp_getbindtoken oder srv_getbindtoken erfolgreich ausgeführt werden kann.

Bindungstoken müssen durch den Anwendungscode, der die erste Sitzung herstellt, an den Anwendungscode gesendet werden, der eine nachfolgende Sitzung an die erste Sitzung bindet. Es gibt keine Transact-SQL-Anweisung oder API-Funktion, die eine Anwendung verwenden kann, um das Bindungstoken für eine Transaktion abzurufen, die von einem anderen Prozess gestartet wurde. Nachfolgend werden verschiedene Methoden zum Übertragen von Bindungstokens aufgeführt:

  • Wenn alle Sitzungen vom selben Anwendungsprozess initiiert werden, können die Bindungstoken im globalen Speicher gespeichert oder als Parameter an Funktionen übergeben werden.

  • Wenn die Sitzungen jedoch von separaten Anwendungsprozessen initiiert werden, können die Bindungstoken mithilfe der prozessübergreifenden Kommunikation (IPC, Interprocess Communication), wie z. B. von Remoteprozeduraufrufen (RPCs, Remote Procedure Calls) oder DDE (Dynamic Data Exchange), übertragen werden.

  • Bindungstoken können in einer Instanz der SQL Server-Datenbank-Engine in einer Tabelle gespeichert werden, die von die Bindung zur ersten Sitzung herstellenden Prozessen gelesen werden kann.

Es kann jeweils nur eine Sitzung in einer Menge von gebundenen Sitzungen aktiv sein. Wenn eine Sitzung eine Anweisung auf der Instanz ausführt oder auf Ergebnisse von der Instanz wartet, können keine anderen gebundenen Sitzungen auf die Instanz zugreifen, bis die aktuelle Sitzung die aktuelle Anweisung vollständig verarbeitet hat oder abbricht. Ist die Instanz mit der Verarbeitung einer Anweisung einer anderen gebundenen Sitzung ausgelastet, zeigt eine Fehlermeldung an, dass der Transaktionsbereich verwendet wird und der Zugriffsversuch der Sitzung später wiederholt werden kann.

Beim Binden von Sitzungen behält jede Sitzung ihre eigene Isolationsstufeneinstellung bei. Das Verwenden von SET TRANSACTION ISOLATION LEVEL zum Ändern der Isolationsstufeneinstellung einer Sitzung wirkt sich nicht auf die Einstellung anderer gebundener Sitzungen aus.

Typen von gebundenen Sitzungen

Gebundene Sitzungen lassen sich in lokale und verteilte gebundene Sitzungen unterteilen.

  • Lokale gebundene Sitzung Ermöglicht, dass gebundene Sitzungen den Transaktionsbereich einer einzelnen Instanz der SQL Server-Datenbank-Engine gemeinsam nutzen.

  • Verteilte gebundene Sitzung Ermöglicht, dass gebundene Sitzungen dieselbe Transaktion auf zwei oder mehreren Instanzen gemeinsam nutzen, bis für die gesamte Transaktion mithilfe von Microsoft Distributed Transaction Coordinator (MS DTC) ein Commit oder Rollback ausgeführt wird.

Verteilte gebundene Sitzungen werden nicht durch ein Bindungstoken in Form einer Zeichenfolge gekennzeichnet, sondern durch numerische IDs für verteilte Transaktionen. Wenn eine gebundene Sitzung an einer lokalen Transaktion beteiligt ist und mit SET REMOTE_PROC_TRANSACTIONS ON einen Remoteprozeduraufruf (RPC) auf einem Remoteserver ausführt, wird die lokale gebundene Transaktion automatisch von MS DTC zu einer verteilten gebundenen Transaktion heraufgestuft und eine MS DTC-Sitzung gestartet.

Sinnvoller Einsatz von gebundenen Sitzungen

In früheren Versionen von SQL Server wurden gebundene Sitzungen hauptsächlich zum Entwickeln erweiterter gespeicherter Prozeduren verwendet, die Transact-SQL-Anweisungen für den Prozess ausführen mussten, der sie aufgerufen hat. Wenn der aufrufende Prozess ein Bindungstoken als Parameter an die erweiterte gespeicherte Prozedur übergibt, ermöglicht dies der Prozedur, den Transaktionsbereich des aufrufenden Prozesses mitzunutzen. Dadurch wird die erweiterte gespeicherte Prozedur in den aufrufenden Prozess integriert.

In der SQL Server-Datenbank-Engine sind mithilfe von CLR geschriebene gespeicherte Prozeduren sicherer, skalierbarer und stabiler als erweiterte gespeicherte Prozeduren. CLR-gespeicherte Prozeduren verwenden nicht sp_bindsession, sondern das SqlContext-Objekt, um sich dem Kontext der aufrufenden Sitzung anzuschließen.

Gebundene Sitzungen können zum Entwickeln von dreistufigen Anwendungen verwendet werden. Geschäftsabläufe werden hierbei in getrennte Programme integriert, die gemeinsam für eine einzelne Geschäftstransaktion zuständig sind. Diese Programme müssen hinsichtlich der Koordination ihres Zugriffs auf die Datenbank sehr sorgfältig codiert werden. Da die beiden Sitzungen die Sperren gemeinsam nutzen, dürfen die beiden Programme dieselben Daten nicht gleichzeitig ändern. Zu einem gegebenen Zeitpunkt darf jeweils nur eine Sitzung als Teil der Transaktion Änderungen vornehmen – ein paralleles Ausführen von Vorgängen ist ausgeschlossen. Die Transaktion kann nur an bestimmten, gut definierten Zwischenergebnispunkten zwischen Sitzungen wechseln, z. B. wenn alle DML-Anweisungen abgeschlossen und deren Ergebnisse abgerufen wurden.

Codieren effizienter Transaktionen

Transaktionen sollten so kurz wie möglich gehalten werden. Wenn eine Transaktion gestartet wird, muss ein Datenbank-Managementsystem (Database Management System, DBMS) viele Ressourcen bis zum Ende der Transaktion bereitstellen, um die ACID-Eigenschaften der Transaktion zu schützen. Wenn Daten verändert werden, müssen die zu ändernden Zeilen durch exklusive Sperren geschützt werden, die verhindern, dass andere Transaktionen die Zeilen lesen. Diese exklusiven Sperren müssen so lange aufrechterhalten werden, bis für die Transaktion ein Commit oder Rollback ausgeführt wird. Abhängig von den Einstellungen der Isolationsstufen von Transaktionen können SELECT-Anweisungen Sperren einrichten, die bis zum Ausführen eines Commits oder Rollbacks für die Transaktion aufrechterhalten werden müssen. Vor allem bei Systemen mit zahlreichen Benutzern müssen Transaktionen so kurz wie möglich gehalten werden, um die Wahrscheinlichkeit zu reduzieren, dass bei gleichzeitigen Verbindungen Sperrkonflikte für Ressourcen auftreten. Lang andauernde, ineffiziente Transaktionen sind bei wenigen Benutzern möglicherweise nicht problematisch, in einem System mit Tausenden von Benutzern jedoch inakzeptabel. Ab SQL Server 2014 (12.x) unterstützt SQL Server verzögerte dauerhafte Transaktionen. Verzögerte dauerhafte Transaktionen gewährleisten keine Dauerhaftigkeit. Weitere Informationen finden Sie im Thema Steuern der Transaktionsdauerhaftigkeit.

Codierungsrichtlinien

Im Folgenden sind Richtlinien für das Codieren von effizienten Transaktionen aufgeführt:

  • Verzichten Sie auf Benutzereingaben während einer Transaktion. Sorgen Sie dafür, dass alle notwendigen Eingaben von den Benutzern vor Beginn der Transaktion vorgenommen werden. Wenn zusätzliche Benutzereingaben während einer Transaktion notwendig sind, führen Sie für die aktuelle Transaktion einen Rollback aus, und starten Sie die Transaktion neu, nachdem die Benutzereingaben erfolgt sind. Selbst wenn Benutzer sofort reagieren, ist die menschliche Reaktionszeit bedeutend langsamer als die Geschwindigkeit von Computern. Alle Ressourcen, die von der Transaktion beansprucht werden, sind für besonders lange Zeit belegt, was zu Blockierungsproblemen führen kann. Wenn Benutzer nicht reagieren, bleibt die Transaktion aktiv und sperrt so lange wichtige Ressourcen, bis der Benutzer reagiert. Dies kann Minuten, sogar Stunden dauern.

  • Öffnen Sie nach Möglichkeit keine Transaktion während des Durchsuchens von Daten. Transaktionen sollten erst dann gestartet werden, wenn alle vorhergehenden Datenanalysen abgeschlossen sind.

  • Achten Sie darauf, dass eine Transaktion so kurz wie möglich ist. Wenn Sie wissen, welche Änderungen vorgenommen werden müssen, starten Sie eine Transaktion, führen Sie die Änderungsanweisungen aus, und führen Sie unmittelbar im Anschluss einen Commit oder Rollback aus. Öffnen Sie die Transaktion erst, wenn es erforderlich ist.

  • Sie sollten für schreibgeschützte Abfragen gegebenenfalls eine auf Zeilenversionsverwaltung basierende Isolationsstufe verwenden, um die Möglichkeit von Blockierungen zu reduzieren.

  • Setzen Sie die niedrigeren Isolationsstufen von Transaktionen sinnvoll ein. Viele Anwendungen können ganz leicht so codiert werden, dass die Isolationsstufe, bei der ein Commit vor dem Lesevorgang ausgeführt sein muss, für die Transaktion verwendet wird. Nicht alle Transaktionen erfordern die Isolationsstufe SERIALIZABLE.

  • Setzen Sie die niedrigen Optionen der Cursorparallelität, wie etwa die vollständige Parallelität, sinnvoll ein. Wenn es in einem System relativ unwahrscheinlich ist, dass Updates gleichzeitig vorgenommen werden, kann der Aufwand, der gelegentlich für die Fehlerbehandlung entsteht, wenn Daten nach einem Lesevorgang von einem anderen Benutzer geändert werden, bedeutend geringer sein als der Aufwand, der durch das konsequente Sperren von Zeilen bei jedem Lesen entsteht.

  • Während einer Transaktion sollte auf so wenige Daten wie möglich zugegriffen werden. Dadurch wird die Anzahl der gesperrten Zeilen gesenkt und Konflikte zwischen Transaktionen vermieden.

  • Vermeiden Sie wenn möglich einschränkende Sperrhinweise wie HOLDLOCK. Hinweise wie HOLDLOCK oder eine SERIALIZABLE-Isolationsstufe können dazu führen, dass Prozesse auch bei gemeinsamen Sperren warten und die Parallelität dadurch einschränken.

  • Vermeiden Sie die Verwendung impliziter Transaktionen. Implizite Transaktionen können aufgrund ihrer Merkmale unvorhersehbares Verhalten einführen. Weitere Informationen finden Sie unter Implizite Transaktionen und Vermeiden von Parallelitäts- und Ressourcenproblemen.

  • Entwerfen Sie Indizes mit reduziertem Füllfaktor. Ein reduzierter Füllfaktor kann Sie dabei unterstützen, die Fragmentierung von Indexseiten zu vermeiden oder zu senken. Damit können auch Suchzeiten für Indizes reduziert werden, insbesondere wenn diese von einem Datenträger abgerufen werden. Zum Anzeigen der Fragmentierungsinformationen für die Daten und Indizes einer Tabelle oder Sicht können Sie sys.dm_db_index_physical_stats verwenden.

Implizite Transaktionen und Vermeiden von Parallelitäts- und Ressourcenproblemen

Wenn Sie Parallelitäts- und Ressourcenprobleme vermeiden möchten, sollten implizite Transaktionen sorgfältig verwaltet werden. Bei impliziten Transaktionen wird durch die nächste Transact-SQL-Anweisung nach COMMIT oder ROLLBACK automatisch eine neue Transaktion gestartet. Dadurch kann eine neue Transaktion geöffnet werden, während die Anwendung Daten durchsucht oder sogar wenn Eingaben des Benutzers erforderlich sind. Nach Abschluss der letzten Transaktion, die zum Schutz von Datenänderungen erforderlich ist, sollten Sie die impliziten Transaktionen deaktivieren, bis erneut eine Transaktion benötigt wird, um Datenänderungen zu schützen. Auf diese Weise kann die SQL Server-Datenbank-Engine den Autocommitmodus verwenden, während die Anwendung Daten durchsucht und Benutzereingaben vorgenommen werden.

Wenn die Momentaufnahme-Isolationsstufe aktiviert ist, obwohl eine neue Transaktion keine Sperren beibehält, verhindert außerdem eine Transaktion mit langer Ausführungszeit, dass die alten Versionen aus tempdb entfernt werden.

Verwalten lang andauernder Transaktionen

Eine Transaktion mit langer Ausführungszeit ist eine aktive Transaktion, für die kein Commit bzw. Rollback rechtzeitig ausgeführt wurde. Bei Transaktionen, deren Beginn und Ende vom Benutzer gesteuert werden, kann es vorkommen, dass der Benutzer eine Transaktion startet und dann seinen Arbeitsplatz verlässt, während die Transaktion auf eine Reaktion des Benutzers wartet. Dies ist z. B. eine typische Ursache für eine lang andauernde Transaktion.

Eine Transaktion mit langer Ausführungszeit kann für eine Datenbank schwerwiegende Probleme nach sich ziehen:

  • Wenn eine Serverinstanz heruntergefahren wird, nachdem die aktive Transaktion zahlreiche Änderungen vorgenommen hat, für die kein Commit ausgeführt wurde, kann die Wiederherstellungsphase beim nachfolgenden Neustart erheblich länger dauern als durch die Serverkonfigurationsoption Wiederherstellungsintervall bzw. durch die ALTER DATABASE ... SET TARGET_RECOVERY_TIME-Option angegeben. Durch diese Option wird die Frequenz aktiver bzw. indirekter Prüfpunkte gesteuert. Weitere Informationen zu Typen von Prüfpunkten finden Sie unter Datenbankprüfpunkte (SQL Server).

  • Obwohl durch eine wartende Transaktion möglicherweise nur sehr wenige Protokolldaten generiert werden, wird die Protokollkürzung auf unbestimmte Zeit aufgehalten. Dies führt dazu, dass das Transaktionsprotokoll anwächst und möglicherweise irgendwann voll ist. Wenn das Transaktionsprotokoll voll ist, kann die Datenbank keine weiteren Updates mehr ausführen. Weitere Informationen finden Sie im Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server, unter Problembehandlung bei einem vollständigen Transaktionsprotokoll (SQL Server-Fehler 9002) sowie unter Das Transaktionsprotokoll.

Wichtig

In Azure SQL-Datenbank werden Leerlauftransaktionen (Transaktionen, die für sechs Stunden nichts in das Transaktionsprotokoll geschrieben haben) automatisch beendet, um Ressourcen freizugeben.

Ermitteln von Transaktionen mit langer Ausführungszeit

Verwenden Sie eine der folgenden Optionen, um nach lang andauernden Transaktionen zu suchen:

  • sys.dm_tran_database_transactions

    Diese dynamische Verwaltungssicht gibt Informationen zu Transaktionen auf Datenbankebene zurück. Bei einer Transaktion mit langer Ausführungszeit gehören der Zeitpunkt des ersten Protokolldatensatzes (database_transaction_begin_time), der aktuelle Status der Transaktion (database_transaction_state) und die Protokollfolgenummer (Log Sequence Number, LSN) des ersten Datensatzes im Transaktionsprotokoll (database_transaction_begin_lsn) zu den Spalten von besonderem Interesse.

    Weitere Informationen finden Sie unter sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRAN

    Mithilfe dieser Anweisung können Sie die Benutzer-ID des Transaktionsbesitzers identifizieren. Auf diese Weise können Sie die Quelle der Transaktion ermitteln und die Transaktion ordnungsgemäß beenden (durch ein Commit anstelle eines Rollbacks). Weitere Informationen finden Sie unter DBCC OPENTRAN (Transact-SQL).

Beenden einer Transaktion

Unter Umständen müssen Sie die KILL-Anweisung ausführen. Verwenden Sie diese Anweisung jedoch sehr vorsichtig, besonders wenn gerade kritische Prozesse ausgeführt werden. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Deadlocks

Deadlocks sind ein komplexes Thema im Zusammenhang mit der Sperrung, unterscheiden sich jedoch von der Blockierung.

Zugehöriger Inhalt