Freigeben über

SETZE TRANSAKTIONSISOLATIONSSTUFE (Transact-SQL)

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

Steuert das Verhalten von Sperren und der Zeilenversionsverwaltung von Transact-SQL-Anweisungen, die von einer Verbindung mit SQL Server ausgestellt wurden.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL-Datenbank und SQL-Datenbank in Microsoft Fabric.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Syntax für Azure Synapse Analytics und Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Hinweis

Azure Synapse Analytics implementiert ACID-Transaktionen. Die Standardisolationsstufe ist READ UNCOMMITTED. Sie können sie READ COMMITTED SNAPSHOT ISOLATION ändern, indem Sie die ON Datenbankoption für eine Benutzerdatenbank aktivierenREAD_COMMITTED_SNAPSHOT, wenn sie mit der master Datenbank verbunden ist. Nach der Aktivierung werden alle Transaktionen in dieser Datenbank ausgeführt READ COMMITTED SNAPSHOT ISOLATION , und die Einstellung READ UNCOMMITTED auf Sitzungsebene wird nicht berücksichtigt. Weitere Informationen finden Sie unter ALTER DATABASE SET options (Transact-SQL).

Argumente

UNKOMMISSIONIERT LESEN

Gibt an, dass Anweisungen Zeilen lesen können, die von anderen Transaktionen geändert, aber noch nicht zugesichert wurden.

Transaktionen, die auf Ebene READ UNCOMMITTED ausgeführt werden, stellen keine freigegebenen Sperren aus, um zu verhindern, dass andere Transaktionen Daten ändern, die von der aktuellen Transaktion gelesen werden. READ UNCOMMITTED Transaktionen werden auch nicht durch exklusive Sperren blockiert, die verhindern würden, dass die aktuelle Transaktion Zeilen liest, die geändert wurden, aber nicht durch andere Transaktionen zugesichert wurden. Wenn diese Option festgelegt ist, ist es möglich, nicht ausgelassene Änderungen zu lesen, die als geänderte Lesevorgänge bezeichnet werden. Datenwerte können geändert werden, und Zeilen können vor dem Transaktionsende im Dataset hinzugefügt oder entfernt werden. Diese Option hat dieselbe Auswirkung wie das Festlegen NOLOCK aller Tabellen in allen SELECT Anweisungen in einer Transaktion. Von allen Isolationsstufen ist diese am wenigsten restriktiv.

Im SQL Server können Sie auch Konflikte zwischen Sperren minimieren und zugleich Transaktionen vor Dirty Reads von Datenänderungen, für die kein Commit ausgeführt wurde, folgendermaßen schützen:

  • Die READ COMMITTED Isolationsebene, auf der die READ_COMMITTED_SNAPSHOT Datenbankoption festgelegt ist ON.

  • Die SNAPSHOT Isolationsstufe. Weitere Informationen zur Momentaufnahmeisolation finden Sie unter Momentaufnahmeisolation in SQL Server.

ZUGESICHERT LESEN

Gibt an, dass Anweisungen keine Daten lesen können, die geändert, aber nicht von anderen Transaktionen zugesichert wurden. Dadurch werden Dirty Reads verhindert. Daten können von anderen Transaktionen zwischen einzelnen Anweisungen innerhalb der aktuellen Transaktion geändert werden, was zu nicht wiederholbaren Lesevorgängen oder Phantomdaten führt. Diese Option ist die Standardoption im SQL Server.

Das Verhalten READ COMMITTED hängt von der Einstellung der READ_COMMITTED_SNAPSHOT Datenbankoption ab:

  • Wenn READ_COMMITTED_SNAPSHOT diese Einstellung auf (standard auf SQL Server) festgelegt OFF ist, verwendet das Datenbankmodul 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. Der Freigegebene Sperrtyp bestimmt, wann er losgelassen wird. Zeilensperren werden aufgehoben, bevor die nächste Zeile verarbeitet wird. Seitensperren werden aufgehoben, wenn die nächste Seite gelesen wird, und Tabellensperren werden aufgehoben, nachdem die Ausführung der Anweisung beendet wurde.

  • Wenn READ_COMMITTED_SNAPSHOT dieser Wert festgelegt ONist, verwendet das Datenbankmodul die Zeilenversionsverwaltung, um jede Anweisung mit einer transaktionskonsensierten Momentaufnahme der Daten darzustellen, wie sie am Anfang der Anweisung vorhanden ist. Sperren werden nicht verwendet, um die Daten vor Aktualisierungen durch andere Transaktionen zu schützen.

    • READ_COMMITTED_SNAPSHOT ON ist der Standard in Azure SQL Database und SQL Database in Microsoft Fabric.

Wichtig

Die Auswahl einer Transaktionsisolationsstufe wirkt sich nicht auf die gesperrten Sperren aus, um Datenänderungen zu schützen. 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. Darüber hinaus verwendet ein Update auf READ COMMITTED Isolationsebene Aktualisierungssperren für die ausgewählten Datenzeilen, während ein Update auf SNAPSHOT Isolationsebene Zeilenversionen verwendet, um Zeilen auszuwählen, die aktualisiert werden sollen. 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. Weitere Informationen finden Sie im Leitfaden zur Transaktionssperre und Zeilenversionsverwaltung.

Die Momentaufnahmeisolation unterstützt FILESTREAM-Daten. Im Snapshot-Isolationsmodus sind FILESTREAM-Daten, die von jeder Anweisung in einer Transaktion gelesen werden, die transaktionskonsensierte Version der Daten, die zu Beginn der Transaktion vorhanden waren.

Wenn die READ_COMMITTED_SNAPSHOT Datenbankoption lautet ON, können Sie den READCOMMITTEDLOCK Tabellenhinweis verwenden, um die freigegebene Sperrung anstelle der Zeilenversionsverwaltung für einzelne Anweisungen in Transaktionen anzufordern, die auf Isolationsebene READ COMMITTED ausgeführt werden.

Hinweis

Wenn Sie die READ_COMMITTED_SNAPSHOT Option festlegen, ist nur die Verbindung, die den ALTER DATABASE Befehl ausführt, in der Datenbank zulässig. Es darf keine andere offene Verbindung in der Datenbank vorhanden sein, bis ALTER DATABASE der Vorgang abgeschlossen ist. Die Datenbank muss sich nicht im Einzelbenutzermodus befinden.

WIEDERHOLBARES LESEN

Gibt an, dass Anweisungen keine Daten lesen können, die geändert, aber noch nicht durch andere Transaktionen zugesichert wurden, und dass keine anderen Transaktionen Daten ändern können, die von der aktuellen Transaktion gelesen wurden, bis die aktuelle Transaktion abgeschlossen wurde.

Freigegebene Sperren werden auf alle Daten angewendet, die von den Anweisungen in der Transaktion gelesen werden, und werden bis zum Abschluss der Transaktion aufrecht erhalten. Dadurch wird verhindert, dass andere Transaktionen Zeilen ändern, die von der aktuellen Transaktion gelesen wurden. Andere Transaktionen können neue Zeilen in Übereinstimmung mit den Suchbedingungen von Anweisungen einfügen, die von der aktuellen Transaktion ausgegeben wurden. Wenn die aktuelle Transaktion dann die Anweisung erneut abruft, werden die neuen Zeilen abgerufen, was zu Phantomlesungen führt. Da freigegebene Sperren am Ende einer Transaktion gehalten werden, anstatt am Ende jeder Anweisung freigegeben zu werden, ist die Parallelität niedriger als die Standardisolationsstufe READ COMMITTED . Verwenden Sie diese Option nur, wenn sie wirklich erforderlich ist.

SNAPSHOT

Gibt an, dass daten, die von einer Anweisung in einer Transaktion gelesen werden, die transaktionskonsensierte Version der Daten sind, die zu Beginn der Transaktion vorhanden sind. Die Transaktion kann nur Datenänderungen erkennen, für die vor dem Beginn der Transaktion ein Commit ausgeführt wurde. Datenänderungen, die von anderen Transaktionen nach dem Start der aktuellen Transaktion vorgenommen wurden, sind für anweisungen, die in der aktuellen Transaktion ausgeführt werden, nicht sichtbar. Es erscheint daher, als ob die Anweisungen in einer Transaktion eine Momentaufnahme der festgeschriebenen Daten erhalten, die zu Beginn der Transaktion vorhanden waren.

Außer wenn eine Datenbank wiederhergestellt wird, SNAPSHOT fordern Transaktionen beim Lesen von Daten keine Sperren an. SNAPSHOT Transaktionen, die Daten lesen, verhindern nicht, dass andere Transaktionen Daten schreiben. Transaktionen, die Daten schreiben, blockieren SNAPSHOT keine Transaktionen beim Lesen von Daten.

Während der Rollbackphase einer Datenbankwiederherstellung fordern Transaktionen eine Sperre an, wenn versucht wird, SNAPSHOT Daten zu lesen, die durch eine andere Transaktion gesperrt sind, die zurückgesetzt wird. Die SNAPSHOT Transaktion wird blockiert, bis diese Transaktion zurückgesetzt wird. Die Sperre wird unmittelbar nach der Erteilung freigegeben.

Die ALLOW_SNAPSHOT_ISOLATION Datenbankoption muss festgelegt werden, ON bevor Sie eine Transaktion starten können, die die SNAPSHOT Isolationsstufe verwendet. Wenn eine Transaktion, die die SNAPSHOT Isolationsebene verwendet, auf Daten in mehreren Datenbanken zugreift, ALLOW_SNAPSHOT_ISOLATION muss für ON jede Datenbank festgelegt werden.

Eine Transaktion kann nicht auf SNAPSHOT Isolationsebene festgelegt werden, die mit einer anderen Isolationsstufe gestartet wurde. Dadurch wird die Transaktion abgebrochen. Wenn eine Transaktion auf der SNAPSHOT Isolationsebene beginnt, können Sie sie in eine andere Isolationsstufe und dann zurück zu SNAPSHOTändern. Eine Transaktion wird mit dem erstmaligen Zugriff auf Daten gestartet.

Eine Transaktion, die unter SNAPSHOT Isolationsebene ausgeführt wird, kann Änderungen anzeigen, die von dieser Transaktion vorgenommen wurden. Wenn die Transaktion beispielsweise eine UPDATE Tabelle ausführt und dann eine SELECT Anweisung für dieselbe Tabelle ausgibt, werden die geänderten Daten im Resultset enthalten.

Hinweis

Im Snapshot-Isolationsmodus sind FILESTREAM-Daten, die von einer Anweisung in einer Transaktion gelesen werden, die transaktionskonsensierte Version der Daten, die zu Beginn der Transaktion vorhanden waren, nicht am Anfang der Anweisung.

SERIALIZABLE

Gibt die folgenden Bedingungen an:

  • Anweisungen können keine Daten lesen, die geändert wurden, aber noch nicht von anderen Transaktionen übernommen wurden.

  • Keine anderen Transaktionen können Daten ändern, die von der aktuellen Transaktion gelesen wurden, bis die aktuelle Transaktion abgeschlossen ist.

  • Andere Transaktionen können keine neuen Zeilen mit Schlüsselwerten einfügen, die von allen Anweisungen in der aktuellen Transaktion gelesen werden, bis die aktuelle Transaktion abgeschlossen ist.

Bereichssperren werden in den Schlüsselwertbereichen eingerichtet, die die Suchbedingungen der in einer Transaktion ausgeführten Anweisungen erfüllen. Dadurch wird verhindert, dass andere Transaktionen Zeilen aktualisieren oder einfügen, die den von der aktuellen Transaktion ausgeführten Anweisungen entsprechen würden. Dies bedeutet, dass, wenn eine der Anweisungen in einer Transaktion ein zweites Mal ausgeführt wird, dieselbe Gruppe von Zeilen liest. Die Bereichssperren werden bis zum Abschluss der Transaktion aufrechterhalten. Diese Isolationsstufe ist am restriktivsten, da gesamte Schlüsselbereiche gesperrt werden, und die Sperren bis zum Abschluss der Transaktion aufrechterhalten werden. Da die Parallelität geringer ist, verwenden Sie diese Option nur, wenn es notwendig ist. Diese Option hat dieselbe Auswirkung wie das Festlegen HOLDLOCK aller Tabellen in allen SELECT Anweisungen in einer Transaktion.

Bemerkungen

Es kann jeweils nur eine der Optionen auf Isolationsebene festgelegt werden, und sie bleibt für diese Verbindung festgelegt, bis sie explizit geändert wird. Alle lesevorgänge, die innerhalb der Transaktion ausgeführt werden, werden unter den Regeln für die angegebene Isolationsebene ausgeführt, es sei denn, ein Tabellenhinweis in der FROM Klausel einer Anweisung gibt unterschiedliche Sperr- oder Versionsverwaltungsverhalten für eine Tabelle an.

Die Transaktionsisolationsstufen definieren die Typen von Sperren, die für Lesevorgänge angefordert werden. Freigegebene Sperren, die für oder in der Regel Zeilensperren erworben READ COMMITTEDREPEATABLE READ wurden, können zwar an Seiten- oder Tabellensperren eskaliert werden, wenn auf eine erhebliche Anzahl der Zeilen in einer Seite oder Tabelle durch den Lesevorgang verwiesen wird. Wenn die Transaktion eine Zeile nach dem Lesen ändert, erhält die Transaktion eine exklusive Sperre zum Schutz dieser Zeile, und die exklusive Sperre wird beibehalten, bis die Transaktion abgeschlossen ist. Wenn eine REPEATABLE READ Transaktion beispielsweise eine freigegebene Sperre für eine Zeile aufweist und die Transaktion dann die Zeile ändert, wird die Freigabezeile in eine exklusive Zeilensperre konvertiert.

Mit einer Ausnahme können Sie während einer Transaktion jederzeit von einer Isolationsstufe zu einer anderen wechseln. Die Ausnahme tritt auf, wenn sie von isolationsstufe zu SNAPSHOT Isolation wechselt. Dadurch erzeugt die Transaktion einen Fehler und führt ein Rollback aus. Sie können jedoch eine Transaktion, die isoliert SNAPSHOT gestartet wurde, in jede andere Isolationsstufe ändern.

Beim Wechsel der Isolationsstufe einer Transaktion werden Ressourcen, die nach dem Wechsel gelesen werden, gemäß den Regeln der neuen Isolationsstufe geschützt. Für Ressourcen, die vor dem Wechsel gelesen werden, gelten weiterhin die Regeln der bisherigen Isolationsstufe. Wenn sich beispielsweise eine Transaktion von READ COMMITTED zu SERIALIZABLE"Geändert" änderte, werden die freigegebenen Sperren, die nach der Änderung erworben wurden, nun bis zum Ende der Transaktion gehalten.

Wenn Sie ein Problem in einer gespeicherten Prozedur oder einem Trigger haben SET TRANSACTION ISOLATION LEVEL , wird beim Aufrufen des Objekts die Isolationsebene auf die Ebene zurückgesetzt, die wirksam ist. Wenn Sie z. B. in einem Batch festlegen REPEATABLE READ und der Batch dann eine gespeicherte Prozedur aufruft, die die Isolationsstufe festlegt SERIALIZABLE, wird die Einstellung für die Isolationsstufe wiederhergestellt REPEATABLE READ , wenn die gespeicherte Prozedur die Steuerung an den Batch zurückgibt.

Hinweis

Benutzerdefinierte Funktionen und benutzerdefinierte ClR-Typen (Common Language Runtime) können nicht ausgeführt werden SET TRANSACTION ISOLATION LEVEL. Sie können die Isolationsstufe jedoch mithilfe eines Tabellenhinweises überschreiben. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Wenn Sie zum Binden von zwei Sitzungen verwenden sp_bindsession , behält jede Sitzung die Einstellung der Isolationsstufe bei. Wenn SET TRANSACTION ISOLATION LEVEL Sie die Einstellung der Isolationsebene einer Sitzung ändern, wirkt sich dies nicht auf die Einstellung anderer an sie gebundener Sitzungen aus.

SET TRANSACTION ISOLATION LEVEL wird zur Ausführung oder Laufzeit wirksam und nicht zur Analysezeit.

Durch optimierte Massenladevorgänge für Heaps werden Abfragen blockiert, die unter den folgenden Isolationsstufen ausgeführt werden:

  • SNAPSHOT
  • READ UNCOMMITTED
  • READ COMMITTED Verwenden der Zeilenversionsverwaltung

Umgekehrt werden durch Abfragen, die unter diesen Isolationsstufen ausgeführt werden, optimierte Massenladevorgänge für Heaps blockiert. Weitere Informationen zu Massenladevorgängen finden Sie unter Massenimport und -export von Daten (SQL Server).

FILESTREAM-aktivierte Datenbanken unterstützen die folgenden Transaktionsisolationsstufen.

Isolationsstufe Transact-SQL Zugriff Dateisystemzugriff
Nicht auskommentiert lesen SQL Server Nicht unterstützt
Zugesichert lesen SQL Server SQL Server
Wiederholbares Lesen SQL Server Nicht unterstützt
Serialisierbar SQL Server Nicht unterstützt
Zugesicherte Momentaufnahme lesen SQL Server SQL Server
Snapshot SQL Server SQL Server

Beispiele

Im folgenden Beispiel wird TRANSACTION ISOLATION LEVEL für die Sitzung festgelegt. Für alle folgenden Transact-SQL-Anweisungen erhält SQL Server alle freigegebenen Sperren bis zum Abschluss der Transaktion aufrecht.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Verwandte Inhalte

  • Last updated on