Handbuch zu Transaktionssperren und Zeilenversionsverwaltung

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

In jeder Datenbank führt die fehlerhafte Verwaltung von Transaktionen bei Systemen mit zahlreichen Benutzern häufig zu Konflikten und Leistungsproblemen. Mit steigender Anzahl von Benutzern, die auf die Daten zugreifen, wird der Einsatz von Anwendungen, die Transaktionen effizient verwenden, immer wichtiger. In diesem Leitfaden werden sperrungs- und Zeilenversionsmechanismen beschrieben, die das SQL Server-Datenbankmodul verwendet, um die physische Integrität jeder Transaktion sicherzustellen und Informationen darüber zu liefern, wie Anwendungen Transaktionen effizient steuern können.

Hinweis

Die optimierte Sperre ist ein in 2023 eingeführtes Datenbankmodul-Feature, das den Sperrspeicher drastisch reduziert und die Anzahl der Sperren, die für gleichzeitige Schreibvorgänge erforderlich sind. Dieser Artikel wurde aktualisiert, um das SQL Server-Datenbankmodul mit und ohne optimierte Sperrung zu beschreiben. Derzeit ist die optimierte Sperre nur in azure SQL-Datenbank verfügbar.

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

Die optimierte Sperrung hat einige Abschnitte dieses Artikels erheblich aktualisiert, darunter:

• Sperren in der Datenbank-Engine
• Löschvorgang
• Einfügevorgang
• Eskalation sperren
• Reduzieren der Sperrung und Eskalation
• Verhalten beim Ändern von Daten
• Verhalten in Zusammenfassung
• Sperren von Hinweisen

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.

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

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

Hinweis

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

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

Dauerhaftigkeit
Nach Abschluss einer voll beständigen Transaktion sind ihre Auswirkungen im System dauerhaft. Die Änderungen bleiben auch bei einem Systemfehler persistent. SQL Server 2014 (12.x) und höher ermöglichen 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 Transaktionsdauerhaftigkeit.

SQL-Programmierer sind dafür verantwortlich, Transaktionen an Punkten zu starten und zu beenden, die die logische Konsistenz der Daten erzwingen. Der Programmierer muss die Sequenz der Datenänderungen so definieren, dass die Daten hinsichtlich der Geschäftsregeln der Organisation in konsistentem Status bleiben. Der Programmierer enthält diese Änderungsanweisungen in einer einzelnen Transaktion, sodass das SQL Server-Datenbankmodul die physische Integrität der Transaktion erzwingen kann.

Es liegt in der Verantwortung eines Unternehmensdatenbanksystems, z. B. einer Instanz des SQL Server-Datenbankmoduls, um Mechanismen bereitzustellen, die die physische Integrität jeder Transaktion sicherstellen. Das SQL Server-Datenbankmodul bietet Folgendes:

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

• Protokolliervorrichtungen stellen die Beständigkeit von Transaktionen sicher. Bei vollständig dauerhaften Transaktionen wird der Protokolldatensatz vor dem Transaktionscommit auf den Datenträger geschrieben. Selbst wenn die Serverhardware, das Betriebssystem oder die Instanz des SQL Server-Datenbankmoduls selbst fehlschlägt, verwendet die Instanz die Transaktionsprotokolle beim Neustart, um unvollständige Transaktionen automatisch an den Punkt des Systemfehlers zurückzurufen. 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 Transaktionsdauerhaftigkeit.

• Funktionen der Transaktionsverwaltung, die die Unteilbarkeit und Konsistenz der Transaktionen erzwingen. Nachdem eine Transaktion gestartet wurde, muss sie erfolgreich abgeschlossen (zugesichert) werden, oder das SQL Server-Datenbankmodul macht alle seit dem Start der Transaktion vorgenommenen Datenänderungen rückgängig. Dieser Vorgang wird als Rollback einer Transaktion bezeichnet, da die Daten in den Zustand zurückversetzt werden, der vor den Änderungen gültig war.

Steuern von Transaktionen

Transaktionen werden von Anwendungen hauptsächlich durch Angeben der Zeitpunkte für Transaktionsbeginn und -ende gesteuert. Dies kann mithilfe von Transact-SQL-Anweisungen oder API-Funktionen (Database Application Programming Interface) angegeben werden. Das System muss auch in der Lage sein, Fehler richtig zu behandeln, die eine Transaktion vor deren Abschluss beenden. Weitere Informationen finden Sie unter Transaktionen, Transaktionen in ODBC und Transaktionen in SQL Server Native Client (OLEDB).

Standardmäßig werden Transaktionen auf der Verbindungsebene verwaltet. Wenn eine Transaktion für eine Verbindung gestartet wird, sind alle Transact-SQL-Anweisungen, die für diese Verbindung ausgeführt werden, Teil der Transaktion, bis die Transaktion endet. Bei einer MARS-Sitzung (Multiple Active Result Set) wird eine explizite oder implizite Transact-SQL-Transaktion jedoch zu einer batchbezogenen Transaktion, die auf Batchebene verwaltet wird. Wenn der Batch abgeschlossen ist, wird es automatisch von SQL Server zurückgesetzt, wenn die Batchtransaktion nicht zugesichert oder zurückgesetzt wird. Weitere Informationen finden Sie unter Verwenden von Multiple Active Result Sets (MARS).

Transaktionen starten

Mithilfe von API-Funktionen und Transact-SQL-Anweisungen können Sie Transaktionen in einer Instanz des SQL Server-Datenbankmoduls als explizite, autokommitieren oder implizite Transaktionen starten.

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

Sie können alle Transact-SQL-Anweisungen in einer expliziten Transaktion verwenden, mit Ausnahme der 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
• SICHERUNG
• RESTORE
• RECONFIGURE
• Gespeicherte Volltext-Systemprozeduren
• sp_dboption zum Festlegen von Datenbankoptionen oder einer Systemprozedur, die die master Datenbank innerhalb expliziter oder impliziter Transaktionen ändert.

Hinweis

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

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

Implizite Transaktionen
Wenn eine Verbindung im impliziten Transaktionsmodus ausgeführt wird, startet die Instanz des SQL Server-Datenbankmoduls automatisch eine neue Transaktion, nachdem die aktuelle Transaktion zugesichert oder zurückgesetzt 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. Legen Sie den impliziten Transaktionsmodus entweder über eine API-Funktion oder die Transact-SQL SET-IMPLICIT_TRANSACTIONS ON-Anweisung fest. Dieser Modus wird auch Autocommit OFF genannt. Weitere Informationen finden Sie unter setAutoCommit-Methode.

Nachdem der implizite Transaktionsmodus für eine Verbindung festgelegt wurde, startet die Instanz des SQL Server-Datenbankmoduls beim ersten Ausführen einer dieser Anweisungen automatisch eine Transaktion:

• ALTER TABLE

• CREATE

• DELETE

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• AUSWÄHLEN

• TRUNCATE TABLE

• UPDATE

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

• Verteilte Transaktionen
  Verteilte Transaktionen erstrecken sich auf mindestens zwei Server, die als Ressourcen-Manager bekannt sind. Die Verwaltung der Transaktionen muss zwischen den Ressourcen-Managern von einer Serverkomponente, dem Transaktions-Manager, koordiniert werden. Jede Instanz des SQL Server-Datenbankmoduls kann als Ressourcenmanager in verteilten Transaktionen arbeiten, die von Transaktionsmanagern koordiniert werden, z. B. Microsoft Distributed Transaction Coordinator (MS DTC) oder anderen Transaktionsmanagern, 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 des SQL Server-Datenbankmoduls, die zwei oder mehr Datenbanken umfasst, ist tatsächlich eine verteilte Transaktion. Die Instanz verwaltet die verteilte Transaktion jedoch intern; für den Benutzer entsteht der Eindruck, es handele sich um eine lokale Transaktion.

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

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

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

    SQL Server-Datenbankmodulanwendungen können verteilte Transaktionen entweder über Transact-SQL oder 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. Durch eine COMMIT-Anweisung wird sichergestellt, dass alle Änderungen der Transaktion zum dauerhaften Bestandteil der Datenbank werden. Durch eine COMMIT-Anweisung werden auch von der Transaktion verwendete Ressourcen, wie etwa Sperren, freigegeben.

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

Hinweis

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

Fehler während der Transaktionsverarbeitung

Wenn ein Fehler den erfolgreichen Abschluss einer Transaktion verhindert, setzt SQL Server die Transaktion automatisch zurück und gibt alle Ressourcen frei, die von der Transaktion gehalten werden. Wenn die Netzwerkverbindung des Clients mit einer Instanz des SQL Server-Datenbankmoduls unterbrochen ist, werden alle ausstehenden Transaktionen für die Verbindung zurückgesetzt, wenn das Netzwerk die Instanz des Umbruchs benachrichtigt. Wenn die Clientanwendung fehlschlägt oder der Clientcomputer heruntergeht oder neu gestartet wird, wird die Verbindung ebenfalls unterbrochen, und die Instanz des SQL Server-Datenbankmoduls setzt alle ausstehenden Verbindungen zurück, wenn das Netzwerk die Verbindung benachrichtigt. Wenn sich der Client von der Anwendung abmeldet, werden alle ausstehenden Transaktionen zurückgesetzt.

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

Für den Fall, dass Fehler auftreten, sollte in den Anwendungscode eine korrigierende Aktion (COMMIT oder ROLLBACK) aufgenommen werden. Ein effektives Tool für die Behandlung von Fehlern, einschließlich derjenigen in Transaktionen, ist das Transact-SQL-Konstrukt TRY...CATCH . Weitere Informationen zu Beispielen, die Transaktionen enthalten, finden Sie unter TRY... CATCH (Transact-SQL). Ab SQL Server 2012 (11.x) können Sie die THROW Anweisung verwenden, um eine Ausnahme auszulösen und die Ausführung in einen CATCH Block eines TRY...CATCH Konstrukts zu übertragen. Weitere Informationen finden Sie unter THROW (Transact-SQL).For more information, see THROW (Transact-SQL).

Kompilierungs- und Laufzeitfehler im Autocommit-Modus

Im Autocommit-Modus erscheint es manchmal so, als ob eine Instanz des SQL Server-Datenbankmoduls einen ganzen Batch anstelle einer SQL-Anweisung zurückgesetzt hat. Dies passiert, wenn es sich beim aufgetretenen Fehler um einen Kompilierungsfehler und nicht um einen Laufzeitfehler handelt. Ein Kompilierungsfehler verhindert, dass das SQL Server-Datenbankmodul einen Ausführungsplan erstellt, sodass nichts im Batch ausgeführt wird. 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  

Das SQL Server-Datenbankmodul verwendet die Auflösung verzögerter Namen, bei der Objektnamen erst nach der Ausführung 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 der Sperrung und Zeilenversionsverwaltung

Das SQL Server-Datenbankmodul verwendet die folgenden Mechanismen, um die Integrität von Transaktionen sicherzustellen und die Konsistenz von Datenbanken aufrechtzuerhalten, wenn mehrere Benutzer gleichzeitig auf Daten zugreifen:

• Sperrung

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

• Zeilenversionsverwaltung

  Wenn eine Zeilenversionsbasierte Isolationsebene aktiviert ist, verwaltet das SQL Server-Datenbankmodul Versionen jeder Zeile, die geändert wird. Anwendungen können angeben, dass eine Transaktion die Zeilenversionen verwendet, um die Daten so anzuzeigen, wie sie zum Zeitpunkt des Transaktions- oder Abfragestarts vorgelegen haben, statt alle Lesevorgänge durch Sperren zu schützen. Durch Verwendung der Zeilenversionsverwaltung wird die Wahrscheinlichkeit, dass ein Lesevorgang zur Blockierung anderer Transaktionen führt, weitgehend reduziert.

Sperren und Zeilenversionsverwaltung verhindern, dass Benutzer Daten lesen, für die noch kein Commit ausgeführt wurde, und verhindern, dass viele Benutzer gleichzeitig versuchen, dieselben Daten zu ändern. Ohne Sperren 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. Hinweise auf Tabellenebene können für einzelne Transact-SQL-Anweisungen angegeben werden, um das Verhalten weiter an die Anforderungen der Anwendung anzupassen.

Verwalten des gleichzeitigen Datenzugriffs

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

Parallelitätseffekte

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

• Verlorene Updates

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

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

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

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

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

• Inkonsistente Analyse (nicht wiederholbarer Lesevorgang)

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

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

• Phantomlesezugriffe

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

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

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

• Durch Zeilenupdates verursachte fehlende und doppelte Lesezugriffe

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

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

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

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

Parallelitätstypen

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

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

• Steuerung durch eingeschränkte Parallelität

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

• Steuerung für optimistische Parallelität

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

SQL Server unterstützt einen Bereich von Parallelitätssteuerelementen. 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 oder über die Eigenschaften und Attribute von APIs (Database Application Programming Interfaces) wie ADO, ADO.NET, OLE DB und ODBC definiert werden.

Isolationsebenen im SQL Server-Datenbankmodul

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

Durch die Transaktionsisolationsstufen wird Folgendes gesteuert:

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

Wichtig

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

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

Isolationsstufen der Datenbank-Engine

Der ISO-Standard definiert die folgenden Isolationsstufen, die alle vom SQL Server-Datenbankmodul unterstützt werden:

Isolationsstufe	Definition
Read uncommitted	Die niedrigste Isolationsstufe, bei der Transaktionen nur soweit isoliert werden, dass sichergestellt ist, dass keine physisch beschädigten Daten gelesen werden. Auf dieser Stufe sind Dirty Reads zulässig, d. h., eine Transaktion kann Änderungen verfolgen, die von anderen Transaktionen vorgenommen wurden und für die noch kein Commit ausgeführt wurde.
Read committed	Ermöglicht einer Transaktion das Lesen von Daten, die zuvor von einer anderen Transaktion gelesen (nicht geändert) wurden, ohne warten zu müssen, bis die erste Transaktion abgeschlossen ist. Das SQL Server-Datenbankmodul behält Schreibsperren (abgerufen bei ausgewählten Daten) bis zum Ende der Transaktion bei, aber Lesesperren werden freigegeben, sobald der SELECT-Vorgang ausgeführt wird. Dies ist die Standardebene des SQL Server-Datenbankmoduls.
Repeatable read	Das SQL Server-Datenbankmodul behält Lese- und Schreibsperren bei, die für ausgewählte Daten bis zum Ende der Transaktion abgerufen werden. Da Bereichssperren jedoch nicht verwaltet werden, können Phantomlesevorgänge auftreten.
Serializable	Die höchste Stufe, auf der Transaktionen vollständig voneinander isoliert sind. Das SQL Server-Datenbankmodul behält Lese- und Schreibsperren bei ausgewählten Daten bei, die am Ende der Transaktion freigegeben werden sollen. Bereichssperren werden angefordert, wenn ein SELECT-Vorgang eine WHERE-Bereichsklausel verwendet. Dies dient vor allem der Vermeidung von Phantomlesevorgängen. Hinweis: DDL-Vorgänge und -Transaktionen in replizierten Tabellen schlagen möglicherweise fehl, wenn die Isolationsstufe SERIALIZABLE angefordert wird. Das liegt daran, dass Replikationsabfragen Hinweise verwenden, die möglicherweise mit der serialisierbaren Isolationsstufe nicht kompatibel sind.

SQL Server unterstützt auch zwei zusätzliche Transaktionsisolationsstufen, die die Zeilenversionsverwaltung verwenden. Einer ist eine Implementierung der READ COMMITTED-Isolation, und eine ist eine Transaktionsisolationsstufe, Momentaufnahme.

Isolationsstufe der Zeilenversionsverwaltung	Definition
READ COMMITTED-Momentaufnahme (RCSI)	Wenn die Option READ_COMMITTED_SNAPSHOT Datenbank aktiviert ist, verwendet READ COMMIT-Isolation Zeilenversionsverwaltung, um die Lesbarkeit auf Anweisungsebene bereitzustellen. Lesevorgänge erfordern dabei lediglich SCH-S-Sperren auf der Tabellenebene und keine Seiten- oder Zeilensperren. Das SQL Server-Datenbankmodul verwendet die Zeilenversionsverwaltung, um jede Anweisung mit einer transaktionskonsensierten Momentaufnahme der Daten darzustellen, wie sie zu Beginn der Anweisung vorhanden ist. 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 DEAKTIVIERT ist, was die Standardeinstellung ist, verwendet READ COMMIT-Isolation freigegebene Sperren, um zu verhindern, dass andere Transaktionen Zeilen ändern, während die aktuelle Transaktion einen Lesevorgang ausführt. Durch freigegebene Sperren wird außerdem verhindert, dass die Anweisung Zeilen, die von anderen Transaktionen geändert werden, erst nach Abschluss der anderen Transaktion lesen kann. Beide Implementierungen entsprechen der ISO-Definition der READ COMMIT-Isolation.
Momentaufnahme	Die Momentaufnahmeisolationsstufe verwendet die Zeilenversionsverwaltung, um die Lesekonsistenz auf der Transaktionsebene zu gewährleisten. Dabei werden durch Lesevorgänge keine Seiten- oder Zeilensperren eingerichtet, sondern lediglich SCH-S-Tabellensperren. Beim Lesen von Zeilen, die durch eine andere Transaktion geändert wurden, wird die Version der Zeile abgerufen, die zum Startzeitpunkt der Transaktion vorhanden war. Sie können die Momentaufnahmeisolation für eine Datenbank nur verwenden, wenn die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption auf ON festgelegt wurde. Standardmäßig ist diese Option für Benutzerdatenbanken auf OFF gesetzt. Hinweis: SQL Server unterstützt keine Versionsverwaltung 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 nach einer BEGIN TRANSACTION-Anweisung unter Momentaufnahmeisolation nicht zulässig: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME sowie alle CLR (Common Language Runtime)-DDL-Anweisungen. Diese Anweisungen sind zulässig, wenn Sie die Momentaufnahmeisolation in impliziten Transaktionen verwenden. Eine implizite Transaktion ist definitionsgemäß eine einzelne Anweisung, mit der die Semantik der Momentaufnahmeisolation auch in DDL-Anweisungen erzwungen werden kann. Verstöße gegen dieses Prinzip können zu Fehler 3961 führen: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation..

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

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

Weitere Informationen zu den spezifischen Sperr- oder Zeilenversionstypen, die von jeder Transaktionsisolationsstufe gesteuert werden, finden Sie unter SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about the specific types of locking or row versionsing controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about the specific types of locking or row versionsing controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

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, oder adXactRepeatableReadadXactReadSerializable.

ADO.NET
ADO.NET Anwendungen, die den System.Data.SqlClient verwalteten Namespace verwenden, können die SqlConnection.BeginTransaction Methode aufrufen und die IsolationLevel Option auf Unspecified, , ReadUncommittedChaos, ReadCommitted, RepeatableRead, , Serializableoder Snapshotfestlegen.

OLE DB
Beim Starten einer Transaktion verwenden Anwendungen, die ole DB-Aufruf ITransactionLocal::StartTransaction verwenden, die isoLevel auf ISOLATIONLEVEL_READUNCOMMITTED", ISOLATIONLEVEL_READCOMMITTED" , " ISOLATIONLEVEL_REPEATABLEREADISOLATIONLEVEL_SNAPSHOToder ISOLATIONLEVEL_SERIALIZABLE" festgelegt sind.

Wenn Sie die Transaktionsisolationsstufe im Autocommit-Modus angeben, können OLE DB-Anwendungen die Eigenschaft auf , , DBPROPVAL_TI_BROWSEDBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_SERIALIZABLEDBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_REPEATABLEREADDBPROPVAL_TI_CURSORSTABILITYoder DBPROPVAL_TI_ISOLATED.DBPROPVAL_TI_SNAPSHOTDBPROPVAL_TI_CHAOSDBPROP_SESS_AUTOCOMMITISOLEVELSDBPROPSET_SESSION

ODBC
ODBC-Anwendungen rufen SQLSetConnectAttr auf AttributeValuePtrSQL_ATTR_TXN_ISOLATION und legen sie auf SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, , , SQL_TXN_REPEATABLE_READoder .SQL_TXN_SERIALIZABLE

Bei Momentaufnahmetransaktionen rufen SQLSetConnectAttr Anwendungen mit Attributsatz auf SQL_COPT_SS_TXN_ISOLATION und ValuePtr legen sie auf SQL_TXN_SS_SNAPSHOTfest. Eine Momentaufnahmetransaktion kann entweder mithilfe SQL_COPT_SS_TXN_ISOLATION von oder SQL_ATTR_TXN_ISOLATION.

Sperren in der Datenbank-Engine

Das Sperren ist ein Mechanismus, der vom SQL Server-Datenbankmodul verwendet wird, um den Zugriff von mehreren Benutzern auf dasselbe Datenstück gleichzeitig 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 besitzen verschiedene Modi, z. B. freigegeben oder exklusiv. Der Sperrmodus definiert den Grad der Abhängigkeit, den die Transaktion für die Daten eingerichtet hat. Keiner Transaktion wird eine Sperre gewährt, die einen Konflikt mit dem Modus einer Sperre verursachen würde, die einer anderen Transaktion bereits für die betreffenden Daten erteilt wurde. Wenn eine Transaktion einen Sperrmodus anfordert, der mit einer Sperre in Konflikt steht, die bereits für dieselben Daten gewährt wurde, hält die Instanz des SQL Server-Datenbankmoduls die anfordernde Transaktion an, bis die erste Sperre freigegeben wurde.

Wenn eine Transaktion eine Datenmenge ändert, enthält sie bestimmte Sperren, die die Änderung bis zum Ende der Transaktion schützen. Wie lange eine Transaktion die zum Schutz von Lesevorgängen erworbenen Sperren enthält, hängt von der Einstellung der Transaktionsisolationsstufe und davon ab, ob die optimierte Sperre aktiviert ist.

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

• Wenn die optimierte Sperre aktiviert ist, wird für die Dauer der Transaktion nur eine Transaktions-ID (TID) gesperrt. Unter der Standardisolationsstufe enthalten Transaktionen keine Zeilen- und Seitensperren, die für Schreibvorgänge bis zum Ende der Transaktion erforderlich sind. Dadurch wird der erforderliche Sperrspeicher reduziert und die Notwendigkeit einer Sperreskalation reduziert. Wenn eine optimierte Sperre aktiviert ist, wertet die Optimierung nach der Qualifizierung (LAQ) Prädikate einer Abfrage für die neueste zugesicherte Version der Zeile aus, ohne eine Sperre zu erhalten und die Parallelität zu verbessern.

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

Anwendungen fordern in der Regel Sperren nicht direkt an. Sperren werden intern von einem Teil des SQL Server-Datenbankmoduls verwaltet, der als Sperr-Manager bezeichnet wird. Wenn eine Instanz des SQL Server-Datenbankmoduls eine Transact-SQL-Anweisung verarbeitet, bestimmt der SQL Server-Datenbankmodul-Abfrageprozessor, 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

Das SQL Server-Datenbankmodul verfügt über eine mehrgranulare Sperre, mit der verschiedene Arten von Ressourcen durch eine Transaktion gesperrt werden können. Um die Kosten für die Sperre zu minimieren, sperrt das SQL Server-Datenbankmodul Ressourcen automatisch auf einer Ebene, die dem Vorgang entspricht. 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.

Das SQL Server-Datenbankmodul muss häufig Sperren auf mehreren Granularitätsebenen abrufen, 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 Index vollständig zu schützen, muss eine Instanz des SQL Server-Datenbankmoduls möglicherweise Freigabesperren für Zeilen und Absichtsfreigabesperren auf den Seiten und der Tabelle abrufen.

In der folgenden Tabelle sind die Ressourcen aufgeführt, die vom SQL Server-Datenbankmodul gesperrt werden können.

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

Hinweis

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

Sperrmodi

Das SQL Server-Datenbankmodul sperrt Ressourcen mithilfe verschiedener Sperrmodi, die bestimmen, wie auf die Ressourcen durch gleichzeitige Transaktionen zugegriffen werden kann.

In der folgenden Tabelle sind die Ressourcensperrmodi aufgeführt, die vom SQL Server-Datenbankmodul verwendet werden.

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

Freigegebene Sperren

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

Sperren aktualisieren

Das Datenbankmodul platziert Aktualisierungssperren (U), während es sich darauf vorbereitet, ein Update auszuführen. U-Sperren sind mit S-Sperren kompatibel, aber nur eine Transaktion kann eine U-Sperre gleichzeitig für eine bestimmte Ressource enthalten. Dies ist der Schlüssel – viele gleichzeitige Transaktionen können S-Sperren enthalten, aber nur eine Transaktion kann eine U-Sperre für eine Ressource enthalten. Updatesperren (U) werden schließlich auf exklusive (X)-Sperren aktualisiert, um eine Zeile zu aktualisieren.

Aktualisierungssperren (U) können auch von Abfragen ausgeführt werden, die kein UPDATE ausführen, wenn der UPDLOCK-Tabellenhinweis in der Abfrage angegeben wird. Es ist üblich, dass Anwendungen ein Muster zum Auswählen einer Zeile und dann zum Aktualisieren des Zeilenmusters verwenden, bei dem lese- und schreibvorgänge innerhalb der Transaktion explizit getrennt sind. Wenn in diesem Fall die Isolationsebene wiederholbar gelesen oder serialisierbar ist, können gleichzeitige Updates wahrscheinlich deadlocken. Stattdessen könnten Anwendungen einem Muster "Eine Zeile mit UPDLOCK-Hinweis auswählen und dann das Zeilenmuster aktualisieren" folgen.

• In einer wiederholbaren lese- oder serialisierbaren Transaktion liest die Transaktion Daten, erhält eine freigegebene (S)-Sperre für die Ressource und ändert dann die Daten, die eine Sperrkonvertierung in eine exklusive (X)-Sperre erfordern. Wenn zwei Transaktionen freigegebene (S)-Sperren für eine Ressource erwerben und dann versuchen, Daten gleichzeitig zu aktualisieren, versucht eine Transaktion die Sperrkonvertierung in eine exklusive (X)-Sperre. Die Konvertierung der freigabe-zu-exklusiven Sperre muss warten, da die exklusive Sperre für eine Transaktion nicht mit der freigegebenen (S)-Sperre der anderen Transaktion kompatibel ist; Es tritt eine Sperre auf. Die zweite Transaktion versucht nun ebenfalls, eine exklusive Sperre (X) für das Update einzurichten. Da beide Transaktionen in exklusive (X)-Sperren konvertieren, und sie warten jeweils, bis die andere Transaktion ihre freigegebene (S)-Sperre freigibt, tritt ein Deadlock auf.

• In der standardmäßigen Isolationsstufe für lesesicherte Lesevorgänge sind S-Sperren kurze Dauer, sobald sie verwendet werden. Die kurzen Sperrungen für die Dauer sind unwahrscheinlich, dass sie zu Deadlocks führen.

Exklusive Sperren

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

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

Absichtssperren

Das SQL Server-Datenbankmodul verwendet Absichtssperren, um das Platzieren einer freigegebenen (S)-Sperre oder einer exklusiven (x)-Sperre auf einer Ressource zu schützen, die niedriger in der Sperrhierarchie ist. Absichtssperren werden als "Absichtssperren" bezeichnet, da sie vor einer Sperre auf der unteren Ebene abgerufen werden und daher signalisieren, dass Sperren auf einer niedrigeren Ebene platziert werden.

Beabsichtigte Sperren werden aus zwei Gründen verwendet:

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

Eine beabsichtigte freigegebene Sperre auf Tabellenebene wird also beispielsweise angefordert, bevor freigegebene Sperren (S) für Seiten oder Zeilen in dieser Tabelle angefordert werden. Durch Festlegen einer beabsichtigten Sperre auf Tabellenebene wird verhindert, dass andere Transaktionen anschließend eine exklusive Sperre (x) für die Tabelle einrichten können, die diese Seite enthält. Absichtssperren verbessern die Leistung, da das SQL Server-Datenbankmodul Absichtssperren nur auf Tabellenebene untersucht, um festzustellen, ob eine Transaktion eine Sperre für diese Tabelle sicher abrufen kann. Dadurch ist es nicht mehr erforderlich, jede Zeilen- oder Seitensperre in der Tabelle zu überprüfen, um zu ermitteln, ob eine Transaktion die gesamte Tabelle sperren kann.

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

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

Schemasperren

Das SQL Server-Datenbankmodul verwendet Schemaänderungssperren (Sch-M) während eines DDL-Vorgangs (Table Data Definition Language), z. B. das Hinzufügen einer Spalte oder das Ablegen einer Tabelle. Während die Sch-M-Sperre besteht, werden gleichzeitige Zugriffe auf die Tabelle verhindert. Dies bedeutet, dass die Sch-M-Sperre alle externen Vorgänge blockiert, bis die Sperre aufgehoben wird.

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

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

Massenaktualisierungssperren

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

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

Tipp

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

Schlüsselbereichssperren

Schlüsselbereichssperren schützen einen Bereich von Zeilen, die implizit in einem Datensatzsatz enthalten sind, der von einer Transact-SQL-Anweisung gelesen wird, während die serialisierbare Transaktionsisolationsstufe 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.

Kompatibilität von Sperren

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

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

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

Hinweis

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

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

Schlüsselbereichssperren

Schlüsselbereichssperren schützen einen Bereich von Zeilen, die implizit in einem Datensatzsatz enthalten sind, der von einer Transact-SQL-Anweisung gelesen wird, während die serialisierbare Transaktionsisolationsstufe verwendet wird. Für die Isolationsstufe SERIALIZABLE muss jede Abfrage, die während einer Transaktion ausgeführt wird, dieselben Zeilen erhalten, wenn sie im Rahmen der Transaktion ausgeführt wird. Durch eine Schlüsselbereichssperre wird diese Anforderung geschützt, indem verhindert wird, dass von anderen Transaktionen neue Zeilen eingefügt werden, deren Schlüssel dem Schlüsselbereich zugehörig sind, die von der serialisierbaren Transaktion gelesen werden.

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

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

Tastenbereich-Sperrmodi

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

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

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

Hinweis

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

Schlüsselbereichssperrmodi haben eine Kompatibilitätsmatrix, die zeigt, welche Sperren mit anderen Sperren, die für überlappende Schlüssel und Bereiche eingerichtet wurden, kompatibel sind.

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

Konvertierungssperren

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

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

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

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

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

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

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

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

Beispiele

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

Bereichsscanabfrage

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

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

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

Hinweis

Die Anzahl der aufrechterhaltenen Sperren vom Typ „RangeS-S“ entspricht n+1. Hierbei ist n die Anzahl der Zeilen, die der Abfrage entsprechen.

Singleton-Abruf nicht vorhandener Daten

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

SELECT name  
FROM mytable  
WHERE name = 'Bill';  

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

Löschvorgang ohne optimierte Sperrung

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

DELETE mytable  
WHERE name = 'Bob';  

Eine exklusive Sperre (X) wird für den Indexeintrag platziert, der dem Namen Bob entspricht. Andere Transaktionen können Werte vor oder nach dem gelöschten Wert Bob einfügen oder löschen. Eine Transaktion, die versucht, den Wert Bob zu lesen, einzufügen oder zu löschen, wird jedoch so lange blockiert, bis für die löschende Transaktion entweder ein Commit oder ein Rollback ausgeführt wird. (Die READ_COMMITTED_SNAPSHOT Datenbankoption und die SNAPSHOT-Isolationsebene ermöglichen auch Lesevorgänge aus einer Zeilenversion des zuvor zugesicherten Zustands.)

Das Löschen des Bereichs kann mithilfe von drei grundlegenden Sperrmodi ausgeführt werden: Zeilen-, Seiten- oder Tabellensperre. Die Verwendung der Zeilen-, Seiten- oder Tabellensperren wird vom Abfrageoptimierer festgelegt oder kann vom Benutzer über Abfrageoptimierungshinweise, wie ROWLOCK, PAGLOCK oder TABLOCK, angegeben werden. Wenn PAGLOCK oder TABLOCK verwendet wird, führt das SQL Server-Datenbankmodul sofort eine Indexseite aus, wenn alle Zeilen von dieser Seite gelöscht werden. Wenn hingegen ROWLOCK verwendet wird, werden sämtliche Zeilen lediglich als gelöscht markiert und zu einem späteren Zeitpunkt mithilfe eines Hintergrundtasks von der Indexseite entfernt.

Löschvorgang mit optimierter Sperrung

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

DELETE mytable  
WHERE name = 'Bob';  

Eine TID-Sperre wird für die Dauer der Transaktion auf allen geänderten Zeilen platziert. Für die TID der Indexeinträge, die dem Namen Bobentsprechen, wird eine Sperre abgerufen. Bei optimierter Sperre werden Seiten- und Zeilensperren weiterhin für Updates erworben, aber jede Seiten- und Zeilensperre wird freigegeben, sobald jede Zeile aktualisiert wird. Die TID-Sperre schützt die Aktualisierung der Zeilen, bis die Transaktion abgeschlossen ist. Jede Transaktion, die versucht, den Wert Bob zu lesen, einzufügen oder zu löschen, wird blockiert, bis die Löschtransaktion entweder commits oder zurückgesetzt wird. (Die READ_COMMITTED_SNAPSHOT Datenbankoption und die SNAPSHOT-Isolationsebene ermöglichen auch Lesevorgänge aus einer Zeilenversion des zuvor zugesicherten Zustands.)

Andernfalls sind die Sperrmechaniken eines Löschvorgangs identisch mit der ohne optimierte Sperrung.

Einfügevorgang ohne optimierte Sperrung

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

INSERT mytable VALUES ('Dan');  

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

Einfügevorgang mit optimierter Sperrung

Wenn ein Wert in einer Transaktion eingefügt wird, muss der Bereich, in dem der Wert liegt, nicht für die gesamte Dauer der Transaktion, die den Einfügungsvorgang ausführt, gesperrt werden. Zeilen- und Seitensperren werden nur selten abgerufen, wenn ein Onlineindex in Bearbeitung ist oder wenn in der Instanz serailizierbare Transaktionen vorhanden sind. Wenn Zeilen- und Seitensperren erworben werden, werden sie schnell freigegeben und nicht für die Dauer der Transaktion gehalten. Das Platzieren einer exklusiven TID-Sperre für den eingefügten Schlüsselwert bis zum Ende der Transaktion reicht aus, um die Serialisierung zu gewährleisten. Angenommen, folgende INSERT-Anweisung wird ausgeführt:

INSERT mytable VALUES ('Dan');  

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

Sperrenausweitung

Die Lockeskalation ist der Prozess der Umwandlung vieler Feinkornsperren in weniger grobkörnige Sperren, wodurch der Systemaufwand reduziert wird, während die Wahrscheinlichkeit der Parallelitätskonkurrenz erhöht wird.

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

Sperren der Eskalation ohne optimierte Sperrung

Wenn das SQL Server-Datenbankmodul Sperre auf niedriger Ebene abruft, werden auch Absichtssperren für die Objekte platziert, die die Objekte auf niedrigerer Ebene enthalten:

• Beim Sperren von Zeilen- oder Indexschlüsselbereichen platziert das Datenbankmodul eine Intent-Sperre auf den Seiten, die die Zeilen oder Schlüssel enthalten.
• Beim Sperren von Seiten platziert das Datenbankmodul eine Intent-Sperre auf den Objekten höherer Ebene, die die Seiten enthalten. Zusätzlich zur Intent-Sperre für das Objekt werden Absichtsseitensperren für die folgenden Objekte angefordert:
  • Seiten auf Blattebene von nicht gruppierten Indizes
  • Datenseiten von gruppierten Indizes
  • Heap-Datenseiten

Das Datenbankmodul kann sowohl Zeilen- als auch Seitensperrungen für dieselbe Anweisung ausführen, um die Anzahl der Sperren zu minimieren und die Wahrscheinlichkeit zu verringern, dass die Sperreskalation erforderlich ist. So könnte die Datenbank-Engine z. B. Seitensperren für einen nicht gruppierten Index (sofern ausreichend viele zusammenhängende Schlüssel im Indexknoten ausgewählt sind, um der Abfrage zu entsprechen) und Zeilensperren für die Daten einrichten.

Um Sperren zu eskalieren, versucht das Datenbankmodul, die Absichtssperre auf der Tabelle in die entsprechende voll gesperrte Sperre zu ändern, z. B. eine Ix-Sperre (Intent Exclusive, IX) in eine exklusive (X)-Sperre oder eine freigabefähige (INTENT Shared, IS)-Sperre in eine freigegebene (S)-Sperre). Wenn der Lock-Eskalationsversuch erfolgreich ist und die vollständige Tabellensperre abgerufen wird, werden alle Heap- oder B-Struktur-, Seiten- (PAGE) oder RID-Sperren (Row-Level) freigegeben, die von der Transaktion im Heap oder Index gespeichert werden. Wenn die vollständige Sperre nicht erreicht wird, erfolgt keine Sperrenausweitung, und die Datenbank-Engine richtet weiterhin Zeilen-, Schlüssel- oder Seitensperren ein.

Das Datenbankmodul eskaliert keine Zeilen- oder Schlüsselbereichssperrungen an Seitensperren, sondern eskaliert sie direkt an Tabellensperren. Ebenso werden Seitensperren immer zu Tabellensperren eskaliert. Das Sperren von partitionierten Tabellen kann auf die HoBT-Ebene für die zugehörige Partition statt auf die Tabellensperre ausgeweitet werden. Eine HoBT-Sperre sperrt nicht unbedingt die ausgerichteten HoBTs für die Partition.

Hinweis

HoBT-Level-Sperren erhöhen in der Regel die Parallelität, stellen jedoch das Potenzial für Deadlocks vor, wenn Transaktionen, die unterschiedliche Partitionen sperren, die jeweils ihre exklusiven Sperren auf die anderen Partitionen erweitern möchten. In seltenen Fällen kann die TABLE-Sperr granularität besser funktionieren.

Wenn ein Lock-Eskalationsversuch aufgrund von widersprüchlichen Sperren, die von gleichzeitigen Transaktionen gehalten werden, fehlschlägt, versucht das Datenbankmodul die Sperreskalation für alle zusätzlichen 1.250 Sperren, die von der Transaktion erworben wurden.

Jedes Eskalationsereignis wird in erster Linie auf der Ebene einer einzelnen Transact-SQL-Anweisung ausgeführt. Wenn das Ereignis gestartet wird, versucht das Datenbankmodul, alle Sperren im Besitz der aktuellen Transaktion in einer der Tabellen zu eskalieren, auf die von der aktiven Anweisung verwiesen wurde, vorausgesetzt, sie erfüllt die Eskalationsschwellenanforderungen. Wenn das Eskalationsereignis beginnt, bevor die Anweisung auf eine Tabelle zugegriffen hat, wird kein Versuch unternommen, die Sperren in dieser Tabelle zu eskalieren. Wenn die Sperreskalation erfolgreich verläuft, werden alle Sperren, die von der Transaktion in einer vorherigen Anweisung erworben wurden und zum Zeitpunkt des Beginns des Ereignisses noch ausgeführt werden, eskaliert, wenn auf die Tabelle von der aktuellen Anweisung verwiesen wird und in das Eskalationsereignis einbezogen wird.

Gehen Sie beispielsweise davon aus, dass eine Sitzung diese Vorgänge ausführt:

• beginnt eine Transaktion,
• Aktualisiert TableA. Dadurch werden exklusive Zeilensperren in TableA generiert, die bis zum Abschluss der Transaktion gehalten werden.
• Aktualisiert TableB. Dadurch werden exklusive Zeilensperren in TableB generiert, die bis zum Abschluss der Transaktion gehalten werden.
• Führt eine SELECT-Anweisung aus, die TableA mit TableC verknüpft. Der Abfrageausführungsplan ruft die aus TableA abzurufenden Zeilen auf, bevor die Zeilen aus TableC abgerufen werden.
• Die SELECT-Anweisung löst die Sperrenausweitung aus, während sie die Zeilen aus TableA abruft und bevor sie auf TableC zugegriffen hat.

Bei erfolgreicher Sperrenausweitung werden nur die von der Sitzung für TableA gehaltenen Sperren ausgeweitet. Dazu gehören sowohl die freigegebenen Sperren aus der SELECT-Anweisung als auch die exklusiven Sperren aus der vorherigen UPDATE-Anweisung. Während bei der Beurteilung, ob die Sperrenausweitung erfolgen soll, nur die Sperren berücksichtigt werden, die die Sitzung in TableA für die SELECT-Anweisung eingerichtet hat, werden bei erfolgreicher Ausweitung alle von der Sitzung in TableA gehaltenen Sperren zu einer exklusiven Sperre für die Tabelle ausgeweitet, und alle anderen Sperren mit geringerer Granularität, einschließlich beabsichtigter Sperren, für TableA werden aufgehoben.

Es wird nicht versucht, die Sperren für TableB auszuweiten, weil es in der SELECT-Anweisung keinen aktiven Verweis auf TableB gibt. Desgleichen wird nicht versucht, die Sperren für TableC auszuweiten, weil zum Zeitpunkt der Ausweitung noch kein Zugriff auf die Tabelle erfolgt war.

Sperren der Eskalation mit optimierter Sperrung

Optimierte Sperrung hilft, den Sperrspeicher zu reduzieren, da nur sehr wenige Sperren für die Dauer der Transaktion gehalten werden. Wenn das SQL Server-Datenbankmodul Zeilen- und Seitensperren abruft, kann die Sperreskalation ähnlich, aber viel seltener auftreten. Optimierte Sperrung ist in der Regel erfolgreich, um Sperreskalationen zu vermeiden, wobei die Anzahl der Sperren und die Menge des erforderlichen Sperrspeichers verringert werden.

Wenn die optimierte Sperre aktiviert ist und in der Standardmäßigen READ COMMIT-Isolationsebene Zeilen- und Seitensperren freigegeben werden, sobald der Schreibvorgang abgeschlossen ist. Es werden keine Zeilen- und Seitensperren für die Dauer der Transaktion gehalten, mit Ausnahme einer einzelnen Transaktions-ID(TID)-Sperre. Dadurch wird die Wahrscheinlichkeit einer Sperreskalation reduziert.

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 erwirbt mindestens 5.000 Sperren für eine einzelne nicht partitionierte Tabelle oder einen index.
• Eine einzelne Transact-SQL-Anweisung erwirbt mindestens 5.000 Sperren auf einer einzelnen Partition einer partitionierten Tabelle, und die ALTER TABLE SET LOCK_ESCALATION Option ist auf AUTO festgelegt.
• Die Anzahl der Sperren in einer Instanz des Datenbankmoduls überschreitet Arbeitsspeicher- oder Konfigurationsschwellenwerte.

Wenn Sperren aufgrund von Sperrkonflikten nicht eskaliert werden können, löst das Datenbankmodul regelmäßig die Sperreskalation bei allen 1.250 neu erworbenen Sperren aus.

Ausweitungsschwellenwert für eine Transact-SQL-Anweisung

Wenn das Datenbankmodul bei allen 1.250 neu erworbenen Sperren nach möglichen Eskalationen sucht, tritt eine Sperreskalation nur dann auf, wenn eine Transact-SQL-Anweisung mindestens 5.000 Sperren für einen einzelnen Verweis einer Tabelle erworben hat. Die Sperreskalation wird ausgelöst, wenn eine Transact-SQL-Anweisung mindestens 5.000 Sperren für einen einzelnen Verweis einer Tabelle erwirbt. Die Sperreskalation 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 erwirbt. Ebenso wird die Sperreskalation nicht ausgelöst, wenn eine Anweisung über eine Selbstverknnung in einer Tabelle verfügt und jeder Verweis auf die Tabelle nur 3.000 Sperren in der Tabelle erhält.

Die Sperreskalation tritt nur für Tabellen auf, auf die zu dem Zeitpunkt zugegriffen wurde, zu dem die Eskalation ausgelöst wurde. Angenommen, eine einzelne SELECT-Anweisung ist ein Join, der auf drei Tabellen in genau dieser Reihenfolge zugreift: TableA, TableB und TableC. Die Anweisung richtet 3.000 Zeilensperren im gruppierten Index für TableA ein und mindestens 5.000 Zeilensperren im gruppierten Index für TableB. Auf TableC wurde jedoch noch nicht zugegriffen. Wenn das Datenbankmodul erkennt, dass die Anweisung mindestens 5.000 Zeilensperren TableBerhalten hat, versucht es, alle Sperren zu eskalieren, die von der aktuellen Transaktion TableBgehalten werden. 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

Wenn die Anzahl der Sperren größer als der Speicherschwellenwert für die Sperreskalation ist, löst das Datenbankmodul die Sperreskalation aus. Der Speicherschwellenwert richtet sich nach der Einstellung der Konfigurationsoption „locks“:

• Wenn die locks-Option auf die Standardeinstellung 0 festgelegt ist, wird der Schwellenwert der Sperrenausweitung erreicht, wenn der von Sperrobjekten belegte Speicheranteil 24 % des von der Datenbank-Engine verwendeten Speichers (ausschließlich AWE-Speicher) beträgt. Die Zum Darstellen einer Sperre verwendete Datenstruktur beträgt ca. 100 Byte. Dieser Schwellenwert ist dynamisch, da die Datenbank-Engine je nach wechselnder Arbeitsauslastung dynamisch Speicher reserviert und freigibt.

• Wenn die locks-Option einen von 0 abweichenden Wert hat, beträgt der Schwellenwert für die Sperrenauswertung 40 % des Werts der locks-Option (oder weniger, wenn nicht genügend Arbeitsspeicher verfügbar ist).

Das Datenbankmodul kann jede aktive Anweisung aus jeder Sitzung zur Eskalation auswählen, und für alle 1.250 neuen Sperren wählt es Anweisungen für die Eskalation aus, solange der in der Instanz verwendete Sperrspeicher über dem Schwellenwert liegt.

Eskalieren gemischter Sperrtypen

Wenn die Sperreskalation auftritt, 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 eine Sitzung an:

• beginnt eine Transaktion,
• Aktualisiert eine Tabelle, die einen gruppierten Index enthält.
• Gibt eine SELECT-Anweisung aus, die auf dieselbe Tabelle verweist.

Die UPDATE-Anweisung erwirbt diese Sperren:

• Exklusive (X)-Sperren für die aktualisierten Datenzeilen.
• Intent exclusive (IX) sperrt sich auf den gruppierten Indexseiten, die diese Zeilen enthalten.
• Eine IX-Sperre für den gruppierten Index und eine andere auf der Tabelle.

Die SELECT-Anweisung erhält diese Sperren:

• Freigegeben (S) sperrt alle gelesenen Datenzeilen, es sei denn, die Zeile ist bereits durch eine X-Sperre aus der UPDATE-Anweisung geschützt.
• Absichtsfreigabesperren auf allen gruppierten Indexseiten, die diese Zeilen enthalten, es sei denn, die Seite ist bereits durch eine IX-Sperre geschützt.
• Keine Sperre für den gruppierten Index oder die Gruppierte Tabelle, da sie bereits durch IX-Sperren geschützt sind.

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

Reduzieren der Sperrung und Eskalation

In den meisten Fällen bietet das Datenbankmodul die beste Leistung, wenn sie mit ihren Standardeinstellungen für die Sperr- und Sperreskalation verwendet wird.

• Nutzen Sie die vorteile der optimierten Sperrung.

  • Optimierte Sperrung bietet einen verbesserten Mechanismus für die Transaktionssperrung, mit dem der Speicherverbrauch und das Blockieren gleichzeitiger Transaktionen reduziert wird. Die Sperreskalation ist viel weniger wahrscheinlich, wenn eine optimierte Sperrung aktiviert ist.
  • Vermeiden Sie Tabellenhinweise mit optimierter Sperrung. Tabellenhinweise können die Effektivität der optimierten Sperrung verringern.
  • Aktivieren Sie READ_COMMITTED_SNAPSHOT in der Datenbank, um am meisten von der optimierten Sperrung zu profitieren. Dies ist die Standardisolationsstufe in der Azure SQL-Datenbank.
  • Für eine optimierte Sperrung ist eine beschleunigte Datenbankwiederherstellung (ADR) erforderlich, die für die Datenbank aktiviert ist.

Wenn eine Instanz des Datenbankmoduls viele Sperren generiert und häufige Sperreskalationen angezeigt wird, erwägen Sie, die Anzahl der Sperren mit den folgenden Strategien zu reduzieren:

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

  • READ COMMIT isolation level when the READ_COMMITTED_SNAPSHOT database option is ON.
  • SNAPSHOT-Isolationsebene.
  • READ UNCOMMITTED isolation level. Dies kann nur für Systeme verwendet werden, die mit schmutzigen Lesevorgängen arbeiten können.

  Hinweis

  Das Ändern der Isolationsstufe wirkt sich auf alle Tabellen in der Instanz des Datenbankmoduls aus.

• Verwenden Sie die Tabellenhinweise PAGLOCK oder TABLOCK, damit das Datenbankmodul Seiten-, Heap- oder Indexsperren anstelle von Sperren auf niedriger Ebene verwendet. Die Verwendung dieser Option erhöht jedoch die Probleme, dass Benutzer andere Benutzer blockieren, die versuchen, auf dieselben Daten zuzugreifen, und sollte nicht in Systemen mit mehr als einigen gleichzeitigen Benutzern verwendet werden.

• Wenn die optimierte Sperre nicht aktiviert ist, verwenden Sie für partitionierte Tabellen die Option LOCK_ESCALATION ALTER TABLE , um Sperren anstelle der Tabelle auf hoBT-Ebene zu eskalieren oder die Sperreskalation zu deaktivieren.

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

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

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

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

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

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

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

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

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

• Sie können auch Ablaufverfolgungskennzeichnungen 1211 und 1224 verwenden, um alle oder einige Sperreskalationen zu deaktivieren. Diese Ablaufverfolgungskennzeichnungen deaktivieren jedoch alle Sperreskalation global für das gesamte Datenbankmodul. Die Sperreskalation dient einem sehr nützlichen Zweck im Datenbankmodul, indem die Effizienz von Abfragen maximiert wird, die andernfalls durch den Mehraufwand beim Abrufen und Freigeben von mehreren Tausend Sperren verlangsamt werden. Sperrenausweitung trägt auch dazu bei, den erforderlichen Arbeitsspeicher zu minimieren, um Sperren nachzuverfolgen. Der Arbeitsspeicher, den das Datenbankmodul dynamisch für Sperrstrukturen zuordnen kann, ist endlich. Wenn Sie die Sperreskalation deaktivieren und der Sperrspeicher groß genug wird, versuchen Sie möglicherweise, zusätzliche Sperren für jede Abfrage zuzuweisen, und der folgende Fehler tritt auf: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Hinweis

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

  Hinweis

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

Überwachen der Sperreskalation

Überwachen Sie die Sperreskalation mithilfe des lock_escalation erweiterten Ereignisses (xEvent), z. B. 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

Wichtig

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

Dynamisches Sperren

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

Das SQL Server-Datenbankmodul verwendet eine dynamische Sperrstrategie, um die kostengünstigsten Sperren zu ermitteln. Das SQL Server-Datenbankmodul bestimmt automatisch, welche Sperren am besten geeignet sind, wenn die Abfrage ausgeführt wird, basierend auf den Merkmalen des Schemas und der Abfrage. Um beispielsweise den Aufwand für die Sperren zu senken, kann der Abfrageoptimierer festlegen, dass beim Ausführen eines Indexscans Sperren auf Seitenebene für einen Index eingerichtet werden.

Dynamische Sperren bieten die folgenden Vorteile:

• Vereinfachte Datenbankverwaltung. Datenbankadministratoren müssen die Sperreneskalationsschwellen nicht anpassen.
• Gesteigerte Leistung. Das SQL Server-Datenbankmodul minimiert den Systemaufwand, indem Sperrungen verwendet werden, die für die Aufgabe geeignet sind.
• Anwendungsentwickler können sich auf die Entwicklung konzentrieren. Das SQL Server-Datenbankmodul passt die Sperre automatisch an.

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

Sperren der 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 CPUs verfügbar, wird automatisch aktiviert und kann nicht deaktiviert werden. Es können nur Objektsperren partitioniert werden. Objektsperren, die einen Untertyp aufweisen, werden nicht partitioniert. Weitere Informationen finden Sie unter sys.dm_tran_locks (Transact-SQL).

Grundlegendes zur Sperrpartitionierung

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

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

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

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

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

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

Implementieren und Überwachen der Sperrpartitionierung

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

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

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

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

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 Sperrpartitionierung

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.

Diese Transact-SQL-Anweisungen erstellen Testobjekte, die in den folgenden Beispielen verwendet werden.

-- Create a test table.  
CREATE TABLE TestTable  (col1 int);  
GO  
  
-- Create a clustered index on the table.  
CREATE CLUSTERED INDEX ci_TestTable   
    ON TestTable (col1);  
GO  
  
-- Populate the table.  
INSERT INTO TestTable VALUES (1);  
GO  

Beispiel A

Sitzung 1

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

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

Sitzung 2:

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

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

Sitzung 1

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

SELECT col1  
FROM TestTable  
WITH (TABLOCKX);  

Beispiel B

Sitzung 1

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

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

Sitzung 2:

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

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

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

Zeilenversionsbasierte Isolationsebenen im SQL Server-Datenbankmodul

Ab SQL Server 2005 (9.x) bietet das SQL Server-Datenbankmodul eine Implementierung einer vorhandenen Transaktionsisolationsstufe, die einen Snapshot auf Anweisungsebene mithilfe der Zeilenversionsverwaltung bereitstellt. Die SQL Server-Datenbank-Engine bietet außerdem die Transaktionsisolationsstufe SNAPSHOT, die ebenfalls die Zeilenversionsverwaltung verwendet, um Momentaufnahmen auf Transaktionsebene bereitzustellen.

Die Zeilenversionsverwaltung ist ein allgemeines Framework in SQL Server, das einen Kopier-on-Write-Mechanismus aufruft, wenn eine Zeile geändert oder gelöscht wird. Das setzt bei einer ausgeführten Transaktion voraus, dass die alte Zeilenversion für Transaktionen verfügbar sein muss, die einen früheren transaktionskonsistenten Zustand erfordern. Die Zeilenversionsverwaltung wird zur Unterstützung folgender Funktionen verwendet:

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

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

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

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

2. Festlegen der entsprechenden Transaktionsisolationsstufe in einer Anwendung:

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

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

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

Hinweis

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

Die Zeilenversionen werden lang genug aufbewahrt, um den Anforderungen von Transaktionen gerecht zu werden, die unter auf der Zeilenversionsverwaltung basierenden Isolationsstufen ausgeführt werden. Das SQL Server-Datenbankmodul verfolgt die früheste nützliche Transaktionssequenznummer und löscht regelmäßig alle Zeilenversionen, die mit Transaktionssequenznummern abgestempelt sind, die niedriger als die früheste nützliche Sequenznummer sind.

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

Hinweis

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

Verhalten beim Lesen von Daten

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

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

Wenn eine Transaktion mit der Momentaufnahmeisolationsstufe gestartet wird, zeichnet die Instanz des SQL Server-Datenbankmoduls alle derzeit aktiven Transaktionen auf. Wenn die Momentaufnahmetransaktion eine Zeile mit einer Versionskette liest, folgt das SQL Server-Datenbankmodul der Kette und ruft die Zeile ab, in 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.

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

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

Hinweis

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

Verhalten beim Ändern von Daten

Das Verhalten von Datenschreibvorgängen unterscheidet sich erheblich mit und ohne optimierte Sperrung.

Ändern von Daten ohne optimierte Sperrung

Bei einer lesesicheren Transaktion mit Zeilenversionsverwaltung erfolgt die Auswahl der zu aktualisierenden Zeilen mithilfe einer Blockierungsüberprüfung, bei der eine Aktualisierungssperre (U) in der Datenzeile abgerufen wird, während Datenwerte gelesen werden. Das ist dasselbe Verhalten wie bei Read Committed-Transaktionen ohne Zeilenversionsverwaltung. Wenn die Datenzeile nicht dem Updatekriterium entspricht, wird die Updatesperre für diese Zeile aufgehoben, und die nächste Zeile wird gesperrt und gescannt.

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

Hinweis

Aktualisieren Sie Vorgänge, die unter der Snapshotisolation ausgeführt werden, intern unter READ COMMIT-Isolation, wenn die Momentaufnahmetransaktion auf eine der folgenden Aktionen zugreift:

Eine Tabelle mit einer FOREIGN KEY-Einschränkung.

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

Eine indizierte Sicht, die auf mehrere Tabellen verweist.

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

Ändern von Daten mit optimierter Sperrung

Mit aktivierter optimierter Sperrung und aktivierter READ_COMMITTED_SNAPSHOT(RCSI)-Datenbankoption und Verwendung der standardmäßigen READ COMMIT-Isolationsstufe erwerben Leser keine Sperren, und Autoren erwerben kurze Sperrungen auf niedriger Dauer anstelle von Sperren, die am Ende der Transaktion ablaufen.

Die Aktivierung von RCSI wird für die meisten Effizienz mit optimierter Sperre empfohlen. Bei Verwendung strengerEr Isolationsebenen wie wiederholbarem Lesen oder Serialisieren wird das Datenbankmodul gezwungen, Zeilen- und Seitensperren bis zum Ende der Transaktion für Leser und Autoren zu speichern, was zu einem erhöhten Blockierungs- und Sperrspeicher führt.

Bei aktivierter RCSI-Funktion und bei Verwendung der standardmäßigen READ COMMIT-Isolationsebene qualifizieren Autoren Zeilen pro Prädikat basierend auf der neuesten zugesicherten Version der Zeile, ohne U-Sperren zu erwerben. Eine Abfrage wartet nur, wenn die Zeile qualifiziert ist und eine aktive Schreibtransaktion für diese Zeile oder Seite vorhanden ist. Das Qualifizieren basierend auf der neuesten zugesicherten Version und sperren nur die qualifizierten Zeilen reduziert die Blockierung und erhöht die Parallelität.

Wenn Aktualisierungskonflikte mit RCSI und in der standardmäßigen READ COMMIT-Isolationsstufe erkannt werden, werden sie automatisch behandelt und erneut versucht, ohne Auswirkungen auf Kundenarbeitslasten.

Bei aktivierter optimierter Sperrung ist das Verhalten von Aktualisierungskonflikten mit der SNAPSHOT-Isolationsstufe identisch. Aktualisierungskonflikte müssen von der Anwendung behandelt und manuell wiederholt werden.

Hinweis

Weitere Informationen zu Verhaltensänderungen mit der Sperre nach der Qualifiation (LAQ)-Funktion für eine optimierte Sperrung finden Sie unter Abfrageverhaltensänderungen mit optimierter Sperrung und RCSI.

Gesamtverhalten

In der folgenden Tabelle sind die Unterschiede zwischen Momentaufnahmeisolation und READ COMMIT-Isolation mithilfe der Zeilenversionsverwaltung zusammengefasst.

Eigenschaft	Read Committed-Isolationsstufe mit Zeilenversionsverwaltung	Momentaufnahmeisolationsstufe
Die Datenbankoption, die auf ON gesetzt sein muss, um die erforderliche Unterstützung zu aktivieren.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Wie eine Sitzung den speziellen Typ der Zeilenversionsverwaltung anfordert.	Verwenden Sie die standardmäßige Read Committed-Isolationsstufe, oder führen Sie die SET TRANSACTION ISOLATION LEVEL-Anweisung aus, um die READ COMMITTED-Isolationsstufe anzugeben. Das kann nach dem Start der Transaktion durchgeführt werden.	Erfordert, dass SET TRANSACTION ISOLATION LEVEL zum Angeben der MOMENTAUFNAHMEN-Isolationsstufe vor dem Start der Transaktion ausgeführt wird.
Die von den Anweisungen gelesene Datenversion.	Alle Daten, für die vor dem Start jeder Anweisung ein Commit erfolgte.	Alle Daten, für die vor dem Start jeder Transaktion ein Commit erfolgte.
Wie Updates behandelt werden.	Ohne optimierte Sperrung: Wird von Zeilenversionen auf tatsächliche Daten zurückgesetzt, um Zeilen auszuwählen, die aktualisiert werden sollen, 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 letzten zugesicherten Version ausgewählt, ohne dass Sperren abgerufen werden. Wenn Zeilen für das Update gelten, werden exklusive Zeilen- oder Seitensperren abgerufen. Wenn Aktualisierungskonflikte erkannt werden, werden sie automatisch behandelt und wiederholt.	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.
Aktualisieren der Konflikterkennung	Ohne optimierte Sperrung: Keine. Mit optimierter Sperrung: Wenn Updatekonflikte erkannt werden, werden sie automatisch behandelt und wiederholt.	Integrierte Unterstützung. Kann nicht deaktiviert werden.

Ressourcenverwendung bei der Zeilenversionsverwaltung

Das Zeilenversionsframework unterstützt die folgenden Features, die in SQL Server verfügbar sind:

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

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

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

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

Auf der Zeilenversionsverwaltung basierende Isolationsstufen erhöhen die von Datenänderungen benötigten Ressourcen. Bei Aktivierung dieser Optionen werden für alle Datenänderungen für die Datenbank Versionen angegeben. Eine Kopie der Daten, bevor änderungen gespeichert werden, auch wenn keine aktiven Transaktionen mit zeilenbasierter Versionsverwaltung vorhanden tempdb sind. Die Daten nach der Änderung enthalten einen Zeiger auf die in der Version gespeicherten tempdbDaten. Bei großen Objekten wird nur ein Teil des Objekts kopiert tempdb, in das sich geändert hat.

In tempdb verwendeter Speicherplatz

Für jede Instanz des SQL Server-Datenbankmoduls muss genügend Speicherplatz vorhanden sein, um die Zeilenversionen zu speichern, tempdb die für jede Datenbank in der Instanz generiert werden. Der Datenbankadministrator muss sicherstellen, dass tempdb ausreichend Speicherplatz zur Unterstützung des Versionsspeichers vorhanden ist. Es gibt zwei Versionsspeicher in tempdb:

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

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

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

Hinweis

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

Wenn tempdb nicht mehr Speicherplatz vorhanden ist, erzwingt das SQL Server-Datenbankmodul, dass die Versionsspeicher 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. Wenn der Verkleinerungsprozess erfolgreich ist, wird Speicherplatz verfügbar in tempdb. Andernfalls ist nicht mehr Platz vorhanden, tempdb und es tritt Folgendes 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 tempdb vollständigen Rollbacks nicht generiert wurden, werden mit einem Fehler 3958 beendet.

In Datenzeilen verwendeter Speicherplatz

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

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

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

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

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

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

In großen Objekten verwendeter Speicherplatz

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

Wenn einer Datenbank neue große Werte hinzugefügt werden, werden diese mithilfe von maximal 8.040 Byte an Daten pro Fragment zugeordnet. Frühere Versionen des SQL Server-Datenbankmoduls gespeichert bis zu 8080 Byte von ntext, textoder image Daten pro Fragment.

Vorhandene ntext, textund image große Objektdaten (LOB) werden nicht aktualisiert, um Platz für die Zeilenversionsinformationen zu schaffen, wenn eine Datenbank von einer früheren Version von SQL Server auf SQL Server aktualisiert 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. Die LOB-Daten werden aktualisiert, auch wenn nur 1 Byte geändert wird. Es handelt sich hierbei um einen einmaligen Vorgang für jede ntext-, text-, oder image-Spalte. Durch jeden Vorgang wird jedoch je nach dem Umfang der LOB-Daten eine hohe Menge an Seitenzuordnungen und E/A-Aktivitäten generiert. Es können zudem viele Protokollierungsaktivitäten generiert werden, sofern die Änderung vollständig protokolliert wird. WRITETEXT- und UPDATETEXT-Vorgänge werden minimal protokolliert, wenn der Datenbankwiederherstellungsmodus auf den Wert FULL festgelegt ist.

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

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

Überwachen der Zeilenversionsverwaltung und des Versionsspeichers

Für die Überwachung von Zeilenversionsverwaltungs-, Versionsspeicher- und Momentaufnahmeisolationsprozessen für Leistung und Probleme stellt SQL Server Tools in Form von DYNAMISCHEn Verwaltungsansichten (Dynamic Management Views, DMVs) und Leistungsindikatoren in Windows System Monitor bereit.

DMVs

Die folgenden DMVs enthalten Informationen über den aktuellen Systemstatus tempdb und den Versionsspeicher sowie Transaktionen mit Zeilenversionsverwaltung.

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

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

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

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

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

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

  Hinweis

  Die Systemobjekte sys.dm_tran_top_version_generators und sys.dm_tran_version_store potenziell sehr kostspielige Funktionen, die ausgeführt werden können, da beide den gesamten Versionsspeicher abfragen, was sehr groß sein könnte.
  Die sys.dm_tran_version_store_space_usage Ausführung ist zwar effizient und nicht teuer, da sie nicht durch einzelne Versionsspeicherdatensätze navigiert und stattdessen aggregierten Speicherplatz für tempdb den Versionsspeicher pro Datenbank zurückgibt.

• sys.dm_tran_active_snapshot_database_transactions. Gibt eine virtuelle Tabelle für alle aktiven Transaktionen in allen Datenbanken in der SQL Server-Instanz zurück, 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 vergleichbar sys.dm_tran_transactions_snapshotmit der Ausnahme, dass nur die aktiven Transaktionen für die aktuelle Momentaufnahme zurückgegeben werden. Weitere Informationen finden Sie unter sys.dm_tran_current_snapshot (Transact-SQL).

Leistungsindikatoren

SQL Server-Leistungsindikatoren liefern Informationen zur Systemleistung, die von SQL Server-Prozessen betroffen ist. Die folgenden Leistungsindikatoren überwachen tempdb und den Versionsspeicher sowie Transaktionen mit Zeilenversionsverwaltung. Die Leistungsindikatoren sind im SQLServer:Transaktionen-Leistungsobjekt enthalten.

• Freier Speicherplatz in tempdb (KB): Überwacht die Menge in Kilobyte (KB) des freien Speicherplatzes in der tempdb Datenbank. Es muss genügend freier Speicherplatz vorhanden tempdb sein, um den Versionsspeicher zu verarbeiten, der die Snapshotisolation 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.

• Versionsspeichergröße (KB): Überwacht die Größe in KB aller Versionsspeicher. Diese Informationen helfen dabei, den in der tempdb Datenbank benötigten Speicherplatz für den Versionsspeicher zu ermitteln. Die Überwachung dieses Leistungsindikators über einen bestimmten Zeitraum bietet eine nützliche Schätzung des zusätzlichen Platzes, der benötigt tempdbwird.

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

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

  Hinweis

  Informationen aus der Versionsgenerierungsrate (KB/s) und der Versionsbereinigungsrate (KB/s) können verwendet werden, um die Speicherplatzanforderungen vorherzusagen tempdb .

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

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

• Abschneiden von Versionsspeichereinheiten: Überwacht die Gesamtzahl der Versionsspeichereinheiten, die abgeschnitten wurden, seitdem die Instanz gestartet wurde. Eine Versionsspeichereinheit wird abgeschnitten, wenn SQL Server feststellt, dass keine der in der Versionsspeichereinheit gespeicherten Versionszeilen zum Ausführen aktiver Transaktionen erforderlich ist.

• Updatekonfliktquote: Überwacht das Verhältnis von Aktualisierungsmomentaufnahmentransaktionen, die Aktualisierungskonflikte aufweisen, zur Gesamtanzahl der Aktualisierungsmomentaufnahmentransaktionen.

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

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

• Momentaufnahmetransaktionen. Überwacht die Gesamtzahl aktiver Momentaufnahmetransaktionen.

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

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

  Hinweis

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

Beispiel für eine auf der Zeilenversionsverwaltung basierende Isolationsstufe

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

A. Arbeiten mit Snapshotisolation

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

Für Sitzung 1:

USE AdventureWorks2022;  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2022  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  
GO  
  
-- Start a snapshot transaction  
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

Für Sitzung 2:

USE AdventureWorks2022;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under snapshot isolation shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

Für Sitzung 1:

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

Für Sitzung 2:

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

Für Sitzung 1:

    -- Reissue the SELECT statement - this still   
    -- shows the employee having 48 vacation hours  
    -- even after the other transaction has committed  
    -- the data modification.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- Because the data has been modified outside of the  
    -- snapshot transaction, any further data changes to   
    -- that data by the snapshot transaction will cause   
    -- the snapshot transaction to fail. This statement   
    -- will generate a 3960 error and the transaction will   
    -- terminate.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION  
GO  

B. Arbeiten mit Lese-Commit mithilfe der Zeilenversionsverwaltung

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

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

Für Sitzung 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.  
GO  
  
-- Enable READ_COMMITTED_SNAPSHOT on the database.  
-- For this statement to succeed, this session  
-- must be the only connection to the AdventureWorks2022  
-- database.  
ALTER DATABASE AdventureWorks2022  
    SET READ_COMMITTED_SNAPSHOT ON;  
GO  
  
-- Start a read-committed transaction  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

Für Sitzung 2:

USE AdventureWorks2022;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under read-committed using row versioning shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

Für Sitzung 1:

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

Für Sitzung 2:

-- Commit the transaction.  
COMMIT TRANSACTION;  
GO  

Für Sitzung 1:

    -- Reissue the SELECT statement which now shows the   
    -- employee having 40 vacation hours.  Being   
    -- read-committed, this transaction is reading the   
    -- committed data. This is different from snapshot  
    -- isolation which reads from the versioned row.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- This statement, which caused the snapshot transaction   
    -- to fail, will succeed with read-committed using row versioning.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION;  
GO  

Aktivieren von Zeilenversionsverwaltungsbasierten Isolationsstufen

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

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

Die folgende Transact-SQL-Anweisung aktiviert READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022  
    SET READ_COMMITTED_SNAPSHOT ON;  

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

Die folgende Transact-SQL-Anweisung aktiviert ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  

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

Status der Momentaufnahmeisolationsumgebung der aktuellen Datenbank	Beschreibung
OFF	Die Unterstützung von Momentaufnahmeisolationstransaktionen ist nicht aktiviert. Momentaufnahmeisolationtransaktionen sind nicht zulässig.
PENDING_ON	Die Unterstützung von Momentaufnahmeisolationstransaktionen befindet sich in einem Übergangsstatus (von OFF nach ON). Offene Transaktionen müssen abgeschlossen werden. Momentaufnahmeisolationtransaktionen sind nicht zulässig.
EIN	Die Unterstützung von Momentaufnahmeisolationstransaktionen ist aktiviert. Momentaufnahmeisolationtransaktionen sind zulässig.
PENDING_OFF	Die Unterstützung von Momentaufnahmeisolationstransaktionen befindet sich in einem Übergangsstatus (von ON nach OFF). Momentaufnahmetransaktionen, die nach diesem Zeitpunkt gestartet werden, können nicht auf die Datenbank zugreifen. Updatetransaktionen sind ist in dieser Datenbank noch durch die Versionsverwaltung eingeschränkt. Vorhandene Momentaufnahmetransaktionen können immer noch problemlos auf die Datenbank zugreifen. Der PENDING_OFF-Status wird erst OFF, wenn alle Momentaufnahmetransaktionen abgeschlossen sind, die zu dem Zeitpunkt, als der Momentaufnahmeisolationsstatus der Datenbank ON war, aktiviert waren.

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

Alle Aktualisierungen von Benutzertabellen und einigen Systemtabellen, die in master Zeilenversionen gespeichert und msdb generiert werden.

Die ALLOW_SNAPSHOT_ISOLATION Option wird in den master Datenbanken msdb automatisch aktiviert und kann nicht deaktiviert werden.

Benutzer können die READ_COMMITTED_SNAPSHOT Option "EIN" in master, , tempdboder msdb.

Verwenden von Zeilenversionsverwaltungsbasierten Isolationsstufen

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

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

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

  ALTER DATABASE AdventureWorks2022  
      SET READ_COMMITTED_SNAPSHOT ON;  
  

  Wenn die Datenbank aktiviert READ_COMMITTED_SNAPSHOTist, verwenden alle Abfragen, die unter der Isolationsebene READ COMMIT ausgeführt werden, zeilenversionsverwaltung, was bedeutet, dass Lesevorgänge keine Aktualisierungsvorgänge blockieren.

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

  ALTER DATABASE AdventureWorks2022  
      SET ALLOW_SNAPSHOT_ISOLATION ON;  
  

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

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

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

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

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

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

• READ_COMMITTED_SNAPSHOTkann nicht in tempdb, oder msdbmaster.

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

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

• Momentaufnahmetransaktionen erzeugen einen Fehler, wenn Folgendes zutrifft:

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

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

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

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

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

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

  Hinweis

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

Anpassen der Sperrung und Zeilenversionsverwaltung

Anpassen des Sperrtimeouts

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

Hinweis

Verwenden Sie in SQL Server die sys.dm_os_waiting_tasks dynamische Verwaltungsansicht, um zu bestimmen, ob ein Prozess blockiert wird und wer ihn blockiert. Verwenden Sie in früheren Versionen von SQL Server die sp_who gespeicherte Systemprozedur. Weitere Informationen und Beispiele finden Sie unter Grundlegendes und Beheben von SQL Server-Blockierungsproblemen.

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

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

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

SELECT @@lock_timeout;  
GO  

Anpassen der Transaktionsisolationsstufe

READ COMMIT ist die Standardisolationsstufe für das Microsoft SQL Server-Datenbankmodul. 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 verwalteten Namespace verwenden, können mithilfe der SqlConnection.BeginTransaction Methode eine IsolationLevel Option angeben.
• Anwendungen, die ADO verwenden, können die Autocommit Isolation Levels-Eigenschaft festlegen.
• Beim Starten einer Transaktion können Anwendungen mit OLE DB die gewünschte Transaktionsisolationsstufe aufrufen ITransactionLocal::StartTransactionisoLevel . Wenn Sie die Isolationsebene im AutoCommit-Modus angeben, können Anwendungen, die OLE DB verwenden, 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.

Wenn die Isolationsebene angegeben wird, wird das Sperrverhalten für alle Abfragen und DML-Anweisungen (Data Manipulation Language) in der SQL Server-Sitzung auf dieser Isolationsebene ausgeführt. Die Isolationsstufe bleibt gültig, bis die Sitzung beendet wird oder bis eine andere Isolationsstufe festgelegt wird.

Im folgenden Beispiel wird die SERIALIZABLE-Isolationsstufe festgelegt:

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

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

Das SQL Server-Datenbankmodul muss möglicherweise Sperren beim Lesen von Metadaten abrufen, auch wenn die Isolationsebene auf eine Ebene festgelegt ist, auf der Freigabesperren beim Lesen von Daten nicht angefordert werden. Eine in der READ UNCOMMITTED-Isolationsstufe ausgeführte Transaktion richtet beim Lesen von Daten beispielsweise keine freigegebenen Sperren ein, kann jedoch zu einem gewissen Zeitpunkt Sperren anfordern, wenn eine Systemkatalogsicht gelesen wird. Dies bedeutet, dass eine READ UNCOMMITTED-Transaktion beim Abfragen einer Tabelle Blockierungen verursachen kann, wenn eine andere Transaktion gleichzeitig die Metadaten der Tabelle ändert.

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

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

Hier ist das Resultset.

Set Option                   Value  
---------------------------- -------------------------------------------  
textsize                     2147483647  
language                     us_english  
dateformat                   mdy  
datefirst                    7  
...                          ...  
Isolation level              repeatable read  
 
(14 row(s) affected)   
 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sperrhinweise

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

Hinweis

Sperrhinweise werden nicht für die Verwendung empfohlen, wenn die optimierte Sperrung aktiviert ist. Während Tabellen- und Abfragehinweise berücksichtigt werden, reduzieren sie den Vorteil einer optimierten Sperrung. Weitere Informationen finden Sie unter "Vermeiden von Sperrhinweisen mit optimierter Sperrung".

Weitere Informationen zu den spezifischen Sperrhinweisen und deren Verhalten finden Sie unter Table Hints (Transact-SQL).For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

Hinweis

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

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

Wie im folgenden Beispiel gezeigt, wird die Transaktionsisolationsstufe auf SERIALIZABLE" festgelegt" und der Sperrhinweis NOLOCK auf Tabellenebene mit der SELECT Anweisung verwendet. Schlüsselbereichssperrungen, die in der Regel verwendet werden, um serialisierbare Transaktionen aufrechtzuerhalten, werden nicht abgerufen.

USE AdventureWorks2022;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT JobTitle  
    FROM HumanResources.Employee WITH (NOLOCK);  
GO  
  
-- Get information about the locks held by   
-- the transaction.  
SELECT    
        resource_type,   
        resource_subtype,   
        request_mode  
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;  
  
-- End the transaction.  
ROLLBACK;  
GO  

Die einzige Sperre, die referenziert HumanResources.Employee wurde, ist eine Schemastabilitätssperre (Sch-S). In diesem Fall kann die Serialisierbarkeit nicht mehr garantiert werden.

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

Anpassen der Sperre für einen Index

Das SQL Server-Datenbankmodul verwendet eine dynamische Sperrstrategie, die automatisch die beste Sperr granularität für Abfragen in den meisten Fällen auswäht. Es empfiehlt sich, die Standardeinstellungen der Sperrebenen, in denen die Seiten- und Zeilensperre aktiviert ist, nicht zu überschreiben, es sei denn, die Zugriffsmuster für Tabellen oder Indizes sind bekannt und konsistent und es liegt ein Ressourcenkonflikt vor, der behoben werden muss. Das Überschreiben einer Sperrebene kann den gleichzeitigen Zugriff auf eine Tabelle oder einen Index signifikant einschränken. Wenn beispielsweise in einer großen Tabelle, auf die viele Benutzer zugreifen, nur Sperren auf Tabellenebene angegeben werden, kann dies zu Engpässen führen, da die Benutzer die Aufhebung der Sperre auf Tabellenebene abwarten müssen, bevor sie auf die Tabelle zugreifen können.

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

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

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

Die Granularität der Sperren für einen Index kann mithilfe der Anweisungen CREATE INDEX und ALTER INDEX festgelegt werden. Die Einstellungen für die Sperre werden sowohl auf die Indexseiten als auch auf die Tabellenseiten angewendet. Darüber hinaus können die Anweisungen CREATE TABLE und ALTER TABLE dazu verwendet werden, die Granularität der Sperren für PRIMARY KEY- und UNIQUE-Einschränkungen festzulegen. Aus Gründen der Abwärtskompatibilität kann auch die gespeicherte Systemprozedur sp_indexoption zum Festlegen der Granularität verwendet werden. Verwenden Sie zum Anzeigen der aktuellen Sperroption für einen bestimmten Index die INDEXPROPERTY-Funktion. Es ist möglich, Sperren auf Seitenebene, auf Zeilenebene oder eine Kombination von Sperren auf Seiten- und Zeilenebene für einen bestimmten Index nicht zuzulassen.

Nicht zugelassene Sperren	Indexzugriff durch
Seitenebene	Sperren auf Zeilen- und Tabellenebene
Zeilenebene	Sperren auf Seiten- und Tabellenebene
Seiten- und Zeilenebene	Sperren auf Tabellenebene

Erweiterte Transaktionsinformationen

Schachteln von Transaktionen

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

Im folgenden Beispiel wird dargestellt, wie geschachtelte Transaktionen verwendet werden sollten. Die TransProc-Prozedur erzwingt eine Transaktion, unabhängig vom Transaktionsmodus des Prozesses, der die Prozedur ausführt. Wenn TransProc aufgerufen wird, wenn eine Transaktion aktiv ist, wird die geschachtelte Transaktion in TransProc weitgehend ignoriert, und ihre INSERT Anweisungen werden basierend auf der endgültigen Aktion, die für die äußere Transaktion erworben wurde, zugesichert oder zurückgesetzt. Wenn TransProc von einem Prozess ausgeführt wird, der keine ausstehende Transaktion aufweist, führt COMMIT TRANSACTION am Ende der Prozedur letztendlich einen Commit für die INSERT-Anweisungen aus.

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

Der Commit für interne Transaktionen wird vom SQL Server-Datenbankmodul ignoriert. Die Transaktion wird entweder zugesichert oder basierend auf der am Ende der äußersten Transaktion erworbenen Aktion zurückgesetzt. 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. Auch wenn eine Anweisung innerhalb einer COMMIT TRANSACTION transaction_name geschachtelten Transaktion auf den Transaktionsnamen der äußeren Transaktion verweist, gilt der Commit nur für die innerste Transaktion.

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

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

Verwenden gebundener 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.

Um an einer gebundenen Sitzung teilzunehmen, ruft eine Sitzung sp_getbindtoken oder srv_getbindtoken (über Open Data Services) auf, 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 binden durch Aufrufen sp_bindsessiondes Bindungstokens, das von der ersten Sitzung empfangen wurde, an die Transaktion.

Hinweis

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

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

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

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

• Bindungstoken können in einer Tabelle in einer Instanz des SQL Server-Datenbankmoduls gespeichert werden, die von Prozessen gelesen werden können, die eine Bindung an die erste Sitzung ausführen 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 auf Ergebnisse von der Instanz wartet, können keine anderen gebundenen Sitzungen auf die Instanz zugreifen, bis die aktuelle Sitzung die aktuelle Anweisung vollständig verarbeitet hat oder abbricht. Ist die Instanz mit der Verarbeitung einer Anweisung einer anderen gebundenen Sitzung ausgelastet, zeigt eine Fehlermeldung an, dass der Transaktionsbereich verwendet wird und der Zugriffsversuch der Sitzung später wiederholt werden kann.

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

Typen von gebundenen Sitzungen

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

• Lokale gebundene Sitzungen
  Ermöglicht gebundenen Sitzungen das Freigeben des Transaktionsraums einer einzelnen Transaktion in einer einzigen Instanz des SQL Server-Datenbankmoduls.

• Verteilte gebundene Sitzungen
  Ermöglicht gebundenen Sitzungen die gemeinsame Nutzung derselben Transaktion über zwei oder mehr Instanzen hinweg, bis die gesamte Transaktion mithilfe von Microsoft Distributed Transaction Coordinator (MS DTC) zugesichert 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 in erster Linie bei der Entwicklung erweiterter gespeicherter Prozeduren verwendet, die Transact-SQL-Anweisungen im Auftrag des Prozesses ausführen müssen, der sie aufruft. 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.

Im SQL Server-Datenbankmodul sind gespeicherte Prozeduren, die mit CLR geschrieben wurden, sicherer, skalierbarer und stabiler als erweiterte gespeicherte Prozeduren. CLR-gespeicherte Prozeduren verwenden nicht sp_bindsession, sondern das SqlContext-Objekt, um sich dem Kontext der aufrufenden Sitzung anzuschließen.

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

Effiziente Transaktionen mit Code

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

Coderichtlinien

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

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

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

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

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

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

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

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

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

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

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

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 verwendung impliziter Transaktionen wird 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. Mit diesem Prozess kann das SQL Server-Datenbankmodul den AutoCommit-Modus verwenden, während die Anwendung Daten durchsucht und Eingaben vom Benutzer erhält.

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

Verwalten langer Transaktionen

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

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

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

• Obwohl durch eine wartende Transaktion möglicherweise nur sehr wenige Protokolldaten generiert werden, wird die Protokollkürzung auf unbestimmte Zeit aufgehalten. Dies führt dazu, dass das Transaktionsprotokoll anwächst und möglicherweise irgendwann voll ist. Wenn das Transaktionsprotokoll voll ist, kann die Datenbank keine weiteren Updates mehr ausführen. Weitere Informationen finden Sie im SQL Server Transaction Log Architecture and Management Guide, Problembehandlung für ein vollständiges Transaktionsprotokoll (SQL Server-Fehler 9002) und das Transaktionsprotokoll (SQL Server)

Ermitteln von Transaktionen mit langer Ausführungszeit

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

• sys.dm_tran_database_transactions

  Diese dynamische Verwaltungssicht gibt Informationen zu Transaktionen auf Datenbankebene zurück. Bei einer lang andauernden Transaktion enthalten Die Spalten von besonderem Interesse die Zeit des ersten Protokolldatensatzes (database_transaction_begin_time), den aktuellen Status der Transaktion (database_transaction_state) und die Protokollsequenznummer (LSN) des Anfangsdatensatzes im Transaktionsprotokoll (database_transaction_begin_lsn).

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

• DBCC OPENTRAN

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

Beenden einer Transaktion

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

Deadlocks

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

• Weitere Informationen zu Deadlocks, einschließlich Überwachung, Diagnose und Beispiele, finden Sie im Deadlocks-Handbuch für das SQL Server-Datenbankmodul.
• Weitere Informationen zu spezifisch für Azure SQL-Datenbank spezifischen Deadlocks finden Sie unter Analysieren und Verhindern von Deadlocks in der Azure SQL-Datenbank.

Siehe auch

• Mehraufwand der Zeilenversionsverwaltung
• Erweiterte Ereignisse
• sys.dm_tran_locks (Transact-SQL)
• Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
• Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit Transaktionen (Transact-SQL)

Nächste Schritte

• Verstehen und Beheben von Problemen durch Blockierungen in SQL Server
• Verstehen und Beheben von Problemen durch Blockierungen in Azure SQL-Datenbank