Handbuch zu Transaktionssperren und Zeilenversionsverwaltung

Gilt für:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

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. Diese Anleitung beschreibt Sperr- und Zeilenversionsverwaltungsmechanismen, die die Datenbank-Engine zur Sicherstellung der Integrität jeder Transaktion verwendet, und bietet Informationen darüber, wie Anwendungen Transaktionen effizient steuern können.

Note

Optimiertes Sperren ist eine Datenbank-Engine-Funktion, die 2023 eingeführt wurde und den Arbeitsspeicher für Sperren drastisch reduziert sowie die Zahl der für gleichzeitiges Schreiben erforderlichen Sperren verringert. Dieser Artikel wurde aktualisiert, um das Verhalten der Datenbank-Engine mit und ohne optimierte Sperrung zu beschreiben.

• Weitere Informationen und Informationen dazu, wo optimierte Sperrung verfügbar sind, finden Sie unter Optimierte Sperrung.
• Informationen zum Bestimmen, ob die optimierte Sperrung in Ihrer Datenbank aktiviert ist, finden Sie unter Ist die optimierte Sperrung aktiviert?

Aufgrund der optimierten Sperrung wurden an einigen Abschnitten dieses Artikels bedeutende Änderungen vorgenommen:

• Sperren in der Datenbank-Engine
• Löschvorgang
• Einfügevorgang
• Eskalation sperren
• Reduzieren von Sperren und Sperrenausweitung
• Verhalten beim Ändern von Daten
• Gesamtverhalten
• Locking-Hinweise

Transaktionsgrundlagen

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.

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

Consistency
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.

Note

Die Dokumentation verwendet den Begriff B-Baum im Allgemeinen in Bezug auf Indizes. In Zeilenstoreindizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. 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.

Durability
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.

Anwendungen sind dafür verantwortlich, Transaktionen an Points zu starten und zu beenden, die die logische Konsistenz der Daten erzwingen. Die Anwendung muss die Sequenz von Datenänderungen definieren, die die Daten in einem konsistenten Zustand in Bezug auf die Geschäftsregeln der Organisation belassen. Die Anwendung führt diese Änderungen in einer einzigen Transaktion aus, damit die Datenbank-Engine die Integrität der Transaktion erzwingen kann.

Es ist die Zuständigkeit eines Unternehmensdatenbanksystems, wie einer Instanz der Datenbank-Engine, Mechanismen bereitzustellen, die die Integrität jeder Transaktion gewährleisten. Die Datenbank-Engine bietet:

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

• Protokollierungseinrichtungen zur Sicherstellung der Dauerhaftigkeit von Transaktionen. Bei vollständig dauerhaften Transaktionen wird der Protokolldatensatz vor dem Transaktionscommit auf den Datenträger geschrieben. Somit wird, selbst wenn die Server-Hardware, das Betriebssystem oder die Instanz der Datenbank-Engine selbst einen Fehler aufweist, die Instanz beim Neustart die Transaktionsprotokolle nutzen, um automatisch alle unvollständigen Transaktionen bis zum Punkt des Systemfehlers zurückzusetzen. 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. Nachdem eine Transaktion gestartet wurde, muss sie erfolgreich abgeschlossen (Commit ausgeführt) werden, oder die Datenbank-Engine macht alle Datenänderungen rückgängig, die durch die Transaktion seit ihrem Start vorgenommen 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.

Transaktionen kontrollieren

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. Für weitere Informationen siehe Transaktionen, Durchfü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 ist und die bereichsbezogene Transaktion nicht commit ausgeführt oder zurückgesetzt wird, wird sie automatisch von der Datenbank-Engine zurückgesetzt. Weitere Informationen finden Sie unter Verwenden von Multiple Active Result Sets (MARS).

Transaktionen starten

Mit API-Funktionen und Transact-SQL-Anweisungen können Sie Transaktionen als explizite, Autocommit- oder implizite Transaktionen starten.

Explizite Transaktionen

Eine explizite Transaktion ist eine, bei der Sie sowohl den Beginn als auch das Ende der Transaktion explizit durch eine API-Funktion oder durch das Ausführen der Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION oder ROLLBACK WORK Transact-SQL-Anweisungen definieren. Wenn die Transaktion endet, kehrt die Verbindung zu dem Transaktionsmodus zurück, in dem sie sich befand, bevor die explizite Transaktion gestartet wurde, was der implizite oder Autocommitmodus sein könnte.

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_dboptionum Datenbank-Optionen oder eine Systemprozedur festzulegen, die die master-Datenbank innerhalb expliziter oder impliziter Transaktionen ändert.

Note

UPDATE STATISTICS kann innerhalb einer expliziten Transaktion verwendet werden Allerdings führt UPDATE STATISTICS den Commit unabhängig von der umgebenden Transaktion aus und kann nicht zurückgesetzt werden.

Autocommit-Transaktionen

Der Autocommit-Modus ist der Standardtransaktionsverwaltungsmodus der 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 zu einer Instanz der Datenbank-Engine arbeitet im Autocommit-Modus, wann immer dieser Standardmodus nicht durch explizite oder implizite Transaktionen überschrieben wurde. Der Autocommit-Modus ist auch der Standardmodus für SqlClient, ADO, OLE DB und ODBC.

Implizite Transaktionen

Wenn sich eine Verbindung im impliziten Transaktionsmodus befindet, startet die Datenbank-Engine-Instanz 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. Aktivieren Sie den impliziten Transaktionsmodus entweder über eine API-Funktion oder die Transact-SQL SET IMPLICIT_TRANSACTIONS ON-Anweisung. 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 Instanz der Datenbank-Engine automatisch eine Transaktion, wenn eine dieser Anweisungen zum ersten Mal ausgeführt wird:

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Batchbezogene Transaktionen

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. Eine batch-übergreifende Transaktion, die beim Abschluss eines Batches nicht bestätigt oder zurückgesetzt wird, wird vom Datenbankmodul automatisch zurückgesetzt.

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 Datenbank-Engine kann als Ressourcenmanager in verteilten Transaktionen fungieren, die von Transaktionsmanagern wie dem Microsoft Distributed Transaction Coordinator (MS DTC) oder anderen Transaktionsmanagern koordiniert werden, die die Open Group XA-Spezifikation für die verteilte Transaktionsverarbeitung unterstützen. Weitere Informationen finden Sie in der MS DTC-Dokumentation.

Eine Transaktion innerhalb einer einzelnen Instanz der Datenbank-Engine, die sich über zwei oder mehr Datenbanken erstreckt, ist 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.

In der Anwendung wird eine verteilte Transaktion ähnlich 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 verläuft (die Vorbereitungsphase und die Commitphase), bekannt als Zweiphasencommit.

• 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 Ressourcenmanager führt dann alle erforderlichen Schritte aus, um die Transaktion dauerhaft zu machen, und alle Transaktionsprotokollpuffer für die Transaktion werden auf die Festplatte geschrieben. 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. Verzögerte dauerhafte Transaktionen werden festgeschrieben, bevor die Transaktionsprotokollpuffer auf jedem Ressourcenmanager auf die Festplatte geschrieben werden. Weitere Informationen zur Dauerhaftigkeit verzögerter Transaktionen finden Sie im Artikel Steuern der Transaktionsdauerhaftigkeit.

• Commit-Phase

  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.

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

Transaktionen beenden

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. Eine COMMIT-Anweisung garantiert, dass alle Änderungen der Transaktion dauerhaft Teil der Datenbank werden. Ein Commit gibt auch Ressourcen frei, wie zum Beispiel Sperren, die von der Transaktion verwendet werden.

• Zurücksetzen

  Wenn ein Fehler in einer Transaktion auftritt oder wenn der Benutzer sich entscheidet, die Transaktion abzubrechen, setzen Sie die Transaktion zurück. Eine ROLLBACK-Anweisung macht alle Änderungen, die in der Transaktion vorgenommen wurden, rückgängig, indem sie die Daten in den Zustand zurückversetzt, in dem sie sich zu Beginn der Transaktion befanden. Das Zurücksetzen gibt auch die von der Transaktion gehaltenen Ressourcen frei.

Note

In Sitzungen mit mehreren aktiven Ergebnismengen (MARS) kann eine über eine API-Funktion gestartete explizite Transaktion nicht abgeschlossen werden, solange noch ausstehende Ausführungsanforderungen vorliegen. Jeder Versuch, diesen Transaktionstypen durchzuführen, während derzeit Anforderungen ausgeführt werden, führt zu einem Fehler.

Fehler während der Transaktionsverarbeitung

Wenn ein Fehler das erfolgreiche Abschließen einer Transaktion verhindert, setzt die Datenbank-Engine die Transaktion automatisch zurück und gibt alle von der Transaktion gehaltenen Ressourcen frei. Wenn die Netzwerkverbindung des Clients zu einer Instanz der Datenbank-Engine unterbrochen wird, werden alle ausstehenden Transaktionen für die Verbindung zurückgesetzt, wenn das Netzwerk die Instanz über die Unterbrechung der Verbindung benachrichtigt. Wenn die Clientanwendung ausfällt oder der Clientcomputer abstürzt oder neu gestartet wird, wird die Verbindung ebenfalls unterbrochen, und die Instanz der Datenbank-Engine setzt alle ausstehenden Transaktionen zurück, wenn das Netzwerk sie über die Verbindungsunterbrechung informiert. Wenn der Client die Verbindung zur Datenbank-Engine trennt, werden alle ausstehenden Transaktionen zurückgesetzt.

Wenn ein Laufzeitfehler (wie ein Verstoß gegen eine Einschränkung) in einem Batch auftritt, besteht das Standardverhalten in der Datenbank-Engine darin, nur die Anweisung zurückzusetzen, die den Fehler verursacht hat. Sie können dieses Verhalten mithilfe der SET XACT_ABORT ON-Anweisung ändern. Nach der Ausführung von SET XACT_ABORT ON führt ein Laufzeit-Fehler bei einer Anweisung zu einem automatischen Rollback der aktuellen Transaktion. Kompilierungsfehler, wie z.B. Syntaxfehler, sind von SET XACT_ABORT nicht betroffen. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).

Wenn Fehler auftreten, sollte die entsprechende Aktion (COMMIT oder ROLLBACK) im Anwendungscode enthalten sein. 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 Autocommit-Modus scheint es manchmal so, als ob eine Instanz der Datenbank-Engine einen gesamten Batch zurückgesetzt hat, anstatt nur eine einzelne SQL-Anweisung. Dies passiert, wenn es sich beim aufgetretenen Fehler um einen Kompilierungsfehler und nicht um einen Laufzeitfehler handelt. Ein Kompilierungsfehler verhindert, dass die Datenbank-Engine einen Ausführungsplan erstellt, daher kann nichts im Batch ausgeführt werden. 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 Datenbank-Engine verwendet eine verzögerte Namensauflösung, bei der Objektnamen zur Ausführungszeit und nicht zur Kompilierungszeit 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 Datenbank-Engine verwendet die folgenden Mechanismen, um die Integrität von Transaktionen sicherzustellen und die Konsistenz von Datenbanken zu gewährleisten, wenn mehrere Benutzer gleichzeitig auf Daten zugreifen:

• Locking

  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 verwendet wird, verwaltet die Datenbank-Engine Versionen jeder geänderten Zeile. Anwendungen können angeben, dass eine Transaktion die Zeilenversionen verwendet, um Daten so anzuzeigen, wie sie zu Beginn der Transaktion oder Anweisung existierten, anstatt alle Lesevorgänge mit 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. Tabellebene-Hinweise können für einzelne Transact-SQL-Anweisungen angegeben werden, um das Verhalten 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 eine Datenbank keine Nebenläufigkeitssteuerung hat, könnten Benutzer die folgenden Nebeneffekte sehen:

• 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.

• Nicht-commitfähige Abhängigkeit (Dirty Read)

  Eine nicht-commitfähige Abhängigkeit tritt auf, wenn eine zweite Transaktion eine Zeile liest, die von einer anderen Transaktion aktualisiert wird. Die zweite Transaktion liest Daten, die noch nicht festgeschrieben wurden und möglicherweise von der Transaktion, die die Zeile aktualisiert, geändert werden könnten.

  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 wiederholbares Lesen)

  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.

• Phantomlesevorgänge

  Ein Phantomlesen ist eine Situation, die auftritt, wenn zwei identische Abfragen ausgeführt werden und die Menge der Zeilen, die von der zweiten Abfrage zurückgegeben werden, unterschiedlich ist. Das folgende Beispiel zeigt, wie es zu dieser Situation kommen kann. Angenommen, die beiden 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;
  

• Fehlende und doppelte Lesevorgänge, die durch Zeilenaktualisierungen verursacht werden

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

    Transaktionen, die auf der READ UNCOMMITTED-Ebene ausgeführt werden (oder Anweisungen, die den NOLOCK-Tabellenhinweis verwenden), geben keine gemeinsamen Sperren aus, um zu verhindern, dass andere Transaktionen die von der aktuellen Transaktion gelesenen Daten ändern. Transaktionen, die auf der READ COMMITTED-Ebene ausgeführt werden, verwenden gemeinsame Sperren, aber die Zeilen- oder Seitensperren werden freigegeben, 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. Ähnlich könnte die Zeile überhaupt nicht gelesen werden, wenn die Schlüsseländerung die Zeile an eine Position im Index verschoben hat, die Sie bereits gelesen haben. 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. Dies tritt nicht auf, wenn Sie die READ COMMITTED-Isolationsstufe verwenden.

Parallelitätstypen

Wenn mehrere Transaktionen gleichzeitig versuchen, Daten in einer Datenbank zu ändern, muss ein Kontrollsystem implementiert werden, damit die von einer Transaktion vorgenommenen Änderungen die einer anderen Transaktion nicht negativ beeinflussen. Dies wird als Parallelitätssteuerung bezeichnet.

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

• Pessimistische Parallelität-Steuerung

  Ein System von Sperren verhindert, dass Transaktionen Daten auf eine Weise ändern, die andere Transaktionen beeinflusst. Sobald eine Transaktion eine Aktion ausführt, die zum Anwenden einer Sperre führt, können andere Transaktionen 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 pessimistische Steuerung bezeichnet und vorwiegend in Systemen verwendet, in denen die Wahrscheinlichkeit von Konflikten beim Zugriff auf Daten sehr hoch ist. In diesen Systemen 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.

• Optimistische Nebenläufigkeitskontrolle

  Bei der optimistischen Nebenläufigkeitskontrolle sperren Transaktionen die Daten nicht, wenn sie diese lesen. Wenn jedoch eine Transaktion Daten aktualisiert, überprüft das System, ob eine andere Transaktion die Daten nach dem Lesen geändert hat. Wenn eine andere Transaktion die Daten aktualisiert hat, wird ein Fehler ausgelöst. Typischerweise wird die Transaktion, die den Fehler erhält, zurückgesetzt und neu gestartet. Diese Vorgehensweise wird als optimistische Steuerung bezeichnet und vorwiegend in Systemen 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.

Die Datenbank-Engine unterstützt beide Methoden der Parallelitätskontrolle. 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 Datenbank-Engine

Transaktionen geben eine Isolationsstufe an, die den Grad definiert, in dem eine Transaktion von den Ressourcen- oder Datenänderungen anderer Transaktionen isoliert sein muss. 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.

Important

Das Auswählen einer Transaktionsisolationsstufe hat keine Auswirkungen auf die Sperren, die zum Schutz der Datenänderung eingerichtet werden. Eine Transaktion hält immer ein exklusives Sperre, um Datenänderungen durchzuführen, und hält diese Sperre, bis die Transaktion abgeschlossen ist, unabhängig von der für diese Transaktion festgelegten Isolationsstufe. 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.

Ein niedrigeres Isolationsniveau erhöht die Fähigkeit vieler Transaktionen, gleichzeitig auf Daten zuzugreifen, erhöht jedoch auch die Anzahl der Parallelitätseffekte (wie z. B. Dirty Reads oder verlorene Updates), denen Transaktionen begegnen können. Umgekehrt reduziert eine höhere Isolationsstufe die Arten von Nebenläufigkeitseffekten, die bei Transaktionen auftreten können, erfordert jedoch mehr Systemressourcen und erhöht die Wahrscheinlichkeit, dass eine Transaktion eine andere blockiert. 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 bei jeder Wiederholung eines Lesevorgangs genau dieselben Daten liest. Dies wird jedoch durch ein Ausmaß an Sperren erreicht, das in Systemen mit mehreren Benutzenden wahrscheinlich zu negativen Auswirkungen für andere Transaktionen führt. Die niedrigste Isolationsstufe, READ UNCOMMITTED, kann Daten abrufen, die von anderen Transaktionen geändert, aber nicht bestätigt wurden. Alle Nebenwirkungen der Nebenläufigkeit können in READ UNCOMMITTED auftreten, aber es gibt keine Lese-Sperren oder Versionierung, sodass der Overhead minimiert wird.

Isolationsstufen der Datenbank-Engine

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

Isolationsstufe	Definition
READ UNCOMMITTED	Die niedrigste Isolationsstufe, bei der Transaktionen nur so weit isoliert sind, dass physisch inkonsistente Daten nicht 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 Datenbank-Engine behält Schreibsperren (erworben auf ausgewählten Daten) bis zum Ende der Transaktion bei, aber Lesesperren werden freigegeben, sobald der Lesevorgang durchgeführt wird. Dies ist die Standardstufe der Datenbank-Engine.
REPEATABLE READ	Die Datenbank-Engine behält Lese- und Schreibsperren, die auf ausgewählten Daten erworben 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 Datenbank-Engine behält Lese- und Schreibsperren auf ausgewählten Daten bis zum Ende der Transaktion bei. Bereichssperren werden erworben, wenn ein SELECT-Vorgang eine Bereichs-WHERE-Klausel verwendet, um Phantom-Lesevorgänge zu vermeiden. Hinweis: DDL-Operationen und Transaktionen auf replizierten Tabellen können fehlschlagen, wenn die SERIALIZABLE-Isolationsstufe angefordert wird. Dies liegt daran, dass Replikationsabfragen Hinweise verwenden, die möglicherweise nicht mit der SERIALIZABLE-Isolationsstufe kompatibel sind.

Die Datenbank-Engine unterstützt auch zwei zusätzliche Transaktionsisolationsebenen, die Zeilenversionsverwaltung verwenden. Eines ist eine Implementierung der READ COMMITTED-Isolationsstufe, und eines ist die SNAPSHOT-Transaktionsisolationsstufe.

Isolationsstufe der Zeilenversionsverwaltung	Definition
Read Committed Snapshot (RCSI)	Wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf „ON“ gesetzt ist, was die Standardeinstellung in Azure SQL-Datenbank ist, verwendet die READ COMMITTED-Isolationsstufe Zeilenversionierung, um Konsistenz auf Anweisungsebene zu gewährleisten. Lesevorgänge erfordern nur die Schema-Stabilität (Sch-S) Tabellenebene-Sperren und keine Seiten- oder Zeilensperren. Das heißt, die 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 READ_COMMITTED_SNAPSHOT-Datenbankoption auf „OFF“ gesetzt ist, was die Standardeinstellung in SQL Server und Azure SQL Managed Instance ist, verwendet die READ COMMITTED-Isolation gemeinsame Sperren, um zu verhindern, dass andere Transaktionen Zeilen ändern, während die aktuelle Transaktion einen Lesevorgang durchfü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 erfüllen die ISO-Definition der READ COMMITTED-Isolation.
SNAPSHOT	Die Momentaufnahmeisolationsstufe verwendet die Zeilenversionsverwaltung, um die Lesekonsistenz auf der Transaktionsebene zu gewährleisten. Lesevorgänge erfordern keine Seiten- oder Zeilensperren; es werden nur die Schema-Stabilität (Sch-S) Tabellensperren erworben. Wenn Sie Zeilen lesen, die von einer anderen Transaktion geändert wurden, rufen Lesevorgänge die Version der Zeile ab, die beim Start der Transaktion existierte. Sie können die SNAPSHOT-Isolation nur verwenden, wenn die Datenbankoption ALLOW_SNAPSHOT_ISOLATION auf ON eingestellt ist. Standardmäßig ist diese Option für Benutzerdatenbanken in SQL Server und Azure SQL Managed Instance auf OFF gesetzt und für Datenbanken in Azure SQL-Datenbank auf ON gesetzt. Hinweis: Die Datenbank-Engine unterstützt keine Versionierung von Metadaten. 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 unter Snapshot-Isolation nach einer BEGIN TRANSACTION-Anweisung nicht zulässig: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME oder eine beliebige Common Language Runtime (CLR) DDL-Anweisung. 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	Dreckig gelesen	Nicht wiederholbares Lesen	Phantom
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	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 ist ein Mechanismus, der von der Datenbank-Engine verwendet wird, um den gleichzeitigen Zugriff mehrerer Benutzer auf dasselbe Datenstück zu synchronisieren.

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 haben unterschiedliche Modi, wie zum Beispiel Shared (S) oder Exklusivmodus (X). 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 mit einer bereits auf denselben Daten gewährten Sperre in Konflikt steht, wird die Datenbank-Engine die anfordernde Transaktion pausieren, bis die erste Sperre freigegeben wird.

Wenn eine Transaktion Daten ändert, werden gewisse Sperren, die die Änderung schützen, aufrechterhalten, bis die Transaktion abgeschlossen ist. Wie lange eine Transaktion die zur Absicherung von Lesevorgängen erworbenen Sperren hält, hängt von der Einstellung der Transaktionsisolationsstufe ab und davon, ob optimiertes Sperren aktiviert ist.

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

• Wenn optimiertes Sperren aktiviert ist, wird nur eine Transaktions-ID (TID)-Sperre bis zum Ende der Transaktion gehalten. Unter der standardmäßigen READ COMMITTED-Isolationsstufe halten Transaktionen die für Schreibvorgänge erforderlichen Zeilen- und Seiten-Sperren erst am Ende der Transaktion. Dadurch verringert sich der Speicherbedarf für Sperren und die Notwendigkeit der Sperrenausweitung. Wenn die optimierte Sperrung aktiviert ist, bewertet die Sperre nach der Qualifikation (LAQ)-Optimierung die Prädikate einer Abfrage auf der neuesten bestätigten Version der Zeile, ohne eine Sperre zu erwerben, was die Parallelität 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. Die Sperren werden intern von einem Teil der Datenbank-Engine verwaltet, der als Sperr-Manager bezeichnet wird. Wenn eine Instanz der Datenbank-Engine eine Transact-SQL-Anweisung verarbeitet, bestimmt der Abfrageprozessor der 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 Datenbank-Engine verfügt über eine mehrstufige Sperrung, die es ermöglicht, dass verschiedene Arten von Ressourcen durch eine Transaktion gesperrt werden können. Um die Kosten für das Sperren zu minimieren, sperrt die Datenbank-Engine Ressourcen automatisch auf einer für die Aufgabe geeigneten Ebene. 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 Datenbank-Engine muss oft Sperren auf mehreren Granularitätsebenen erwerben, um eine Ressource vollständig zu schützen. Diese Gruppe von Sperren auf mehreren Granularitätsebenen wird als Sperrenhierarchie bezeichnet. Um beispielsweise ein Lesen eines Indexes vollständig zu schützen, muss eine Instanz der Datenbank-Engine möglicherweise gemeinsame Sperren auf Zeilen und Absichtssperren auf den Seiten und der Tabelle erwerben.

Die folgende Tabelle zeigt die Ressourcen, die die Datenbank-Engine sperren kann.

Resource	Description
RID	Ein Zeilenbezeichner, der verwendet wird, um eine einzelne Zeile in einem Heap zu sperren.
KEY	Eine Zeilensperre, um eine einzelne Zeile in einem B-Baum-Index zu sperren.
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. Weitere Informationen finden Sie unter Transaktions-ID (TID) Sperrung.

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

² Zusätzliche Sperrressourcen sind für XACT-Sperrressourcen verfügbar, siehe Diagnoseerweiterungen für optimiertes Sperren.

Sperrmodi

Die Datenbank-Engine sperrt Ressourcen in verschiedenen Sperrmodi, die bestimmen, wie die Ressourcen von gleichzeitigen Transaktionen zugegriffen werden können.

Die folgende Tabelle zeigt die Ressourcensperrmodi, die die Datenbank-Engine verwendet.

Sperrmodus	Description
Geteilt (S)	Wird für Lesevorgänge verwendet, die Daten nicht ändern oder aktualisieren, wie z.B. eine SELECT-Anweisung.
Update (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.
Exklusiv (X)	Wird für Datenänderungsvorgänge verwendet, wie z.B. INSERT, UPDATE oderDELETE. Stellt sicher, dass nicht mehrere Updates an derselben Ressource gleichzeitig vorgenommen werden können.
Intent	Wird verwendet, um eine Sperrhierarchie zu erstellen. Die Typen der beabsichtigten Sperren sind: beabsichtigte Sperre Shared (IS), beabsichtigte Sperre Exklusivmodus (IX) und Shared mit beabsichtigter Sperre Exklusivmodus (SIX).
Schema	Wird beim Ausführen von Vorgängen verwendet, die vom Schema einer Tabelle abhängen. Die Arten von Schema-Sperren sind: Schemaänderung (Sch-M) und Schema-Stabilität (Sch-S).
Massenaktualisierung (BU)	Verwendet beim Massendatenkopieren in eine Tabelle mit dem TABLOCK Hinweis.
Key-range	Schützt den Bereich der von einer Abfrage gelesenen Zeilen beim Verwenden der SERIALIZABLE Transaktionsisolationsebene. Stellt sicher, dass andere Transaktionen keine Zeilen einfügen können, die für die Abfragen der SERIALIZABLE-Transaktion qualifizieren würden, wenn die Abfragen erneut ausgeführt würden.

Freigegebene Sperren

Gemeinsame (S) Sperren ermöglichen gleichzeitigen Transaktionen, eine Ressource unter pessimistischer Parallelitätssteuerung zu lesen. Keine anderen Transaktionen können die Daten ändern, solange freigegebene (S) Sperren auf der Ressource existieren. Freigegebene (S) Sperren auf einer Ressource werden sofort freigegeben, sobald der Lesevorgang abgeschlossen ist, es sei denn, die Transaktionsisolationsebene ist auf REPEATABLE READ oder höher eingestellt, oder ein Sperrhinweis wird verwendet, um die freigegebenen (S) Sperren für die Dauer der Transaktion zu behalten.

Sperren aktualisieren

Die Datenbank-Engine setzt Update- (U) Sperren, während sie sich auf die Ausführung eines Updates vorbereitet. U-Sperren sind mit S-Sperren kompatibel, aber nur eine Transaktion kann jeweils eine U-Sperre auf einer bestimmten Ressource halten. Dies ist entscheidend – viele gleichzeitige Transaktionen können S-Sperren halten, aber nur eine Transaktion kann eine U-Sperre auf einer Ressource halten. Update- (U) Sperren werden schließlich zu exklusiven (X) Sperren hochgestuft, um eine Zeile zu aktualisieren.

Update- (U) Sperren können auch von anderen Anweisungen als UPDATE genommen werden, wenn der UPDLOCK-Tabellenhinweis in der Anweisung angegeben ist.

• Einige Anwendungen verwenden das Muster „eine Zeile auswählen, dann die Zeile aktualisieren“, bei dem das Lesen und Schreiben innerhalb der Transaktion explizit getrennt sind. In diesem Fall, wenn die Isolationsstufe REPEATABLE READ oder SERIALIZABLE ist, könnten gleichzeitige Aktualisierungen einen Deadlock verursachen, wie folgt:

  Eine Transaktion liest Daten und erwirbt dabei ein gemeinsames (S) Sperre auf der Ressource. Anschließend werden die Daten modifiziert, was eine Sperrenkonvertierung in eine exklusive (X) Sperre erfordert. Wenn zwei Transaktionen gemeinsame (S) Sperren auf einer Ressource erwerben und dann versuchen, Daten gleichzeitig zu aktualisieren, versucht eine Transaktion die Sperrenumwandlung in eine exklusive (X) Sperre. Die Umwandlung der geteilten in eine exklusive Sperre muss warten, da die exklusive (X) Sperre für eine Transaktion nicht mit der geteilten (S) Sperre der anderen Transaktion kompatibel ist; es tritt ein Sperren-Wartezustand auf. Die zweite Transaktion versucht, ein exklusives (X) Sperre für ihre Aktualisierung zu erwerben. Da beide Transaktionen in exklusive (X) Sperren umgewandelt werden und sie jeweils darauf warten, dass die andere Transaktion ihre gemeinsame (S) Sperre freigibt, tritt ein Deadlock auf.

  In der Standard-READ COMMITTED-Isolationsstufe sind S-Sperren von kurzer Dauer und werden freigegeben, sobald sie genutzt werden. Während die oben beschriebene Pattsituation weiterhin möglich ist, ist sie bei Sperren von kurzer Dauer viel weniger wahrscheinlich.

  Um diese Art von Deadlock zu vermeiden, können Anwendungen einem Muster folgen, bei dem eine Zeile mit UPDLOCK Hinweis ausgewählt und anschließend die Zeile aktualisiert wird.

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

Exklusive Sperren

Exklusive (X) Sperren verhindern den Zugriff auf eine Ressource durch gleichzeitige Transaktionen. Mit einem exklusiven (X) Sperre können keine anderen Transaktionen die durch die Sperre geschützten Daten ändern; Leseoperationen können nur mit der Verwendung des NOLOCK Hinweises oder der READ UNCOMMITTED Isolationsstufe durchgeführt werden.

Datenänderungsanweisungen, wie INSERT, UPDATE und DELETE, vereinen sowohl Lese- als auch Änderungsoperationen. 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. Zum Beispiel könnte eine UPDATE-Anweisung Zeilen in einer Tabelle basierend auf einem Join mit einer anderen Tabelle ändern. In diesem Fall fordert die UPDATE-Anweisung neben exklusiven Sperren auf den aktualisierten Zeilen auch gemeinsame Sperren auf den in der Join-Tabelle gelesenen Zeilen an.

Absichtssperren

Die Datenbank-Engine verwendet Intent-Sperren, um das Setzen einer gemeinsamen (S) Sperre oder einer exklusiven (X) Sperre auf eine Ressource weiter unten in der Sperrenhierarchie zu schützen. 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.
• Zur Verbesserung der Effizienz der Datenbank-Engine bei der Erkennung von Sperrkonflikten auf einer höheren Granularitätsebene.

Zum Beispiel wird eine gemeinsame Absichtssperre auf Tabellenebene angefordert, bevor gemeinsame (S) Sperren auf Seiten oder Zeilen innerhalb dieser Tabelle angefordert werden. Das Festlegen einer Absichtssperre auf Tabellenebene verhindert, dass eine andere Transaktion anschließend eine exklusive (X) Sperre für die Tabelle erwirbt, die diese Seite enthält. Intent-Sperren verbessern die Leistung, weil die Datenbank-Engine Intent-Sperren nur auf Tabellenebene prüft, um festzustellen, ob eine Transaktion sicher eine Sperre für diese Tabelle erwerben 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 Sperre (IS), Exklusivmodus (IX) und Shared mit Exklusivmodus (SIX).

Sperrmodus	Description
Absicht geteilt (IS)	Schützt angeforderte oder eingerichtete freigegebene Sperren bestimmter (aber nicht aller) Ressourcen untergeordneter Ebenen in der Hierarchie.
Absicht Exklusiv (IX)	Schützt angeforderte oder eingerichtete exklusive Sperren bestimmter (aber nicht aller) Ressourcen untergeordneter Ebenen in der Hierarchie. IXist eine Obermenge von IS, und es schützt auch die Anforderung gemeinsamer Sperren auf niedrigeren Ebenen von Ressourcen.
Geteilt mit Absicht exklusiv (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 IS-Sperren bei der Ressource auf oberster Ebene sind zulässig. Zum Beispiel erwirbt das Sperren einer Tabelle mit einem SIX auch exklusive Sperren für die zu ändernden Seiten und exklusive Sperren für die geänderten Zeilen. Es kann jeweils nur ein SIX-Sperre pro Ressource vorhanden sein, um zu verhindern, dass andere Transaktionen die Ressource aktualisieren. Andere Transaktionen können jedoch Ressourcen in der Hierarchieebene darunter lesen, indem sie IS-Sperren auf Tabellenebene erhalten.
Absicht aktualisieren (IU)	Schützt angeforderte oder eingerichtete Updatesperren aller Ressourcen untergeordneter Hierarchieebenen. IU-Sperren werden nur auf Seitenressourcen angewendet. IU-Sperren werden in IX-Sperren umgewandelt, wenn ein Aktualisierungsvorgang stattfindet.
Geteilt Absicht Aktualisierung (SIU)	Eine Kombination aus S- und IU-Sperren, die durch das separate Erwerben dieser Sperren und das gleichzeitige Halten beider Sperren entsteht. Zum Beispiel führt eine Transaktion eine Abfrage mit dem PAGLOCK-Hinweis aus und führt anschließend eine Aktualisierungsoperation durch. Die Abfrage mit dem PAGLOCK-Hinweis erwirbt das S-Sperre, und der Aktualisierungsvorgang erwirbt das IU-Sperre.
Aktualisierung Absicht exklusiv (UIX)	Eine Kombination aus U- und IX-Sperren, die durch das separate Erwerben dieser Sperren und das gleichzeitige Halten beider Sperren entsteht.

Schemasperren

Die Datenbank-Engine verwendet Schemaänderungs (Sch-M) Sperren während einer DDL-Operation (Data Definition Language) wie dem Hinzufügen einer Spalte oder dem Löschen einer Tabelle. Während der Zeit, in der es gehalten wird, verhindert das Sch-M-Sperre den gleichzeitigen Zugriff auf die Tabelle. Das bedeutet, dass die Sch-M-Sperre alle externen Operationen blockiert, bis die Sperre freigegeben wird.

Einige Datenmanipulationssprachen (DML)-Operationen, wie das Kürzen von Tabellen, verwenden Sch-M-Sperren, um den Zugriff auf betroffene Tabellen durch gleichzeitige Operationen zu verhindern.

Die Datenbank-Engine verwendet Schema-Stabilitäts-Sperren (Sch-S), wenn Abfragen kompiliert und ausgeführt werden. Sch-S-Sperren blockieren keine Transaktionssperren, einschließlich exklusiver (X) Sperren. Deshalb laufen andere Transaktionen, einschließlich derjenigen mit X-Sperren auf einer Tabelle, weiter, während eine Abfrage kompiliert wird. Jedoch werden gleichzeitige DDL-Operationen und gleichzeitige DML-Operationen, die Sch-M-Sperren erwerben, durch Sch-S-Sperren blockiert.

Massenupdatesperren

Bulkupdate (BU) Sperren ermöglichen es mehreren Threads, Daten gleichzeitig in dieselbe Tabelle zu laden, während sie verhindern, dass andere Prozesse, die keine Massendaten laden, auf die Tabelle zugreifen. Die Datenbank-Engine verwendet Massendatenaktualisierungs-Sperren (BU), wenn beide der folgenden Bedingungen erfüllt sind.

• Sie verwenden die Transact-SQL BULK INSERT-Anweisung, oder die OPENROWSET(BULK)-Funktion, oder Sie verwenden einen der Bulk Insert API-Befehle wie .NET SqlBulkCopy, OLEDB Fast Load APIs oder die ODBC Bulk Copy APIs, um Daten in eine Tabelle zu kopieren.
• Der TABLOCK Hinweis ist angegeben oder die table lock on bulk load Tabellenoption wird mit sp_tableoption festgelegt.

Tip

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

Schlüsselbereichssperren

Key-Range-Sperren schützen einen Bereich von Zeilen, die implizit in einer durch eine Transact-SQL-Anweisung gelesenen Ergebnismenge enthalten sind, während die SERIALIZABLE Transaktionsisolationsebene verwendet wird. 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.

Sperrkompatibilität

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. Solange ein exklusives (X) Sperre gehalten wird, kann keine andere Transaktion eine Sperre irgendeiner Art (gemeinsam, aktualisieren oder exklusiv) auf dieser Ressource erwerben, bis die exklusive (X) Sperre freigegeben wird. Umgekehrt können andere Transaktionen auch ein freigegebenes (S) Sperre oder ein Aktualisierungs- (U) Sperre für diese Ressource erwerben, selbst wenn die erste Transaktion noch nicht abgeschlossen ist. Andere Transaktionen können jedoch eine exklusive Sperre erst dann einrichten, wenn die freigegebene Sperre aufgehoben wurde.

Die folgende Tabelle zeigt die Kompatibilität der am häufigsten vorkommenden Sperrmodi.

Vorhandener erteilter Modus	IS	S	U	IX	SIX	X
Angeforderter Modus
Absicht geteilt (IS)	Yes	Yes	Yes	Yes	Yes	No
Geteilt (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Absicht Exklusiv (IX)	Yes	No	No	Yes	No	No
Geteilt mit Absicht exklusiv (SIX)	Yes	No	No	No	No	No
Exklusiv (X)	No	No	No	No	No	No

Note

Eine beabsichtigte exklusive (IX) Sperre ist mit einem IX Sperrmodus kompatibel, weil IX bedeutet, dass die Absicht besteht, nur einige der Zeilen zu aktualisieren, anstatt alle von ihnen. 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. Jedoch wird einer Transaktion ein X-Sperre auf Zeilenebene gewährt. Die andere Transaktion muss warten, bis die Sperre auf Zeilenebene aufgehoben wird.

Verwenden Sie die folgende Tabelle, um die Kompatibilität aller im Datenbankmodul verfügbaren Sperrmodi zu bestimmen.

Key	Description
N	Kein Konflikt
I	Illegal
C	Conflict
NL	Keine Sperre
SCH-S	Schema-Stabilitätssperre
SCH-M	Schema-Änderungssperre
S	Shared
U	Update
X	Exclusive
IS	Absicht geteilt
IU	Intent-Aktualisierung
IX	Exklusive Absicht
SIU	Mit Absichtsaktualisierung freigeben
SIX	Freigeben mit Absicht Exklusivmodus
UIX	Update mit Absicht exklusiv
BU	Massenaktualisierung
RS-S	Freigegebener Bereich-freigegeben
RS-U	Freigegebenes Bereichs-Update
RI-N	Nullbereich einfügen
RI-S	Freigegebenen Bereich einfügen
RI-U	Bereichsaktualisierung einfügen
RI-X	Bereich exklusiv einfügen
RX-S	Exklusive Reichweite gemeinsam genutzt
RX-U	Exklusives Bereichs-Update
RX-X	Exklusives Sortiment

Sperren des Schlüsselbereichs

Key-Range-Sperren schützen einen Bereich von Zeilen, die implizit in einer durch eine Transact-SQL-Anweisung gelesenen Ergebnismenge enthalten sind, während die SERIALIZABLE Transaktionsisolationsebene verwendet wird. Die SERIALIZABLE Isolationsstufe erfordert, dass jede Abfrage, die während einer Transaktion ausgeführt wird, jedes Mal denselben Satz von Zeilen erhält, wenn sie während der Transaktion ausgeführt wird. Ein Schlüsselbereichsschloss erfüllt diese Anforderung, indem es verhindert, dass andere Transaktionen neue Zeilen einfügen, deren Schlüssel in den von der SERIALIZABLE Transaktion gelesenen Schlüsselbereich fallen würden.

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. Zum Beispiel könnte eine SERIALIZABLE-Transaktion eine SELECT-Anweisung ausgeben, die alle Zeilen liest, deren Schlüsselwerte der Bedingung BETWEEN 'AAA' AND 'CZZ' entsprechen. 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 mit einem Bindestrich (–) verbunden, wie zum Beispiel RangeT-K.

Range	Row	Mode	Description
RangeS	S	RangeS-S	Shared Bereich, gemeinsame Sperre für freigegebene Ressource; SERIALIZABLE-Bereich überprüfen.
RangeS	U	RangeS-U	Shared Bereich, Aktualisierungssperre der Ressource aktualisieren; SERIALIZABLE überprüfen aktualisieren.
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.

Note

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
Geteilt (S)	Yes	Yes	No	Yes	Yes	Yes	No
Update (U)	Yes	No	No	Yes	No	Yes	No
Exklusiv (X)	No	No	No	No	No	Yes	No
RangeS-S	Yes	Yes	No	Yes	Yes	No	No
RangeS-U	Yes	No	No	Yes	No	No	No
RangeI-N	Yes	Yes	Yes	No	No	Yes	No
RangeX-X	No	No	No	No	No	No	No

Konvertierungssperren

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

Sperre 1	Sperren 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:

• Das Transaktionsisolation-Level muss auf SERIALIZABLE gesetzt werden.
• Der Abfrageprozessor muss zum Implementieren des Bereichsfilterprädikäts verwendet werden. Zum Beispiel könnte die WHERE-Klausel in einer SELECT-Anweisung eine Bereichsbedingung mit diesem Prädikat festlegen: ColumnX BETWEEN N'AAA' AND N'CZZ'. Ein Bereichsschloss kann nur erworben werden, wenn ColumnX durch einen Indexschlüssel abgedeckt ist.

Examples

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

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';

Schlüsselbereichssperren werden auf die Indexeinträge angewendet, die dem Bereich der Zeilen entsprechen, in denen der Name zwischen den Werten Adam und Dale liegt, um zu verhindern, dass neue Zeilen, die in der vorherigen Abfrage qualifiziert sind, hinzugefügt oder gelöscht werden. Obwohl der erste Name in diesem Bereich Adam ist, stellt die RangeS-S Modus-Schlüsselbereichssperre für diesen Indexeintrag sicher, dass keine neuen Namen, die mit dem Buchstaben A beginnen, vor Adam hinzugefügt werden können, wie zum Beispiel Abigail. Ähnlich stellt das RangeS-S-Schlüsselbereichsschloss beim Indexeintrag für Dale sicher, dass keine neuen Namen, die mit dem Buchstaben C beginnen, nach Carlos hinzugefügt werden können, wie z. B. Clive.

Note

Die Zahl der gehaltenen RangeS-S Sperren ist n+1, wobei n die Zahl der Zeilen ist, die die Abfrage erfüllen.

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. Der RangeS-S Modus-Schlüsselbereichsperre wird auf den Indexeintrag Bing gesetzt. Dadurch wird verhindert, dass andere Transaktionen Werte, wie etwa Bill, zwischen die Indexeinträge Ben und Bing einfügen.

Löschen-Operation ohne optimiertes Sperren

Beim Löschen einer Zeile innerhalb einer Transaktion muss der Bereich, in den die Zeile fällt, nicht für die Dauer der Transaktion, die die Löschoperation durchführt, gesperrt werden. Die Serialisierbarkeit wird bereits dann aufrechterhalten, wenn der gelöschte Schlüsselwert bis zum Ende der Transaktion gesperrt wird. Zum Beispiel, bei dieser DELETE-Anweisung:

DELETE mytable
WHERE name = 'Bob';

Ein exklusives (X) Sperre wird auf den Indexeintrag gesetzt, der dem Namen Bob entspricht. Andere Transaktionen können Werte vor oder nach der Zeile mit dem Wert Bob einfügen oder löschen, die gelöscht wird. Jedoch wird jede Transaktion blockiert, die versucht, Zeilen zu lesen, einzufügen oder zu löschen, die dem Wert Bob entsprechen, bis die Löschtransaktion entweder abgeschlossen oder zurückgesetzt wird. (Die READ_COMMITTED_SNAPSHOT-Datenbankoption und die SNAPSHOT-Isolationsstufe erlauben auch Lesevorgänge von einer Zeilenversion des zuvor festgeschriebenen Zustands.)

Das Löschen des Bereichs kann mithilfe von drei grundlegenden Sperrmodi ausgeführt werden: Zeilen-, Seiten- oder Tabellensperre. Die Sperrstrategie für Zeilen, Seiten oder Tabellen wird vom Abfrageoptimierer festgelegt oder kann vom Benutzer durch Abfrageoptimierer-Hinweise wie ROWLOCK, PAGLOCK oder TABLOCK angegeben werden. Wenn PAGLOCK oder TABLOCK verwendet wird, gibt die Datenbank-Engine eine Indexseite sofort frei, wenn alle Zeilen von dieser Seite gelöscht werden. Im Gegensatz dazu werden, wenn ROWLOCK verwendet wird, alle gelöschten Zeilen nur als gelöscht markiert; sie werden später mithilfe eines Hintergrundprozesses von der Indexseite entfernt.

Löschoperation mit optimiertem Sperren

Beim Löschen einer Zeile innerhalb einer Transaktion werden die Zeilen- und Seiten-Sperren schrittweise erworben und freigegeben und nicht für die Dauer der Transaktion gehalten. 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. Ein Sperre wird auf die Steuernummer der Indexzeilen erworben, die dem Wert Bob entsprechen. 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. Jede Transaktion, die versucht, Zeilen mit dem Wert Bob zu lesen, einzufügen oder zu löschen, wird blockiert, bis die löschende Transaktion entweder bestätigt oder zurückgesetzt wird. (Die READ_COMMITTED_SNAPSHOT-Datenbankoption und die SNAPSHOT-Isolationsstufe erlauben auch Lesevorgänge von einer Zeilenversion des zuvor festgeschriebenen Zustands.)

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

Einfügevorgang ohne optimierte Sperrung

Wenn eine Zeile innerhalb einer Transaktion eingefügt wird, muss der Bereich, in den die Zeile fällt, nicht für die Dauer der Transaktion, die die Einfügeoperation durchfü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');

Der RangeI-N Modus-Schlüsselbereichssperre wird auf die Indexzeile gesetzt, die dem Namen David entspricht, um den Bereich zu testen. Wenn das Sperren gewährt wird, wird eine Zeile mit dem Wert Dan eingefügt und ein exklusives (X) Sperre auf die eingefügte Zeile gesetzt. Die RangeI-N-Modusbereichssperre ist nur zum Testen des Bereichs erforderlich und wird nicht für die Dauer der Transaktion gehalten, die den Einfügevorgang durchführt. Andere Transaktionen können Werte vor oder nach der eingefügten Zeile mit dem Wert Dan einfügen oder löschen. Jede Transaktion, die versucht, die Zeile mit dem Wert Dan zu lesen, einzufügen oder zu löschen, wird jedoch blockiert, bis die einfügende Transaktion entweder bestätigt oder zurückgesetzt wird.

Einfügevorgang mit optimierter Sperrung

Wenn eine Zeile innerhalb einer Transaktion eingefügt wird, muss der Bereich, in den die Zeile fällt, nicht für die Dauer der Transaktion, die die Einfügeoperation durchführt, gesperrt werden. Zeilen- und Seiten-Sperren werden selten erworben, nur wenn ein Online-Index-Neuaufbau im Gange ist oder wenn es gleichzeitige SERIALIZABLE-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. Zum Beispiel, bei dieser INSERT-Anweisung:

INSERT mytable VALUES ('Dan');

Mit optimierter Sperrung wird ein RangeI-N-Sperre nur erworben, wenn es mindestens eine Transaktion gibt, die die SERIALIZABLE-Isolationsebene in der Instanz verwendet. Der RangeI-N Modus-Schlüsselbereichssperre wird auf die Indexzeile gesetzt, die dem Namen David entspricht, um den Bereich zu testen. Wenn das Sperren gewährt wird, wird eine Zeile mit dem Wert Dan eingefügt und ein exklusives (X) Sperre auf die eingefügte Zeile gesetzt. Die RangeI-N-Modusbereichssperre ist nur zum Testen des Bereichs erforderlich und wird nicht für die Dauer der Transaktion gehalten, die den Einfügevorgang durchführt. Andere Transaktionen können Werte vor oder nach der eingefügten Zeile mit dem Wert Dan einfügen oder löschen. Jede Transaktion, die versucht, die Zeile mit dem Wert Dan zu lesen, einzufügen oder zu löschen, wird jedoch blockiert, bis die einfügende Transaktion entweder bestätigt oder zurückgesetzt wird.

Lock-Eskalation

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

Während die Datenbank-Engine niedrigstufige Sperren erwirbt, platziert sie auch Absichtssperren auf den Objekten, die die Objekte niedrigerer 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. Zum Beispiel könnte die Datenbank-Engine Seitensperren auf einen nicht gruppierten Index setzen (wenn genügend zusammenhängende Schlüssel im Indexknoten ausgewählt werden, um die Abfrage zu erfüllen) und Zeilensperren auf den gruppierten Index oder Heap.

Um eine Sperrenausweitung durchzuführen, versucht das Datenbankmodul, die Absichtssperre auf der Tabelle in die entsprechende vollständige Sperre zu ändern, zum Beispiel eine Absicht exklusive (IX) Sperre in eine exklusive (X) Sperre oder eine Absicht gemeinsame (IS) Sperre in eine gemeinsame (S) Sperre zu ändern. Wenn der Versuch der Sperrenausweitung erfolgreich ist und die vollständige Tabellensperre erworben wird, werden alle HoBT-, Seiten- (PAGE) oder Zeilensperren (RID, KEY), die von der Transaktion auf dem Heap oder Index gehalten werden, freigegeben. 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.

Note

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 Instanzen könnte die TABLE Sperrgranularität besser performen.

Wenn ein Sperrenausweitung Versuch fehlschlägt, weil gleichzeitig Transaktionen Konflikte verursachen, versucht das Datenbankmodul die Sperrenausweitung erneut für jeweils zusätzliche 1.250 Sperren, die von der Transaktion erworben 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 erworben und zum Zeitpunkt, zu dem das Ereignis startet, noch gehalten werden, ausgeweitet, wenn die Tabelle von der aktuellen Anweisung referenziert wird und im Sperrenausweitungsevent enthalten ist.

Nehmen wir beispielsweise an, eine Sitzung:

• beginnt eine Transaktion,
• Aktualisiert TableA. Dies generiert exklusive Zeilensperren in TableA, die gehalten werden, bis die Transaktion abgeschlossen ist.
• Aktualisiert TableB. Dies generiert exklusive Zeilensperren in TableB, die gehalten werden, bis die Transaktion abgeschlossen ist.
• Führt ein SELECT aus, das TableA mit TableC repliziert. Der Abfrageausführungsplan ruft die aus TableA abzurufenden Zeilen auf, bevor die Zeilen aus TableC abgerufen werden.
• Die SELECT-Anweisung löst eine Sperrenausweitung aus, während sie Zeilen von TableA abruft und bevor sie auf TableC zugegriffen hat.

Bei erfolgreicher Sperrenausweitung werden nur die von der Sitzung für TableA gehaltenen Sperren ausgeweitet. Dies umfasst sowohl die gemeinsamen Sperren aus der SELECT-Anweisung als auch die exklusiven Sperren aus der zurückliegenden UPDATE-Anweisung. Während nur die Sperren, die die Sitzung in TableA- für die SELECT-Anweisung erworben hat, gezählt werden, um zu bestimmen, ob eine Sperrenausweitung erfolgen soll, werden nach erfolgreicher Ausweitung alle Sperren, die die Sitzung in TableA hält, zu einer exklusiven Sperre auf der Tabelle ausgeweitet, und alle anderen Sperren mit niedrigerer Granularität, einschließlich beabsichtigter Sperren, auf TableA werden released.

Es wird kein Versuch unternommen, Sperren auf TableB auszuweiten, da es keinen aktiven Verweis auf TableB- in der SELECT-Anweisung gab. 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. Während das Datenbankmodul Zeilen- und Seitensperren erwirbt, kann eine Sperrenausweitung ähnlich, aber weit seltener auftreten. 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 das optimierte Sperren aktiviert ist und in der Standard-READ COMMITTED-Isolationsstufe, gibt die Datenbank-Engine Zeilen- und Seitensperren frei, sobald die Zeile geändert wird. 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, dass ein einzelne SELECT-Anweisung ein beitreten ist, das auf drei Tabellen in dieser Sequenz 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 Arbeitsspeicher-Schwellenwert hängt von der Einstellung der sperren Konfigurationsoption ab:

• Wenn die locks Option auf ihre Standardeinstellung von 0 gesetzt ist, wird die Sperrenausweitungsschwelle erreicht, wenn der durch Sperrobjekte genutzte Arbeitsspeicher 24 Prozent des vom Datenbankmodul genutzten Arbeitsspeichers beträgt, AWE-Speicher ausgeschlossen. 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 Option locks einen anderen Wert als 0 hat, beträgt der Schwellenwert für die Eskalation der Sperre 40 Prozent (oder weniger, wenn die Speicherauslastung hoch ist) des Werts der Option locks.

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.

Sperrenausweitung mit gemischten Sperrungstypen

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, die auf dieselbe Tabelle verweist.

Die Anweisung UPDATE erwirbt die folgenden Sperren:

• Exklusive (X) Sperren für die aktualisierten Datenzeilen.
• Exklusive (IX) Sperren für die Cluster-Index-Seiten, die diese Zeilen enthalten.
• Eine IX-Sperre für den Cluster-Index und eine weitere für die Tabelle.

Die Anweisung SELECT erwirbt die folgenden Sperren:

• Geteilte (S) Sperren für alle Datenzeilen, die gelesen werden, es sei denn, die Zeile ist bereits durch eine X-Sperre aus der Anweisung UPDATE geschützt.
• Absicht geteilt (IS) sperrt alle Clustered-Index-Seiten, die diese Zeilen enthalten, es sei denn, die Seite ist bereits durch eine IX-Sperre geschützt.
• Keine Sperre für den Cluster-Index oder die Tabelle, da diese bereits durch IX-Sperren geschützt sind.

Wenn die Anweisung SELECT genügend Sperren erhält, um eine Eskalation der Sperre auszulösen, und die Eskalation erfolgreich ist, wird die Sperre IX für die Tabelle in eine Sperre X konvertiert, und alle Zeilen-, Seiten- und Indexsperren werden freigegeben. Sowohl die Aktualisierungen als auch die Lesevorgänge werden durch die Sperre X in der Tabelle geschützt.

Reduzieren von Sperren und Sperrenausweitung

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.

  • Optimierte Sperren bietet einen verbesserten Mechanismus zur Sperrung von Transaktionen, der den Speicherverbrauch für Sperren und die Blockierung gleichzeitiger 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 die Option READ_COMMITTED_SNAPSHOT für die Datenbank, um die Vorteile der optimierten Sperrung optimal zu nutzen. Dies ist die Standardeinstellung 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 READ_COMMITTED_SNAPSHOT-Datenbankoption ON ist.
  • SNAPSHOT-Isolationsstufe.
  • READ UNCOMMITTED-Isolationsstufe. Diese kann nur für Systeme verwendet werden, die mit Dirty Reads arbeiten können.

• Verwenden Sie die Tabellenhinweise PAGLOCK oder TABLOCK, damit das Datenbankmodul Seiten-, Heap- oder Indexsperren anstelle von Sperren auf niedriger Ebene 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.

• Wenn optimierte Sperren nicht verfügbar sind, verwenden Sie für partitionierte Tabellen die Option LOCK_ESCALATION von ALTER TABLE, um Sperren auf die Partition anstelle der Tabelle zu eskalieren oder die Sperreneskalation für eine Tabelle zu deaktivieren.

• Teilen Sie eine große Anzahl von Vorgängen in mehrere kleinere Vorgänge auf. Angenommen, Sie haben die folgende Abfrage ausgeführt, um mehrere hunderttausend alte Zeilen aus einer Überwachungstabelle zu entfernen, und dann festgestellt, dass sie eine Sperreneskalation verursacht hat, die andere Benutzer blockiert hat:

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  Indem Sie diese Zeilen einige hundert auf einmal entfernen, können Sie die Anzahl der Sperren, die sich pro Transaktion ansammeln, drastisch reduzieren und eine Sperreneskalation verhindern. Beispiel:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Reduzieren Sie den Footprint einer Abfragesperre, indem Sie die Abfrage so effizient wie möglich gestalten. Große Scans oder eine große Anzahl von Schlüsselsuchen können die Wahrscheinlichkeit einer Sperreneskalation erhöhen. Darüber hinaus erhöht dies die Wahrscheinlichkeit von Deadlocks und wirkt sich im Allgemeinen nachteilig auf Parallelität und Leistung aus. Nachdem Sie die Abfrage gefunden haben, die die Sperreneskalation verursacht, suchen Sie nach Möglichkeiten zum Erstellen neuer Indizes oder zum Hinzufügen von Spalten zu einem vorhandenen Index, um vollständige Index- oder Tabellenscans zu entfernen und die Effizienz von Indexsuchen 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 Indexsuchen so wenige Zeilen wie möglich zurückgeben, um die Kosten für Schlüsselsuchen zu minimieren (die Selektivität des Index für die bestimmte Abfrage zu maximieren). Wenn das Datenbankmodul schätzt, dass ein logischer Schlüsselsuchoperator viele Zeilen zurückgeben kann, kann es eine Prefetch-Optimierung verwenden, um die Suche durchzuführen. Wenn das Datenbankmodul Prefetch für eine Suche verwendet, muss es die Transaktionsisolationsstufe eines Teils der Abfrage auf REPEATABLE READ erhöhen. Dies bedeutet, dass eine SELECT-Anweisung auf einer READ COMMITTED-Isolationsstufe viele tausend Schlüsselsperren (sowohl auf dem gruppierten Index als auch auf einem nicht gruppierten Index) erwerben kann, was dazu führen kann, dass eine solche Abfrage die Schwellenwerte für die Sperreneskalation überschreitet. Dies ist besonders wichtig, wenn Sie feststellen, dass die eskalierte Sperre eine gemeinsame Tabellensperre ist, die jedoch auf der Standard-READ COMMITTED-Isolationsebene nicht häufig zu sehen ist.

  Wenn eine Schlüsselsuche mit der Prefetch-Optimierung eine Sperreneskalation verursacht, sollten Sie in Betracht ziehen, zusätzliche Spalten zum nicht gruppierten Index hinzuzufügen, der im Index Seek oder im Index Scan logischen Operator unterhalb des Schlüsselsuche logischen Operators im Abfrageplan erscheint. Es könnte möglich sein, einen umfassenden Index zu erstellen (einen Index, der alle Spalten in einer Tabelle umfasst, die in der Abfrage verwendet wurden), oder zumindest einen Index, der die Spalten abdeckt, die für die Join-Kriterien oder in der WHERE-Klausel verwendet wurden, wenn es unpraktisch ist, alles in die SELECT-Spaltenliste aufzunehmen. Ein geschachtelter Schleifen-Join kann auch die Prefetch-Optimierung verwenden, was das gleiche Sperrverhalten verursacht.

• 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. Zusätzlich, wenn ein Versuch zur Sperreneskalation fehlschlägt, weil eine andere SPID eine inkompatible Tabellensperre hält, blockiert die Abfrage, die die Eskalation versucht hat, nicht, während sie auf eine Tabellensperre wartet. 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. Eine Methode, um die Sperreneskalation auf einer bestimmten Tabelle zu verhindern, besteht darin, eine Sperre auf einer anderen Verbindung zu erwerben und zu halten, die nicht mit dem eskalierten Sperrentyp kompatibel ist. Eine Absicht-exclusive (IX)-Sperre auf Tabellenebene sperrt keine Zeilen oder Seiten, ist aber immer noch nicht mit einer eskalierten freigegebenen (S) oder exklusiven (X) Tabellensperre kompatibel. Angenommen, Sie müssen einen Batch-Job ausführen, der eine große Anzahl von Zeilen in der mytable-Tabelle ändert und der eine Blockierung verursacht hat, die aufgrund einer Sperreneskalation 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;
  

  Diese Abfrage erwirbt und hält ein IX-Sperre auf mytable für eine Stunde, was eine Sperreskalation auf der Tabelle während dieser Zeit verhindert. Dieses Batch modifiziert keine Daten und blockiert keine anderen Abfragen (es sei denn, die andere Abfrage erzwingt eine Tabellensperre mit dem TABLOCK Hinweis oder ein Administrator hat Seiten- oder Zeilensperren auf einem Index auf mytable deaktiviert).

• Sie können auch mit den Ablaufverfolgungsflags 1211 und 1224 alle oder einige Sperrenausweitungen deaktivieren. Diese Ablaufverfolgungsflags deaktivieren jedoch alle Sperrenausweitungen global für die gesamte Datenbank-Engine-Instanz. Die Sperrenausweitung ist in der Datenbank-Engine sehr nützlich, da die Effizienz von Abfragen maximiert wird, die andernfalls durch den Mehraufwand für das Abrufen und Freigeben mehrerer Tausend Sperren verlangsamt würden. Sperrenausweitung hilft auch, den erforderlichen Speicher 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.

  Note

  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.

  Note

  Die Verwendung eines Sperrhinweises wie ROWLOCK ändert nur die anfängliche Sperrung. Sperrhinweise verhindern keine Sperrenausweitung.

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

Sperrenausweitung überwachen

Überwachen Sie die Sperreskalation mithilfe des lock_escalation Extended Events, wie im folgenden Beispiel:

-- 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

Dynamische Verriegelung

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.

Die Datenbank-Engine verwendet eine dynamische Sperrstrategie, um die effektivsten Sperren zu bestimmen. Die Datenbank-Engine bestimmt automatisch, welche Sperren bei der Ausführung der Abfrage am geeignetsten sind, basierend auf den Eigenschaften des Schemas und der Abfrage. Zum Beispiel kann der Optimierer zur Reduzierung des Sperraufwands Seiten-Sperren in einem Index wählen, wenn ein Index-Scan durchgeführt wird.

Sperr-Partitionierung

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 logischen 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.

• Memory

  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:

• Nur die Sperrmodi NL, Sch-S, IS, IU und IX werden auf einer einzelnen Partition erworben.

• Freigegebene (S), exklusive (X) und andere Sperren in Modi außer NL, Sch-S, IS, IU und IX müssen auf allen Partitionen beginnend mit der Partition ID 0 und folgend in der Reihenfolge der Partition ID erworben werden. 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 Leistungsindikatoren für die SQL Server-Sperre 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 erwirbt und behält diese Anweisung ein Intent Shared (IS) Sperre auf der Tabelle (für diese Darstellung werden Zeilen- und Seitensperren ignoriert). Die IS-Sperre wird nur für die Partition aktiviert, die der Transaktion zugewiesen ist. In diesem Beispiel wird davon ausgegangen, dass das IS-Sperre auf der Partition-ID 7 erworben 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 SELECT-Anweisung, die unter dieser Transaktion ausgeführt wird, erwirbt und behält ein gemeinsames (S) Sperre auf der Tabelle. Das S-Sperre wird auf allen Partitionen erworben, was zu mehreren Tabellensperren führt, eine für jede Partition. Zum Beispiel werden auf einem 16-CPU-System 16 S Sperren über die Sperrpartition-IDs 0-15 ausgegeben. Da das S-Sperre mit der IS-Sperre, die auf Partitions-ID 7 von der Transaktion in Sitzung 1 gehalten wird, kompatibel ist, gibt es keine 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) Tabellen-Sperrhinweises versucht die Transaktion, eine X-Sperre auf der Tabelle zu erwerben. Die S-Sperre, die von der Transaktion in Sitzung 2 gehalten wird, blockiert jedoch die X-Sperre bei 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 erwirbt und behält diese Anweisung ein Intent Shared (IS) Sperre auf der Tabelle (für diese Darstellung werden Zeilen- und Seitensperren ignoriert). Die IS-Sperre wird nur für die Partition aktiviert, die der Transaktion zugewiesen ist. In diesem Beispiel wird davon ausgegangen, dass das IS-Sperre auf der Partition-ID 6 erworben 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, ein exklusives (X) Sperre auf der Tabelle zu erwerben. Denken Sie daran, dass das X-Sperre für alle Partitionen, beginnend mit der Partition-ID 0, erworben werden muss. Die X-Sperre wird auf allen Partitions-IDs 0-5 erworben, aber durch die IS-Sperre blockiert, die auf Partitions-ID 6 erworben wurde.

Bei den Partition-IDs 7-15, die das X-Sperre noch nicht erreicht hat, können andere Transaktionen weiterhin Sperren erwerben.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Zeilenversionsbasierte Isolationsstufen in der Datenbank-Engine

Ab SQL Server 2005 (9.x) bietet die Datenbank-Engine eine Implementierung eines bestehenden Transaktionsisolationsniveaus, READ COMMITTED, das eine Anweisungsebene-Snapshot mit Zeilenversionierung bereitstellt. Die Datenbank-Engine bietet auch ein Transaktionsisolationslevel, SNAPSHOT, das eine Transaktionslevel-Snapshot ebenfalls unter Verwendung von Zeilen-Versionierung bereitstellt.

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. Zeilenversionsverwaltung wird verwendet, um die folgenden Funktionen zu implementieren:

• Erstellen Sie die 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 Sie Indexoperationen, die die ONLINE Option angeben.
• Unterstützen von auf der Zeilenversionsverwaltung basierenden Transaktionsisolationsstufen:
  • Eine neue Implementierung der READ COMMITTED-Isolationsstufe, die Zeilenversionierung verwendet, um eine konsistente Lesbarkeit auf Anweisungsebene zu gewährleisten.
  • Eine neue Isolationsstufe, SNAPSHOT, um Konsistenz beim Lesen auf Transaktionsebene bereitzustellen.

Zeilenversionen werden in einem Versionsspeicher gespeichert. Wenn beschleunigte Datenbankwiederherstellung (ADR) für eine Datenbank aktiviert ist, wird der Versionsspeicher in dieser Datenbank erstellt. Andernfalls wird der Versionsspeicher in der tempdb-Datenbank erstellt.

Die Datenbank muss genügend Raum für den Versionsspeicher haben. Wenn sich der Versionsspeicher in tempdbbefindet und die tempdb-Datenbank voll ist, wird die Generierung von Versionen bei Aktualisierungsvorgängen eingestellt, jedoch werden diese weiterhin erfolgreich ausgeführt. Lesevorgänge hingegen können fehlschlagen, da eine benötigte Zeilenversion fehlt. Das wirkt sich auf Vorgänge wie Trigger, MARS und Onlineindizierung aus.

Wenn ADR verwendet wird und der Versionsspeicher voll ist, werden Lesevorgänge weiterhin erfolgreich ausgeführt, aber Schreibvorgänge, die Versionen generieren, z. B. UPDATE und DELETE fehlschlagen. INSERTVorgänge sind weiterhin erfolgreich, wenn die Datenbank über ausreichend Speicherplatz verfügt.

Die Verwendung der Zeilenversionsverwaltung für READ COMMITTED- und SNAPSHOT-Transaktionen ist ein zweistufiger Prozess:

1. Setzen Sie entweder die READ_COMMITTED_SNAPSHOT oder die ALLOW_SNAPSHOT_ISOLATION Datenbankoptionen oder beide 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 das SNAPSHOT Isolationslevel festlegen.

Wenn entweder die READ_COMMITTED_SNAPSHOT- oder die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON gesetzt ist, weist die Datenbank-Engine jeder Transaktion, die Daten mithilfe der Zeilenversionsverwaltung manipuliert, eine Transaktionssequenznummer (XSN) zu. Die Transaktionen starten zu dem Zeitpunkt, wenn eine BEGIN TRANSACTION-Anweisung ausgeführt wird. Allerdings beginnt 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 READ_COMMITTED_SNAPSHOT- oder die ALLOW_SNAPSHOT_ISOLATION-Datenbankoptionen auf ON gesetzt sind, werden logische Kopien (Versionen) für alle Datenänderungen, die in der Datenbank durchgeführt werden, beibehalten. Jedes Mal, wenn eine Zeile durch eine bestimmte Transaktion geändert wird, speichert die Instanz der Datenbank-Engine eine Version des zuvor festgeschriebenen Bildes der Zeile im Versionsspeicher. 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 versionierten Zeilen im Versionsspeicher verkettet.

Note

Für die Änderung von großen Objekten (LOBs) wird nur das geänderte Fragment in den Versionsspeicher 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 Datenbank-Engine verfolgt die früheste nützliche Transaktionssequenznummer und löscht regelmäßig alle Zeilenversionen, die mit Transaktionssequenznummern versehen sind, die niedriger als die früheste nützliche Sequenznummer sind.

Wenn beide Datenbankoptionen auf OFF eingestellt sind, werden nur Zeilen versioniert, die durch Trigger oder MARS-Sitzungen geändert oder durch Online-Indexoperationen gelesen wurden. Diese Zeilenversionen werden jedoch freigegeben, sobald sie nicht mehr benötigt werden. Ein Hintergrundprozess entfernt veraltete Zeilenversionen.

Note

Bei kurzlebigen Transaktionen kann eine Version einer modifizierten Zeile im Pufferpool zwischengespeichert werden, ohne dass sie in den Versionsspeicher geschrieben wird. Wenn der Bedarf an der versionierten Zeile nur von kurzer Dauer ist, wird die Zeile aus dem Buffer-Pool entfernt und verursacht keinen I/O-Overhead.

Verhalten beim Lesen von Daten

Wenn Transaktionen, die unter zeilenversionsbasierter Isolation ausgeführt werden, Daten lesen, erwerben die Lesevorgänge keine gemeinsamen (S) Sperren für die gelesenen Daten und blockieren daher nicht die Transaktionen, die Daten ändern. Außerdem wird der Aufwand für das Sperren von Ressourcen minimiert, weil nur eine reduzierte Anzahl von Sperren angefordert wird. READ COMMITTEDDie Isolation mit Zeilenversionsverwaltung und SNAPSHOT-Isolation sind so konzipiert, dass sie eine Anweisungsebene oder Transaktionsebene der Lesekonsistenz von versionierten Daten bereitstellen.

Alle Abfragen, einschließlich Transaktionen, die unter auf Zeilenversionsbasierte Isolationsstufen ausgeführt werden, erwerben während der Kompilierung und Ausführung Schema-Stabilitäts- (Sch-S) Sperren. Aus diesem Grund werden Abfragen blockiert, wenn eine gleichzeitige Transaktion eine Schemaänderung (Sch-M) Sperre auf der Tabelle hält. Zum Beispiel erwirbt ein Daten-Definitionssprache (DDL)-Vorgang eine Sch-M-Sperre, bevor er die Schema-Informationen der Tabelle ändert. Transaktionen, einschließlich derjenigen, die unter einer auf Zeilenversionsverwaltung basierenden Isolationsstufe laufen, werden blockiert, wenn versucht wird, ein Sch-S-Sperre zu erwerben. Umgekehrt blockiert eine Abfrage, die eine Sch-S-Sperre hält, eine gleichzeitige Transaktion, die versucht, eine Sch-M-Sperre zu erwerben.

Wenn eine Transaktion mit dem SNAPSHOT-Isolationslevel beginnt, zeichnet die Instanz der Datenbank-Engine alle derzeit aktiven Transaktionen auf. Wenn die SNAPSHOT-Transaktion eine Zeile liest, die eine Versionskette hat, folgt die Datenbank-Engine der Kette und ruft die Zeile ab, bei der die Transaktionssequenznummer lautet:

• 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.

Lesevorgänge, die von einer SNAPSHOT-Transaktion durchgeführt werden, rufen die letzte Version jeder Zeile ab, die zum Zeitpunkt des Starts der SNAPSHOT-Transaktion festgeschrieben wurde. Damit wird ein transaktionskonsistente Momentaufnahme der Daten bereitgestellt, wie sie beim Start der Transaktion vorlagen.

READ COMMITTED-Transaktionen, die Zeilenversionsverwaltung verwenden, funktionieren auf ähnliche Weise. Der Unterschied besteht darin, dass die READ COMMITTED-Transaktion ihre eigene Transaktionsfolgenummer bei der Auswahl von Zeilenversionen nicht verwendet. Jedes Mal, wenn eine Anweisung gestartet wird, liest die READ COMMITTED-Transaktion die zuletzt für diese Instanz der Datenbank-Engine ausgegebene Transaktionssequenznummer. Dies ist die Transaktionssequenznummer, die verwendet wird, um die Zeilenversionen für diese Anweisung auszuwählen. Dies ermöglicht READ COMMITTED Transaktionen, eine Momentaufnahme der Daten zu sehen, wie sie zu Beginn jeder Anweisung existieren.

Note

Obwohl READ COMMITTED-Transaktionen, die Zeilenversionsverwaltung verwenden, eine transaktional konsistente Ansicht der Daten auf Anweisungsebene bieten, werden die von dieser Art von Transaktion generierten oder abgerufenen Zeilenversionen bis zum Abschluss der Transaktion beibehalten.

Verhalten beim Ändern von Daten

Das Verhalten von Datenschreibvorgängen unterscheidet sich mit und ohne aktivierte optimierte Sperrmechanismen.

Ändern von Daten ohne optimierte Sperrung

In einer READ COMMITTED-Transaktion unter Verwendung der Zeilenversionsverwaltung erfolgt die Auswahl der zu aktualisierenden Zeilen mittels eines blockierenden Scans, bei dem ein Aktualisierungssperre (U) auf die Datenzeile erlangt wird, sobald die Datenwerte gelesen werden. Dies ist dasselbe wie eine READ COMMITTED-Transaktion, die keine Zeilenversionierung verwendet. 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 unter SNAPSHOT-Isolation ausgeführt werden, verfolgen einen optimistischen Ansatz zur Datenänderung, indem sie Sperren für Daten nur zur Durchsetzung von Einschränkungen vor der Durchführung der Änderung erwerben. Andernfalls werden erst dann Sperren für Daten aktiviert, wenn die Daten geändert werden sollen. Wenn eine Datenzeile die Aktualisierungskriterien erfüllt, überprüft die SNAPSHOT-Transaktion, dass die Datenzeile nicht von einer parallelen Transaktion geändert wurde, die nach Beginn der SNAPSHOT-Transaktion abgeschlossen wurde. Wenn die Datenzeile außerhalb der SNAPSHOT Transaktion geändert wurde, tritt ein Aktualisierungskonflikt auf und die SNAPSHOT Transaktion wird beendet. Der Aktualisierungskonflikt wird von der Datenbank-Engine behandelt, und es gibt keine Möglichkeit, die Erkennung von Aktualisierungskonflikten zu deaktivieren.

Note

Aktualisierungsvorgänge, die unter SNAPSHOT-Isolation ausgeführt werden, werden intern unter READ COMMITTED-Isolation ausgeführt, wenn die SNAPSHOT-Transaktion auf eines der folgenden zugreift:

Eine Tabelle mit einer Fremdschlüsselbeschränkung

Eine Tabelle, die in der Fremdschlüsselbeschränkung einer anderen Tabelle referenziert 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 Daten von einer anderen Transaktion geändert wurden, stößt die SNAPSHOT-Transaktion auf einen Aktualisierungskonflikt und wird beendet. Aktualisierungskonflikte müssen von der Anwendung behandelt und erneut versucht werden.

Ändern von Daten mit optimierter Sperrung

Wenn optimiertes Sperren aktiviert ist und die READ_COMMITTED_SNAPSHOT (RCSI)-Datenbankoption aktiviert ist und die standardmäßige READ COMMITTED-Isolationsstufe verwendet wird, erwerben Leser keine Sperren, und Schreiber erwerben kurzfristige niedrigstufige Sperren, anstatt Sperren, die am Ende der Transaktion ablaufen.

Die Aktivierung von RCSI wird für größtmögliche Effizienz bei der optimierten Sperrung empfohlen. Bei der Verwendung strengerer Isolationsstufen wie REPEATABLE READ oder SERIALIZABLE hält die Datenbank-Engine Zeilen- und Seiten-Sperren bis zum Ende der Transaktion sowohl für Leser als auch für Schreiber, was zu einer erhöhten Blockierung und Sperrspeicher führt.

Mit aktiviertem RCSI und bei Verwendung des Standard-READ COMMITTED-Isolationslevels qualifizieren Autoren Zeilen gemäß dem Prädikat basierend auf der neuesten festgeschriebenen Version der Zeile, ohne U-Sperren zu erwerben. Abfragen warten nur dann, wenn die Zeile qualifiziert ist und eine andere aktive Schreibtransaktion für diese Zeile oder Seite vorhanden ist. Das Qualifizieren basierend auf der neuesten Commitversion und das Sperren nur der qualifizierten Zeilen verringert die Blockierung und erhöht die Parallelität.

Wenn Aktualisierungskonflikte mit RCSI und im Standard-READ COMMITTED-Isolationslevel erkannt werden, werden sie automatisch behandelt und erneut versucht, ohne Auswirkungen auf die Kundenarbeitslasten.

Wenn optimiertes Sperren aktiviert ist und die SNAPSHOT Isolationsstufe verwendet wird, ist das Verhalten bei Aktualisierungskonflikten dasselbe wie ohne optimiertes Sperren. Aktualisierungskonflikte müssen von der Anwendung behandelt und erneut versucht werden.

Note

Weitere Informationen zu Verhaltensänderungen bei Abfragen mit dem Feature "Lock after qualification" (LAQ) für eine optimierte Sperrung finden Sie unter Änderungen des Abfrageverhaltens mit optimierter Sperrung und RCSI.

Gesamtverhalten

Die folgende Tabelle fasst die Unterschiede zwischen SNAPSHOT-Isolation und READ COMMITTED-Isolation unter Verwendung der Zeilenversionsverwaltung zusammen.

Property	READ COMMITTED-Isolationsstufe unter Verwendung von Zeilenversionierung	SNAPSHOT-Isolationsstufe
Die Datenbankoption, die auf ON gesetzt werden 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 das Standard-READ COMMITTED-Isolationslevel, oder führen Sie die SET TRANSACTION ISOLATION LEVEL-Anweisung aus, um das READ COMMITTED-Isolationslevel anzugeben. Das kann nach dem Start der Transaktion durchgeführt werden.	Erfordert die Ausführung von SET TRANSACTION ISOLATION LEVEL, um die SNAPSHOT-Isolationsstufe vor Beginn der Transaktion festzulegen.
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: Wechselt von Zeilenversionen zu tatsächlichen Daten, um auszuwählende Zeilen zu aktualisieren, und verwendet Aktualisierungssperren 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 zuletzt festgeschriebenen Version ausgewählt, ohne dass Sperren erworben 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 Aktualisierungskonflikte erkannt werden, werden sie automatisch behandelt und erneut versucht.	Integrierte Unterstützung. Kann nicht deaktiviert werden.

Ressourcenverwendung bei der Zeilenversionsverwaltung

Der Framework für Zeilenversionsverwaltung unterstützt die folgenden Funktionen der Datenbank-Engine:

• Triggers
• Mehrere aktive Ergebnissätze (MARS)
• Online-Indizierung

Der Rahmen für die Zeilenversionsverwaltung unterstützt auch die folgenden auf Zeilenversionsverwaltung basierenden Transaktionsisolationsstufen:

• Wenn die READ_COMMITTED_SNAPSHOT Datenbankoption auf ON gesetzt ist, bieten READ_COMMITTED Transaktionen eine Anweisungsebene-Lesekonsistenz durch Zeilenversionierung.
• Wenn die ALLOW_SNAPSHOT_ISOLATION Datenbankoption auf ON gesetzt ist, bieten SNAPSHOT Transaktionen Konsistenz auf Transaktionsebene durch Zeilenversionsverwaltung.

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 vor der Änderung wird im Versionsspeicher gespeichert, selbst wenn keine aktiven Transaktionen die zeilenversionsbasierte Isolation verwenden. Die Daten nach der Änderung enthalten einen Verweis auf die versionierten Daten im Versionsspeicher. Für große Objekte wird im Versionsspeicher nur der Teil des Objekts gespeichert, der sich geändert hat.

In tempdb verwendeter Speicherplatz

Für jede Instanz der Datenbank-Engine muss der Versionsspeicher genügend Platz haben, um die Zeilenversionen zu speichern. Der Datenbankadministrator muss sicherstellen, dass tempdb und andere Datenbanken (wenn ADR aktiviert ist) ausreichend Speicherplatz zur Unterstützung des Versionsspeichers haben. Es gibt zwei Arten von Versionsspeichern:

• Der Onlineindex-Erstellungsversionstore wird für Onlineindex-Erstellungen verwendet.
• Der allgemeine Versionsspeicher wird für alle anderen Datenänderungsoperationen verwendet.

Zeilenversionen müssen so lange gespeichert werden, wie eine aktive Transaktion sie benötigt, um auf sie zuzugreifen. Periodisch entfernt ein Hintergrundthread Zeilenversionen, die nicht mehr benötigt werden, und gibt Speicherplatz im Versionsspeicher 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.

Note

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 der Versionsspeicher sich in tempdb befindet und tempdb keinen Speicherplatz mehr hat, zwingt die Datenbank-Engine die Versionsspeicher, sich zu verkleinern. 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 Rollbacks von tempdb nicht generiert wurden und mit einem Fehler 3958 enden.

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:

• READ_COMMITTED_SNAPSHOT oder ALLOW_SNAPSHOT_ISOLATION Optionen sind auf ON gesetzt.
• 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.
• Die beschleunigte Datenbankwiederherstellung (ADR) ist aktiviert.

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

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

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. Um aktuelle Informationen zur Seitendichte für die Daten und Indizes einer Tabelle oder Ansicht anzuzeigen, können Sie sys.dm_db_index_physical_stats verwenden.

Vom permanenten Versionsspeicher (PVS) verwendeter Speicherplatz

Wenn ADR aktiviert ist, können Zeilenversionen je nach Größe der Zeile vor der Änderung in einem persistenten Versionsspeicher (PVS) auf eine der folgenden Arten gespeichert werden:

• Wenn die Größe klein ist, wird die gesamte alte Zeilenversion als Teil der geänderten Zeile gespeichert.
• Wenn die Größe intermediär ist, wird der Unterschied zwischen der alten Zeilenversion und der geänderten Zeile als Teil der geänderten Zeile gespeichert. Der Unterschied wird so konstruiert, dass das Datenbankmodul bei Bedarf die gesamte alte Zeilenversion rekonstruieren kann.
• Wenn die Größe groß ist, wird die gesamte alte Zeilenversion in einer separaten internen Tabelle gespeichert.

Die ersten beiden Methoden werden als In-Row--Versionsspeicher bezeichnet. Die letzte Methode wird Off-Row-Versionsspeicherung genannt. Wenn zeileninterne Versionen nicht mehr benötigt werden, werden sie entfernt, um Speicherplatz auf Seiten freizugeben. Entsprechend werden Seiten in der internen Tabelle, die nicht mehr benötigte Off-Row-Versionen enthalten, vom Versionsreiniger entfernt.

Das Speichern von Zeilenversionen als Teil der Zeile optimiert den Datenabruf durch Transaktionen, die Zeilenversionen lesen müssen. Wenn eine Version in der Zeile gespeichert wird, ist ein separates Lesen einer außerhalb der Zeile befindlichen PVS-Seite nicht erforderlich.

Die sys.dm_db_index_physical_stats DMV liefert die Anzahl und den Typ der Versionen, die in der Zeile und außerhalb der Zeile für eine Partition eines Indexes gespeichert sind. Die Gesamtgröße der in der Zeile gespeicherten Versionsdaten wird in der total_inrow_version_payload_size_in_bytes-Spalte angegeben.

Die Größe des Off-Row-Version-Speichers wird in der persistent_version_store_size_kb-Spalte in der sys.dm_tran_persistent_version_store_stats DMV angegeben.

In großen Objekten verwendeter Speicherplatz

Die Datenbank-Engine unterstützt mehrere Datentypen, die große Zeichenfolgen mit einer Länge von bis zu 2 Gigabyte (GB) speichern können, wie zum Beispiel: nvarchar(max), varchar(max), varbinary(max), ntext, text und image. Große Daten, die mit diesen 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 werden in einer Reihe von Seiten gespeichert, die großen Objekten in einer Tabelle gewidmet sind.

Wenn einer Datenbank neue große Werte hinzugefügt werden, werden diese mithilfe von maximal 8.040 Byte an Daten pro Fragment zugeordnet. Frühere Versionen der Datenbank-Engine speicherten bis zu 8080 Bytes an ntext, text oder image Daten pro Fragment.

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-Operationen werden minimal protokolliert, wenn das Datenbankwiederherstellungsmodell nicht auf VOLLMODUS eingestellt ist.

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

Überwachen der Zeilenversionsverwaltung und des Versionsspeichers

Zur Überwachung der Zeilenversionsverwaltung, des Versionsspeichers und der Snapshot-Isolationsprozesse hinsichtlich Leistung und Problemen stellt die Datenbank-Engine Werkzeuge in Form von Dynamic Management Views (DMVs) und Leistungsindikatoren bereit.

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. Gilt nur für den Versionsspeicher in tempdb. 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. Gilt nur für den Versionsspeicher in tempdb. 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. Gilt nur für den Versionsspeicher in tempdb. Weitere Informationen finden Sie unter sys.dm_tran_version_store_space_usage (Transact-SQL).

  Note

  Abfragen von sys.dm_tran_top_version_generators und sys.dm_tran_version_store kann teuer sein, da beide den gesamten Versionsspeicher durchsuchen, der groß sein könnte. sys.dm_tran_version_store_space_usage ist effizient und kostengünstig im Betrieb, da es nicht durch einzelne Version Store-Datensätze navigiert, sondern stattdessen den aggregierten Version Store-Speicherplatz pro Datenbank in tempdb 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).

• sys.dm_tran_persistent_version_store_stats. Gibt Statistiken für den beständigen Versionsspeicher in jeder Datenbank zurück, die bei aktivierter beschleunigter Datenbankwiederherstellung verwendet wird. Weitere Informationen finden Sie unter sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Leistungsindikatoren

Die folgenden Leistungsindikatoren überwachen den Versionsspeicher in tempdb sowie Transaktionen, die Zeilenversionsverwaltung verwenden. Die Leistungsindikatoren sind im SQLServer:Transactions 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.

  [Größe des allgemeinen Versionsspeichers] = 2 * [Generierte Versionsspeicherdaten pro Minute] * [längste Laufzeit (Minuten) der Transaktion]

  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.

• Größe des Versionsspeichers (KB). Überwacht die Größe in KB aller Versionsspeicher in tempdb. 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.

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

• Bereinigungsgeschwindigkeit der Version (KB/s). Überwacht die Bereinigungsrate der Versionen in KB pro Sekunde in allen Versionsspeichern in tempdb.

  Note

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

• Version Store Einheitanzahl. Überwacht die Anzahl der Versionsspeichereinheiten.

• Erstellung der Version Store-Einheit. Überwacht die Gesamtzahl der Versionsspeichereinheiten, die für das Speichern von Zeilenversionen erstellt wurden, seitdem die Instanz gestartet wurde.

• Version Store-Einheitstrunkierung. Ü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.

• Aktualisieren Sie das Konfliktverhältnis. Überwacht die Quote von Update-Momentaufnahmetransaktionen, die Updatekonflikte aufweisen, im Verhältnis zur Gesamtzahl der Update-Momentaufnahmetransaktionen.

• Längste Transaktionslaufzeit. Überwacht die längste Ausführungszeit in Sekunden aller Transaktionen, die die Zeilenversionsverwaltung verwenden. Dies kann verwendet werden, um festzustellen, ob eine Transaktion unerwartet lange läuft.

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

• Snapshot-Transaktionen. Überwacht die Gesamtzahl aktiver Momentaufnahmetransaktionen.

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

• Nicht-Snapshot-Version-Transaktionen. Überwacht die Gesamtzahl aktiver Nichtmomentaufnahme-Transaktionen, die Versionsdatensätze generieren.

  Note

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

Beispiel für eine auf der Zeilenversionsverwaltung basierende Isolationsstufe

Die folgenden Beispiele zeigen die Unterschiede im Verhalten zwischen SNAPSHOT Isolations-Transaktionen und READ COMMITTED Transaktionen, die Zeilenversionierung verwenden.

A. Arbeiten mit SNAPSHOT-Isolation

In diesem Beispiel liest eine Transaktion, die unter SNAPSHOT-Isolation läuft, Daten, die dann von einer anderen Transaktion geändert werden. Die SNAPSHOT Transaktion blockiert nicht den Aktualisierungsvorgang, der von der anderen Transaktion ausgeführt wird, und sie liest weiterhin Daten aus der versionierten Zeile, wobei die Datenänderung ignoriert wird. Wenn jedoch die SNAPSHOT-Transaktion versucht, die Daten zu ändern, die bereits von der anderen Transaktion geändert wurden, erzeugt die SNAPSHOT-Transaktion 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 older, 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-Isolation unter Verwendung von Zeilenversionierung

In diesem Beispiel läuft eine READ COMMITTED Transaktion mit Zeilenversionsverwaltung gleichzeitig mit einer anderen Transaktion. Die READ COMMITTED-Transaktion verhält sich anders als eine SNAPSHOT-Transaktion. Wie eine SNAPSHOT-Transaktion wird auch die READ COMMITTED-Transaktion versionierte Zeilen lesen, selbst nachdem die andere Transaktion Daten geändert hat. Im Gegensatz zu einer SNAPSHOT-Transaktion unterscheidet sich die READ COMMITTED-Transaktion:

• Liest die geänderten Daten, nachdem die andere Transaktion die Datenänderungen bestätigt hat.
• Ist in der Lage, die Daten zu aktualisieren, die von der anderen Transaktion geändert wurden, wo die SNAPSHOT-Transaktion dies nicht konnte.

Für Sitzung 1:

USE AdventureWorks2022;
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 Datenbankeinstellungen für Zeilenversionsverwaltung, indem sie die Datenbankoptionen READ_COMMITTED_SNAPSHOT und ALLOW_SNAPSHOT_ISOLATION in der ALTER DATABASE-Anweisung verwenden.

Wenn die READ_COMMITTED_SNAPSHOT Datenbankoption auf ON gesetzt ist, werden die Mechanismen zur Unterstützung der Option sofort aktiviert. Wenn Sie die READ_COMMITTED_SNAPSHOT Option festlegen, ist nur die Verbindung, die den ALTER DATABASE Befehl ausführt, in der Datenbank erlaubt. Es darf keine andere offene Verbindung in der Datenbank bestehen, bis ALTER DATABASE abgeschlossen ist. 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 die ALLOW_SNAPSHOT_ISOLATION Datenbankoption auf ON gesetzt ist, beginnt die Instanz der Datenbank-Engine nicht damit, Zeilenversionen für geänderte Daten zu erzeugen, bis alle aktiven Transaktionen, die Daten in der Datenbank geändert haben, abgeschlossen sind. Wenn aktive Änderungs-Transaktionen vorhanden sind, setzt die Datenbank-Engine den Status der Option auf PENDING_ON. Nachdem alle Änderungsaktionen abgeschlossen sind, wird der Status der Option in ON geändert. Benutzer können eine SNAPSHOT-Transaktion in der Datenbank nicht starten, bis die Option ON ist. Ähnlich verhält es sich, wenn die Datenbank durch einen PENDING_OFF Zustand geht, wenn der Datenbankadministrator die ALLOW_SNAPSHOT_ISOLATION Option auf OFF setzt.

Die folgende Transact-SQL-Anweisung aktiviert ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

Die folgende Tabelle listet und beschreibt die Zustände der ALLOW_SNAPSHOT_ISOLATION-Option. Die Verwendung von ALTER DATABASE mit der ALLOW_SNAPSHOT_ISOLATION-Option blockiert keine Benutzer, die derzeit auf die Datenbankdaten zugreifen.

Zustand der SNAPSHOT-Isolation für die aktuelle Datenbank	Description
OFF	Die Unterstützung für SNAPSHOT Isolationstransaktionen ist nicht aktiviert. Keine SNAPSHOT Isolationstransaktionen sind erlaubt.
PENDING_ON	Die Unterstützung für SNAPSHOT Isolationstransaktionen befindet sich im Übergangszustand (von OFF zu ON). Offene Transaktionen müssen abgeschlossen werden. Keine SNAPSHOT Isolationstransaktionen sind erlaubt.
ON	Die Unterstützung für SNAPSHOT-Isolationstransaktionen ist aktiviert. SNAPSHOT-Transaktionen sind erlaubt.
PENDING_OFF	Die Unterstützung für SNAPSHOT Isolationstransaktionen befindet sich im Übergangszustand (von ON zu OFF). SNAPSHOT-Transaktionen, die nach diesem Zeitpunkt gestartet wurden, können nicht auf diese Datenbank zugreifen. Bestehende SNAPSHOT-Transaktionen können weiterhin auf diese Datenbank zugreifen. Bestehende Schreibtransaktionen verwenden in dieser Datenbank weiterhin die Versionierung. Der Zustand PENDING_OFF wird erst dann OFF, wenn alle SNAPSHOT-Transaktionen abgeschlossen sind, die begonnen haben, als der Isolationszustand der Datenbank SNAPSHOTON war.

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 ON- und master-Datenbanken automatisch auf msdb gesetzt und kann nicht deaktiviert werden.

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

Verwenden von auf Zeilenversionsverwaltung basierenden Isolationsstufen

Das Framework für Zeilenversionsverwaltung ist immer aktiviert und wird von mehreren Funktionen verwendet. Neben der Bereitstellung von auf Zeilenversions basierenden Isolationsstufen wird es verwendet, um Änderungen in Triggern und mehreren aktiven Ergebnismengen (MARS)-Sitzungen zu unterstützen und Datenlesevorgänge für Online-Indexoperationen zu unterstützen.

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, dass die Zeilenversionsverwaltung verwendet, indem die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON gesetzt wird, wie im folgenden Codebeispiel gezeigt:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Wenn die Datenbank für READ_COMMITTED_SNAPSHOT aktiviert ist, verwenden alle Abfragen, die unter dem Isolationslevel READ COMMITTED ausgeführt werden, die Zeilenversionsverwaltung, was bedeutet, dass Lesevorgänge Aktualisierungsvorgänge nicht blockieren.

• SNAPSHOT-Isolation durch Festlegen der ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON wie im folgenden Codebeispiel gezeigt:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Bei der Verwendung von datenbankübergreifenden Abfragen kann eine Transaktion, die unter SNAPSHOT-Isolation läuft, auf Tabellen in der/den Datenbank(en) zugreifen, die die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON gesetzt haben. Um auf Tabellen in Datenbanken zuzugreifen, die die ALLOW_SNAPSHOT_ISOLATION Datenbankoption nicht auf ON gesetzt haben, muss das Isolationslevel geändert werden. Zum Beispiel zeigt das folgende Codebeispiel eine SELECT-Anweisung, die zwei Tabellen verbindet, während sie unter einer SNAPSHOT-Transaktion ausgeführt wird. Eine Tabelle gehört zu einer Datenbank, in der SNAPSHOT-Isolation nicht aktiviert ist. Wenn die SELECT Anweisung unter SNAPSHOT Isolation ausgeführt wird, schlägt sie fehl.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  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 denselben SELECT-Befehl, der geändert wurde, um das Transaktionsisolation-Level auf READ COMMITTED zu ändern, wenn auf eine bestimmte Tabelle zugegriffen wird. Durch diese Änderung wird die SELECT-Anweisung erfolgreich ausgeführt.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  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 nicht in tempdb, msdb oder master aktiviert werden.

• Globale temporäre Tabellen werden in tempdb gespeichert. Beim Zugriff auf globale temporäre Tabellen innerhalb einer SNAPSHOT-Transaktion muss eines der folgenden Ereignisse eintreten:

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

• SNAPSHOT-Transaktionen schlagen fehl, wenn:

  • Eine Datenbank wird schreibgeschützt, nachdem die SNAPSHOT-Transaktion gestartet wurde, aber bevor die SNAPSHOT-Transaktion auf die Datenbank zugreift.
  • Wenn auf Objekte aus mehreren Datenbanken zugegriffen wird, wurde ein Datenbankzustand so geändert, dass die Datenbankwiederherstellung nach dem Start einer SNAPSHOT-Transaktion, aber bevor die SNAPSHOT-Transaktion auf die Datenbank zugreift, erfolgt. Zum Beispiel: Die Datenbank wurde auf OFFLINE gesetzt und dann auf ONLINE, die Datenbank wurde automatisch geschlossen und erneut geöffnet, da die AUTO_CLOSE-Option auf ON gesetzt wurde, oder die Datenbank wurde getrennt und wieder angefügt.

• Verteilte Transaktionen, einschließlich Abfragen in verteilten partitionierten Datenbanken, werden unter SNAPSHOT-Isolation nicht unterstützt.

• Die Datenbank-Engine speichert nicht mehrere Versionen der 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, führt jeder gleichzeitige Verweis auf das Objekt unter SNAPSHOT-Isolation dazu, dass die SNAPSHOT-Transaktion fehlschlägt. READ COMMITTED-Transaktionen haben diese Einschränkung nicht, wenn die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON gesetzt ist.

  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.

  Note

  BULK INSERT-Vorgänge können Änderungen an den Metadaten der Zieltabelle verursachen (zum Beispiel, wenn Einschränkungsprüfungen deaktiviert werden). Wenn dies geschieht, schlagen gleichzeitige SNAPSHOT Isolationstransaktionen, die auf massenweise eingefügte Tabellen zugreifen, fehl.

Anpassen von Sperren und Zeilenversionsverwaltung

Anpassen des Timeouts für Sperren

Wenn eine Instanz der Datenbank-Engine einer Transaktion keine Sperre gewähren kann, weil eine andere Transaktion bereits eine widersprüchliche Sperre für die Ressource besitzt, wird die erste Transaktion blockiert und wartet darauf, dass die bestehende Sperre freigegeben wird. Standardmäßig gibt es keine Zeitüberschreitungsperiode für Sperrwarten, daher könnte eine Transaktion potenziell unbegrenzt blockiert werden.

Note

Verwenden Sie die dynamische Verwaltungsansicht sys.dm_os_waiting_tasks, um festzustellen, ob eine Aufgabe blockiert wird und was sie blockiert. 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 als die LOCK_TIMEOUT Einstellung gewartet hat, wird die blockierte Anweisung automatisch abgebrochen, und die Fehlermeldung 1222 (Lock request time-out period exceeded) wird zurückgegeben. Jede Transaktion, die die Anweisung enthält, wird jedoch nicht zurückgesetzt. Die Anwendung muss daher über einen Fehlerhandler verfügen, der die Fehlermeldung 1222 identifizieren kann. Wenn eine Anwendung den Fehler nicht abfängt, kann die Anwendung fortfahren, ohne zu wissen, dass eine einzelne Anweisung innerhalb einer Transaktion abgebrochen wurde, aber die Transaktion bleibt aktiv. Fehler können auftreten, weil spätere Anweisungen in der Transaktion von der Anweisung abhängen könnten, die nie ausgeführt wurde.

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.

Important

Anwendungen, die explizite Transaktionen verwenden und erfordern, dass die Transaktion bei Erhalt des Fehlers 1222 beendet wird, müssen die Transaktion als Teil der Fehlerbehandlung explizit zurücksetzen. Ohne dies können andere Anweisungen unbeabsichtigt in derselben Sitzung ausgeführt werden, während die Transaktion aktiv bleibt, was zu einem unkontrollierten Wachstum des Transaktionsprotokolls und Datenverlust führen kann, wenn die Transaktion später zurückgesetzt wird.

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

SELECT @@LOCK_TIMEOUT;
GO

Anpassen der Transaktionsisolationsstufe

READ COMMITTED ist die Standard-Isolationsebene für die Datenbank-Engine. 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 System.Data.SqlClient-Namensraum 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 die Isolationsstufe angegeben ist, arbeitet das Sperrverhalten für alle Abfragen und Datenmanipulationssprachen (DML)-Anweisungen in der Sitzung auf 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;

COMMIT;
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.

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

Das Resultset sieht wie folgt aus.

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.

Sperren von Hinweisen

Sperrhints können für einzelne Tabellenreferenzen in den Anweisungen SELECT, INSERT, UPDATE, DELETE und MERGE angegeben werden. Die Hinweise geben an, welche Art von Sperren oder Zeilenversionsverwaltung die Instanz der 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.

Note

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).

Note

Es wird empfohlen, dass Sperrhinweise auf Tabellenebene zur Änderung des Standardsperrverhaltens nur dann verwendet werden, wenn dies notwendig ist. Das Erzwingen einer Sperrstufe kann sich nachteilig auf die Parallelität auswirken.

Die Datenbank-Engine muss möglicherweise Sperren erwerben, wenn sie Metadaten liest, selbst wenn eine Anweisung mit einem Sperrhinweis verarbeitet wird, der Anfragen für gemeinsame Sperren beim Lesen von Daten verhindert. Ein Beispiel: Eine SELECT-Anweisung, die unter dem READ UNCOMMITTED-Isolationslevel ausgeführt wird oder den NOLOCK-Hinweis verwendet, erwirbt beim Lesen von Daten keine gemeinsamen Sperren, könnte jedoch manchmal Sperren anfordern, wenn eine Systemkatalogansicht gelesen wird. Das bedeutet, dass es möglich ist, dass eine solche SELECT Anweisung blockiert wird, wenn eine gleichzeitige Transaktion die Metadaten der Tabelle ändert.

Wie im folgenden Beispiel gezeigt, werden bei Festlegung des Transaktionsisolationsniveaus auf SERIALIZABLE und Verwendung des Tabellensperrhinweises NOLOCK mit der Anweisung SELECT die normalerweise zur Aufrechterhaltung von SERIALIZABLE-Transaktionen verwendeten Schlüsselbereichssperren nicht erworben.

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

Das einzige erworbene Sperre, die auf HumanResources.Employee verweist, ist eine Schema-Stabilitäts-Sperre (Sch-S). In diesem Fall kann die Serialisierbarkeit nicht mehr garantiert werden.

Die LOCK_ESCALATION-Option von ALTER TABLE vermeidet Tabellensperren während der Sperreneskalation und ermöglicht HoBT (Partition)-Sperren auf partitionierten Tabellen. Diese Option ist kein Sperrhinweis und kann verwendet werden, um die Sperreskalation zu reduzieren. Weitere Informationen finden Sie unter ALTER TABLE (Transact-SQL).

Anpassen der Sperren für einen Index

Die Datenbank-Engine verwendet eine dynamische Sperrstrategie, die in den meisten Fällen automatisch die beste Sperrgranularität für Abfragen auswählt. Wir empfehlen, die standardmäßigen Sperrlevel nicht zu überschreiben, es sei denn, die Zugriffsmodelle für Tabellen oder Indizes sind gut verstanden und konsistent, und es gibt ein Ressourcenengpassproblem zu lösen. 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 einem gemeinsamen (S) Sperre auf die Tabelle zu, und das wöchentliche Batch-Update greift mit einem exklusiven (X) Sperre 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.

Das Deaktivieren der Seiten- und Zeilensperre könnte akzeptabel sein oder auch nicht, da das wöchentliche Batchupdate verhindert, dass die gleichzeitigen Leser während der Ausführung des Updates auf die Tabelle zugreifen können. 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 eine große Anzahl von Aktualisierungen hat, kann es die beste Methode sein, eine exklusive Sperre auf der Tabelle zu erhalten, um sicherzustellen, dass der Batchauftrag effizient ausgeführt wird.

In einigen Workloads kann eine Art von Deadlock auftreten, wenn zwei gleichzeitige Operationen Zeilensperren auf derselben Tabelle erwerben und sich dann gegenseitig blockieren, weil sie 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. 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. Seitenebene-Sperren, Zeilenebene-Sperren oder sowohl Seitenebene- als auch Zeilenebene-Sperren können für einen bestimmten Index ausgeschlossen werden.

Unzulässige 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

Transaktionen verschachteln

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.

Das folgende Beispiel zeigt die Verwendung von verschachtelten Transaktionen. Wenn TransProc aufgerufen wird, während eine Transaktion aktiv ist, wird das Ergebnis der verschachtelten Transaktion in TransProc durch die äußere Transaktion gesteuert, und ihre INSERT-Anweisungen werden basierend auf dem Commit oder Rollback der äußeren Transaktion festgeschrieben oder zurückgesetzt. Wenn TransProc von einem Prozess ausgeführt wird, der keine ausstehende Transaktion hat, führt das COMMIT TRANSACTION am Ende des Verfahrens 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 roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Das Festschreiben von inneren Transaktionen wird von der Datenbank-Engine ignoriert, wenn eine äußere Transaktion aktiv ist. Die Transaktion wird entweder festgeschrieben oder zurückgesetzt, basierend auf dem Festschreiben oder Zurücksetzen am Ende der äußersten Transaktion. 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 erlaubt, dass der transaction_name-Parameter einer ROLLBACK TRANSACTION-Anweisung auf die innere Transaktion in einem Satz benannter, verschachtelter Transaktionen verweist. transaction_name kann sich nur auf den Transaktionsnamen der äußersten Transaktion beziehen. Wenn eine ROLLBACK TRANSACTION transaction_name-Anweisung mit dem Namen der äußeren Transaktion auf irgendeiner Ebene eines Satzes verschachtelter Transaktionen ausgeführt wird, werden alle verschachtelten Transaktionen zurückgesetzt. Wenn eine ROLLBACK WORK- oder ROLLBACK TRANSACTION-Anweisung ohne transaction_name-Parameter auf irgendeiner Ebene eines Satzes verschachtelter Transaktionen ausgeführt wird, werden alle verschachtelten Transaktionen, einschließlich der äußersten Transaktion, zurückgesetzt.

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. Um festzustellen, ob Sie sich bereits in einer Transaktion befinden, SELECT @@TRANCOUNT, um zu prüfen, ob es 1 oder mehr ist. 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.

Note

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.

• Bindetokens können in einer Tabelle in einer Instanz der Datenbank-Engine gespeichert werden, die von Prozessen gelesen werden kann, die sich mit der ersten Sitzung verbinden möchten.

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 Ergebnisse von der Instanz ausstehen, kann keine andere Sitzung, die an dasselbe Token gebunden ist, auf die Instanz zugreifen, bis die aktuelle Sitzung die Verarbeitung abgeschlossen hat oder die aktuelle Anweisung 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. Die Verwendung von SET TRANSACTION ISOLATION LEVEL, um die Isolationseinstellung einer Sitzung zu ändern, wirkt sich nicht auf die Einstellung einer anderen Sitzung aus, die an dasselbe Token gebunden ist.

Typen von gebundenen Sitzungen

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

• Lokale gebundene Sitzung Ermöglicht es gebundenen Sitzungen, den Transaktionsraum einer einzelnen Transaktion in einer einzigen Instanz der Datenbank-Engine zu teilen.

• Verteilte gebundene Sitzung Ermöglicht es gebundenen Sitzungen, dieselbe Transaktion über zwei oder mehr Instanzen hinweg zu teilen, bis die gesamte Transaktion entweder durch den Microsoft Distributed Transaction Coordinator (MS DTC) bestätigt oder zurückgesetzt 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 Datenbank-Engine sind gespeicherte Prozeduren, die mit CLR geschrieben wurden, sicherer, skalierbarer und stabiler als erweiterte gespeicherte Prozeduren. CLR-gespeicherte Prozeduren verwenden das SqlContext-Objekt, um den Kontext der aufrufenden Sitzung zu verbinden, nicht sp_bindsession.

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 möglicherweise kein Problem bei einer geringen Anzahl von Benutzern, aber sie sind äußerst problematisch in einem System mit Tausenden von Benutzern. Ab SQL Server 2014 (12.x) unterstützt die Datenbank-Engine verzögerte dauerhafte Transaktionen. Verzögerte dauerhafte Transaktionen können die Skalierbarkeit und Leistung verbessern, garantieren jedoch keine Dauerhaftigkeit. Weitere Informationen finden Sie im Thema Steuern der Transaktionsdauerhaftigkeit.

Coderichtlinien

Dies sind die Richtlinien für das effiziente Codieren von Transaktionen:

• 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 so programmiert werden, dass sie das READ COMMITTED Transaktionsisolationsebene verwenden. Wenige Transaktionen erfordern die SERIALIZABLE Transaktionsisolationsebene.

• Intelligente Nutzung von Optionen für optimistische Nebenläufigkeit. 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 nach Möglichkeit pessimistische Sperrhints wie HOLDLOCK. Hinweise wie HOLDLOCK oder SERIALIZABLE Isolationsstufe können dazu führen, dass Prozesse selbst bei gemeinsamen Sperren warten müssen und die Parallelität verringern.

• Vermeiden Sie nach Möglichkeit die Verwendung von impliziten Transaktionen. Implizite Transaktionen können aufgrund ihrer Natur unvorhersehbares Verhalten einführen. Siehe Implizite Transaktionen und Probleme mit der Parallelität.

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. Dieser Prozess ermöglicht es der Datenbank-Engine, den Autocommit-Modus zu verwenden, während die Anwendung Daten durchsucht und Eingaben vom Benutzer erhält.

Darüber hinaus, wenn die SNAPSHOT-Isolationsstufe aktiviert ist, wird eine neue Transaktion zwar keine Sperren halten, aber eine lang andauernde Transaktion wird verhindern, dass die alten Versionen aus dem Versionsspeicher entfernt werden.

Verwalten lang andauernder Transaktionen

Eine lang laufende Transaktion ist eine aktive Transaktion, die nicht rechtzeitig bestätigt oder zurückgesetzt 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 eine aktive Transaktion viele nicht abgeschlossene Änderungen vorgenommen hat, kann die Wiederherstellungsphase des anschließenden Neustarts viel länger dauern als die durch die recovery interval Serverkonfigurationsoption oder die ALTER DATABASE ... SET TARGET_RECOVERY_TIME Option angegebene Zeit. Diese Optionen steuern aktive und indirekte Prüfpunktsetzungen. 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 Schreibvorgänge durchfü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.

Important

In Azure SQL-Datenbank werden inaktive Transaktionen (Transaktionen, die sechs Stunden lang nicht 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. Für eine lang andauernde Transaktion sind besonders die Spalten von Interesse, die die Zeit des ersten Logbuchdatensatzes (database_transaction_begin_time), den aktuellen Zustand der Transaktion (database_transaction_state) und die Logbuchsequenznummer (LSN) des Begin-Eintrags im Transaktionsprotokoll (database_transaction_begin_lsn) enthalten.

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

• DBCC OPENTRAN

  Diese Anweisung ermöglicht es Ihnen, die Benutzer-ID des Eigentümers der Transaktion zu identifizieren, sodass Sie potenziell die Quelle der Transaktion für die entsprechende Beendigung (Commit oder Rollback) nachverfolgen können. Weitere Informationen finden Sie unter DBCC OPENTRAN (Transact-SQL).

Beenden einer Transaktion

Um eine Transaktion in einer bestimmten Sitzung zu beenden, verwenden Sie die KILL-Anweisung. 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.

• Weitere Informationen zu Deadlocks, einschließlich Überwachung, Diagnose und Beispielen, finden Sie im Leitfaden zu Deadlocks.
• Weitere Informationen zu Deadlocks speziell für Azure SQL-Datenbank finden Sie unter Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank.

Verwandte Inhalte

• Verstehen und Lösen von Blockierungsproblemen in SQL Server
• Verstehen und Lösen von Blockierungsproblemen in Azure SQL-Datenbank
• Transaktionsbezogene dynamische Verwaltungsansichten und -funktionen (Transact-SQL)
• Overhead der Zeilenversionierung
• sys.dm_tran_locks (Transact-SQL)