Transaktionsdauerhaftigkeit steuern

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

SQL Server-Transaktionscommits können entweder vollständig dauerhaft sein, was in SQL Server der Standardeinstellung entspricht, oder sie können verzögert dauerhaft sein (auch bekannt als verzögerter Commit).

Vollständig dauerhafte Transaktionscommits sind synchron, melden, dass ein COMMIT erfolgreich ausgeführt wurde, und geben die Steuerung erst an den Client zurück, nachdem die Protokolldatensätze für die Transaktion auf den Datenträger geschrieben wurden. Verzögert dauerhafte Transaktionscommits sind asynchron und melden, dass ein COMMIT erfolgreich ausgeführt wurde, bevor die Protokolldatensätze für die Transaktion auf den Datenträger geschrieben wurden. Damit eine Transaktion dauerhaft ist, müssen die Transaktionsprotokolleinträge auf dem Datenträger festgeschrieben werden. Transaktionen mit verzögerter Dauerhaftigkeit werden dauerhaft, wenn die Transaktionsprotokolleinträge auf den Datenträger geschrieben werden.

In diesem Artikel werden verzögerte dauerhafte Transaktionen ausführlich beschrieben.

Vollständige vs. verzögerte Transaktionsdauerhaftigkeit

Sowohl die vollständige als auch die verzögerte Transaktionsdauerhaftigkeit bieten Vor- und Nachteile. Eine Anwendung kann eine Mischung aus vollständig dauerhaften und verzögert dauerhaften Transaktionen enthalten. Sie sollten sorgfältig abwägen, welche der beiden Transaktionsarten besser für Ihre Geschäftsanforderungen geeignet ist.

Volle Dauerhaftigkeit von Transaktionen

Bei vollständig dauerhaften Transaktionen wird das Transaktionsprotokoll auf dem Datenträger festgeschrieben, bevor die Steuerung an den Client zurückgegeben wird. Vollständig dauerhafte Transaktionen sollten in folgenden Fällen verwendet werden:

• Das System toleriert keine Datenverluste. Lesen Sie den Abschnitt "Wann kann ich Daten verlieren , um Informationen darüber zu erhalten, wann Sie einige Ihrer Daten verlieren können.

• Der Engpass ist nicht auf Latenzen beim Schreiben des Transaktionsprotokolls zurückzuführen.

Bei verzögerter Transaktionsdauerhaftigkeit verringert sich die Latenz aufgrund der E/A-Protokollvorgänge, weil die Transaktionsprotokoll-Datensätze im Arbeitsspeicher vorgehalten und batchweise in das Transaktionsprotokoll geschrieben werden und infolgedessen weniger E/A-Vorgänge erfordern. Verzögerte Transaktionsdauerhaftigkeit kann Konflikte bei Protokoll-E/A-Vorgängen verringern und so Wartezeiten im System reduzieren.

Volle Garantien für die Transaktionsbeständigkeit

• Sobald ein Transaktionscommit erfolgreich war, sind die durch die Transaktion ausgeführten Änderungen für die anderen Transaktionen im System sichtbar. Weitere Informationen zu Transaktionsisolationsstufen finden Sie unter SET TRANSACTION ISOLATION LEVEL (Transact-SQL) oder Transaktionen mit Memory-Optimized Tabellen.

• Die Datenbeständigkeit ist mit dem Commit gewährleistet. Die entsprechenden Protokolldatensätze werden dauerhaft auf dem Datenträger gespeichert, bevor das Transaktionscommit erfolgreich ausgeführt und die Steuerung an den Client zurückgegeben wird.

Verzögerte Transaktionsdauerhaftigkeit

Die verzögerte Transaktionsdauerhaftigkeit wird durch das asynchrone Schreiben von Protokolleinträgen auf den Datenträger erreicht. Transaktionsprotokoll-Datensätze werden in einem Puffer vorgehalten und auf dem Datenträger festgeschrieben, sobald sich der Puffer füllt oder das Leeren des Puffers durch ein Ereignis ausgelöst wird. Die verzögerte Transaktionsdauerhaftigkeit reduziert sowohl Latenzen als auch Konflikte innerhalb des Systems aus folgenden Gründen:

• Die Transaktionscommitverarbeitung wartet nicht, bis E/A-Protokollvorgänge abgeschlossen sind und die Steuerung an den Client zurückgegeben wurde.

• Bei gleichzeitigen Transaktionen sinkt die Konfliktwahrscheinlichkeit bei E/A-Protokollvorgängen. Stattdessen kann der Protokollpuffer in größeren Blöcken auf den Datenträger geleert werden, wodurch Konflikte verringert und der Durchsatz erhöht werden.

  Hinweis

  Bei einem hohen Grad an Parallelität können weiterhin Konflikte bei E/A-Protokollvorgängen auftreten, insbesondere, wenn der Protokollpuffer schneller gefüllt als geleert wird.

Wann die verzögerte Transaktionsbeständigkeit verwendet werden sollte

Einige der Situationen, in denen Sie von der Verwendung der verzögerten Transaktionsbeständigkeit profitieren können, sind:

Datenverluste sind in gewissem Umfang vertretbar.
Wenn Sie einen gewissen Datenverlust in Kauf nehmen können, etwa wenn einzelne Datensätze nicht kritisch sind, solange der Großteil der Daten erhalten bleibt, kann die verzögerte Dauerhaftigkeit erwägenswert sein. Wenn Sie keinerlei Datenverlust tolerieren können, verwenden Sie keine verzögerte Transaktionsdauerhaftigkeit.

Bei Schreibvorgängen in das Transaktionsprotokoll treten Engpässe auf.
Wenn Ihre Leistungsprobleme auf Latenz beim Schreiben des Transaktionsprotokolls zurückzuführen sind, wird Ihre Anwendung wahrscheinlich von verzögerter Transaktionsdauerhaftigkeit profitieren.

Arbeitsauslastungen weisen eine hohe Konfliktrate auf.
Wenn Ihr System Arbeitsauslastungen mit einer hohen Konfliktrate aufweist, wird viel Zeit mit dem Warten auf die Freigabe von Sperren vergeudet. Da die Commitzeit durch die verzögerte Transaktionsdauerhaftigkeit verkürzt wird, können Sperren schneller freigegeben und der Durchsatz kann erhöht werden.

Zusicherungen bei verzögerter Transaktionsdauerhaftigkeit

• Sobald ein Transaktionscommit erfolgreich war, sind die durch die Transaktion ausgeführten Änderungen für die anderen Transaktionen im System sichtbar.

• Die Dauerhaftigkeit von Transaktionen ist erst gewährleistet, nachdem das In-Memory-Transaktionsprotokoll auf die Festplatte geschrieben wurde. Das im Arbeitsspeicher befindliche Transaktionsprotokoll wird in folgenden Fällen auf den Datenträger geschrieben:

  • Durch eine vollständig dauerhafte Transaktion in derselben Datenbank werden eine Änderung in der Datenbank vorgenommen und erfolgreich ein Commit ausgeführt.

  • Der Benutzer führt erfolgreich die gespeicherte Systemprozedur sp_flush_log aus.

    Nachdem für eine vollständig dauerhafte Transaktion oder sp_flush_log erfolgreich ein Commit ausgeführt wurde, wurden alle verzögert dauerhaften Transaktionen, für die zuvor ein Commit ausgeführt wurde, garantiert zu dauerhaften Transaktionen.

  • SQL Server versucht, das Protokoll auf Datenträger zu leeren, sowohl auf Basis von Protokollgenerierung als auch von Zeitangaben, selbst wenn alle Transaktionen dauerhaft verzögert werden. Dies ist in der Regel erfolgreich, wenn das E/A-Gerät Schritt halten kann. SQL Server bietet jedoch keinerlei strikte Garantien hinsichtlich der Dauerhaftigkeit, mit Ausnahme von dauerhaften Transaktionen und sp_flush_log.

So steuern Sie die Dauerhaftigkeit von Transaktionen

Steuerung auf Datenbankebene

Sie als DBA können mit der folgenden Anweisung steuern, ob Benutzer die verzögerte Transaktionsbeständigkeit für eine Datenbank verwenden können. Sie müssen die Einstellung für verzögerte Haltbarkeit mit ALTER DATABASE festlegen.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

DEAKTIVIERT
[Standard] Mit dieser Einstellung sind alle Transaktionen, für die in der Datenbank ein Commit ausgeführt wurde, unabhängig von der Einstellung der Commitebene (DELAYED_DURABILITY=[ON | OFF]) vollständig dauerhaft. Gespeicherte Prozeduren müssen weder geändert noch neu kompiliert werden. Auf diese Weise können Sie verhindern, dass Daten aufgrund verzögerter Dauerhaftigkeit gefährdet werden.

ZULÄSSIG
Mit dieser Einstellung wird die Dauerhaftigkeit jeder Transaktion auf der Transaktionsebene bestimmt: DELAYED_DURABILITY = { OFF | ON }. Weitere Informationen finden Sie unter Steuerung auf Ebene des atomaren Blocks – nativ kompilierte gespeicherte Prozeduren und Steuerung auf COMMIT-Ebene.

FORCED
Mit dieser Einstellung wird jede Transaktion, für die in der Datenbank ein Commit ausgeführt wird, zu einer verzögert dauerhaften Transaktion. Unabhängig davon, ob für die Transaktion vollständige Dauerhaftigkeit (DELAYED_DURABILITY = OFF) oder keine Einstellung angegeben wird, wird sie zu einer verzögert dauerhaften Transaktion. Diese Einstellung ist hilfreich, wenn die verzögerte Transaktionsdauerhaftigkeit für eine Datenbank von Nutzen ist und Sie keinen Anwendungscode ändern möchten.

Steuerung auf Ebene atomarer Blöcke – nativ kompilierte gespeicherte Prozeduren

Der folgende Code steht innerhalb des atomaren Blocks.

DELAYED_DURABILITY = { OFF | ON }

AUS
[Standard] Die Transaktion ist vollständig dauerhaft, es sei denn, die Datenbankoption DELAYED_DURABILITY = FORCED ist aktiviert, wodurch das Commit asynchron und daher verzögert dauerhaft ist. Weitere Informationen finden Sie unter Steuerung auf Datenbankebene.

EIN
Die Transaktion ist verzögert dauerhaft, es sei denn, die Datenbankoption DELAYED_DURABILITY = DISABLED ist aktiviert, wodurch das Commit synchron und daher vollständig dauerhaft ist. Weitere Informationen finden Sie unter Steuerung auf Datenbankebene.

Beispielcode:

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

Tabelle 1: Haltbarkeit in atomaren Blöcken

Steuerung auf COMMIT-Ebene – Transact-SQL

Die COMMIT-Syntax wurde so erweitert, dass Sie eine verzögerte Transaktionsdauerhaftigkeit erzwingen können. Wenn DELAYED_DURABILITY auf Datenbankebene auf DISABLED oder FORCED festgelegt ist (siehe oben), wird diese COMMIT-Option ignoriert.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

AUS
[Standard] Das COMMIT der Transaktion ist vollständig dauerhaft, es sei denn, die Datenbankoption DELAYED_DURABILITY = FORCED ist aktiviert, wodurch das COMMIT asynchron und daher verzögert dauerhaft ist. Weitere Informationen finden Sie unter Steuerung auf Datenbankebene.

EIN
Das COMMIT der Transaktion ist verzögert dauerhaft, es sei denn, die Datenbankoption DELAYED_DURABILITY = DISABLED ist aktiviert, wodurch das COMMIT synchron und daher vollständig dauerhaft ist. Weitere Informationen finden Sie unter Steuerung auf Datenbankebene.

Zusammenfassung der Optionen und jeweiligen Interaktionen

In der folgenden Tabelle wird erläutert, auf welche Weise Einstellungen für verzögerte Dauerhaftigkeit auf Datenbankebene und Commitebene interagieren. Einstellungen auf Datenbankebene haben immer Vorrang vor Einstellungen auf Commitebene.

Erzwungenes Leeren des Transaktionsprotokolls

Sie können auf zwei Weisen erzwingen, dass das Transaktionsprotokoll auf den Datenträger geleert wird.

• Führen Sie eine beliebige vollständig persistente Transaktion aus, die dieselbe Datenbank ändert. Dadurch wird erzwungen, dass die Protokolldatensätze aller vorherigen verzögert dauerhaften Transaktionen, für die ein Commit ausgeführt wurde, auf den Datenträger geleert werden.

• Führen Sie die gespeicherte Systemprozedur sp_flush_log aus. Durch diese Prozedur wird erzwungen, dass die Protokolldatensätze aller vorherigen verzögert dauerhaften Transaktionen, für die ein Commit ausgeführt wurde, auf den Datenträger geleert werden. Weitere Informationen finden Sie unter sys.sp_flush_log (Transact-SQL).

Verzögerte Dauerhaftigkeit und andere SQL Server-Funktionen

Transaktionsreplikation, Änderungsverfolgung und Erfassung von Änderungsdaten

• Die Verwendung der verzögerten Dauerhaftigkeit wird für Datenbanken, die Transaktionsreplikation oder Change Data Capture (CDC) verwenden, nicht unterstützt.

• Die Änderungsnachverfolgung mit verzögerter Haltbarkeit wird unterstützt. Alle Transaktionen mit Änderungsnachverfolgung sind vollständig dauerhaft. Eine Transaktion verfügt über die Eigenschaft für das Nachverfolgen von Änderungen, wenn sie Schreibvorgänge in Tabellen ausführt, für die die Änderungsnachverfolgung aktiviert ist.

Ab SQL Server 2022 CU 2 und SQL Server 2019 CU 20 wird möglicherweise Folgendes angezeigt:

• Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set Wenn Sie versuchen, die Transaktionsreplikation oder die Änderungsdatenerfassung für eine Datenbank zu aktivieren, für die die verzögerte Dauerhaftigkeit aktiviert ist.

• Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled wenn Sie versuchen, eine verzögerte Dauerhaftigkeit für eine Datenbank zu aktivieren, die mit der Transaktionsreplikation oder der Änderungsdatenerfassung konfiguriert ist.

Wiederherstellung nach einem Systemabsturz
Zwar ist Konsistenz gewährleistet, allerdings können einige Änderungen durch verzögert dauerhafte Transaktionen, für die ein Commit ausgeführt wurde, verloren gehen.

Datenbankübergreifende Transaktionen und DTC
Ist eine Transaktion datenbankübergreifend oder verteilt, ist sie unabhängig von einer Datenbank- oder Transaktionscommiteinstellung vollständig dauerhaft.

Always On-Verfügbarkeitsgruppen und Spiegelung
Verzögert dauerhafte Transaktionen gewährleisten weder auf dem primären noch auf den sekundären Replikaten Dauerhaftigkeit. Darüber hinaus sind keine zuverlässigen Informationen zur Transaktion auf dem sekundären Replikat verfügbar. Nach dem Commit wird die Steuerung an den Client zurückgegeben, bevor eine Bestätigung von einem synchronen sekundären Replikat empfangen wird. Die Replikation zu sekundären Replikaten erfolgt weiterhin durch Leerung auf den Datenträger des primären Replikats.

Failoverclustering
Einige Schreibvorgänge verzögert dauerhafter Transaktionen können verloren gehen.

Azure Synapse Link für SQL
Verzögerte dauerhafte Transaktionen werden bei Azure Synapse Link für SQL nicht unterstützt.

Protokollversand
In das gesendete Protokoll werden nur Transaktionen aufgenommen, die in dauerhafte Transaktionen konvertiert wurden.

Sicherung des Transaktionsprotokolls
In die Sicherung werden nur Transaktionen aufgenommen, die in dauerhafte Transaktionen konvertiert wurden.

Wann kann ich Daten verlieren?

Wenn Sie verzögerte Dauerhaftigkeit in einer der Tabellen implementieren, sollten Sie beachten, dass es unter bestimmten Umständen zu Datenverlust kommen kann. Wenn Sie keinerlei Datenverlust tolerieren können, sollten Sie verzögerte Dauerhaftigkeit für Ihre Tabellen nicht verwenden.

Notfälle

Bei Notfällen wie beispielsweise einem Serverabsturz gehen die Daten für alle Transaktionen, für die ein Commit ausgeführt wurde, aber die noch nicht auf dem Datenträger gespeichert wurden, verloren. Verzögerte dauerhafte Transaktionen werden auf den Datenträger geschrieben, sobald eine vollständig dauerhafte Transaktion für eine beliebige Tabelle in der Datenbank ausgeführt wird – ob dauerhaft speicheroptimiert oder datenträgerbasiert – oder sp_flush_log aufgerufen wird. Wenn Sie verzögerte dauerhafte Transaktionen verwenden, sollten Sie in der Datenbank eine kleine Tabelle erstellen und diese regelmäßig aktualisieren oder regelmäßig sp_flush_log aufrufen, um alle noch ausstehenden, bereits bestätigten Transaktionen zu speichern. Das Transaktionsprotokoll wird ebenfalls geleert, sobald es voll ist, aber dies ist schwer vorherzusagen und kaum zu steuern.

Herunterfahren und Neustart von SQL Server

Für die verzögerte Dauerhaftigkeit macht es keinen Unterschied, ob das Herunterfahren unerwartet erfolgt oder ob das Herunterfahren/Neustarten von SQL Server geplant ist. Wie bei Notfällen sollten Sie mit Datenverlusten rechnen. Beim geplanten Herunterfahren/Neustarten werden möglicherweise einige Transaktionen, die noch nicht auf den Datenträger geschrieben wurden, vor dem Herunterfahren auf dem Datenträger gespeichert, aber dies lässt sich nicht planen. Planen Sie so, als ob beim Herunterfahren/Neustarten (ob geplant oder ungeplant) die Daten genauso wie bei unvorhersehbaren Notfällen verloren gehen.

Nächste Schritte

• Transaktionen mit speicheroptimierten Tabellen