Über Transaktion

Es gibt viele Situationen, in denen Benutzer Änderungen an Daten in vielen Tabellen in der Datenbank benötigen. In vielen Situationen verlieren Daten an Konsistenz, wenn sie separat ausgeführt werden.

Angenommen, die erste Anweisung wird korrekt ausgeführt, die nächste Anweisung schlägt jedoch fehl, weil die Daten falsch sind.

Eine besondere Situation sind beispielsweise Geldüberweisungen im Bankensystem. Die Übertragung erfordert eine INSERT-Anweisung und zwei UPDATE-Anweisungen:

• Der Benutzer muss das Guthaben im Quellkonto reduzieren.
• Dann ist es notwendig, den Kontostand im Banksystem im Zielkontodatensatz zu erhöhen.

Der Benutzer muss überprüfen, ob diese Transaktion festgeschrieben ist und ob die gleichen Änderungen an den Quell- und Zielkonten vorgenommen werden.

Transaktionsdefinition

Eine angemessene Arbeitseinheit muss vier Eigenschaften aufweisen, die als Atomicity, Consistency, Isolation, and Persistence (ACID)-Eigenschaften bekannt sind, um als Transaktion zu gelten:

Atomizität : Wenn die Transaktion viele Operationen hat, sollten alle festgeschrieben werden. Wenn eine Operation in der Gruppe fehlschlägt, wird sie zurückgesetzt.

Konsistenz: Die Reihenfolge der Operationen sollte angemessen sein

Isolation: Durchgeführte Operationen müssen dauerhaft von anderen Operationen auf derselben Serverdatenbank isoliert werden

Dauerhaftigkeit: Auf der Datenbank durchgeführte Operationen müssen gespeichert und dauerhaft in der Datenbank gespeichert werden.

Durchführung der Transaktion

SQL Server unterstützt Transaktionen mit mehreren Modi wie folgt:

• Autocommit-Transaktionen: (auto-commit) Jede einzelne Befehlszeile wird automatisch festgeschrieben, wenn sie erfolgreich ist. In diesem Modus müssen keine speziellen Anweisungen geschrieben werden, um die Transaktion zu starten und zu beenden. Dies ist der Standardmodus von SQL Server
• Explizite Transaktionen: (explizit) Jede explizite Transaktion beginnt mit einer BEGIN TRANSACTION-Anweisung und endet mit einer ROLLBACK- oder COMMIT-Transaktion.
• Implizite Transaktionen: (implizit) Eine Transaktion wird automatisch erfasst, wenn eine vorherige Transaktion abgeschlossen wird, und jede Transaktion wird mithilfe der ROLLBACK- oder COMMIT-Syntax abgeschlossen
• Batch-bezogene Transaktionen: (Batch-bezogene) diese Transaktionen beziehen sich auf das Konzept von Multiple Active Results Sets (MARS). Und jede implizite oder explizite Transaktion, die mit einem MARS-Pellet beginnt, wird als Batch-bezogene Transaktion bezeichnet.
• Verteilte Transaktionen: (verteilte Transaktionen) Es ist auf 2 oder mehr Server verteilt, die als Ressourcenmanager bekannt sind. Die Transaktionsverwaltung muss zwischen dem Ressourcenmanager durch eine als Transaktionsmanager bezeichnete Serverkomponente angeordnet sein. Jede Instanz in SQL Server kann als Ressourcenmanager in verteilten Transaktionen fungieren, die von einem Transaktionsmanager wie dem Microsoft Distributed Transaction Coordinator (MS DTC) lokalisiert werden.

Batches zur Transaktionserweiterung

Transaktionsanweisungen bestimmen den Erfolg oder Misserfolg des Blocks und stellen eine Datenbank bereit, die Vorgänge rückgängig machen kann.

Fehler, die während der Ausführung eines einfachen Stapels erkannt werden, sind wahrscheinlich teilweise erfolgreich, was bei der Verwendung von Transaktionen nicht das gewünschte Ergebnis ist.

Dieses Problem führt zu logischen Konflikten zwischen den Tabellen in der Datenbank.

Benutzer können Fehlerkontrollcode hinzufügen, um die Transaktion im Fehlerfall auf den alten Zustand zurückzusetzen.

Fehlerbehandlungscode macht alle Änderungen rückgängig, bevor der Fehler auftritt.

Transaktionskontrolle

Transaktionen können über die Anwendung gesteuert werden, indem der Beginn und das Ende einer Transaktion definiert werden.

Transaktionen werden standardmäßig von Verbindungsschichten verwaltet.

Wenn eine Transaktion eine Verbindung initiiert, werden alle T-SQL-Anweisungen auf derselben Verbindung ausgeführt und sind Teil der Verbindung, bis die Transaktion endet.

TRANSAKTION BEGINNEN

Die BEGIN TRANSACTION-Anweisung markiert den Beginn einer expliziten Transaktion.

Zum Beispiel:

USE AdventureWorks2019; GO DECLARE @TranName VARCHAR(30); SELECT @TranName = 'FirstTransaction'; BEGIN TRANSACTION @TranName; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13;

TRANSAKTION COMMIT

Die Anweisung COMMIT TRANSACTION markiert den Endpunkt, bei dem es sich um eine Festschreibung handelt, die das Ende einer impliziten oder expliziten Transaktion signalisiert.

COMMIT [TRAN | TRANSACTION] [transaction_name | @tran_name_variable]] [;]

Zum Beispiel:

BEGIN TRANSACTION; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION; GO

ARBEIT VERPFLICHTEN

Die Anweisung COMMIT WORK markiert den Endpunkt der Transaktion.

Syntax:

COMMIT [WORK] [;]

COMMIT TRANSACTION und COMMIT WORK sind identisch, außer dass COMMIT TRANSACTION einen benutzerdefinierten Transaktionsnamen akzeptiert.

Transaktion mit Commit erstellen:

BEGIN TRANSACTION DeleteCandidate WITH MARK N'Deleting a Job Candidate'; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION DeleteCandidate;

ROLLBACK-TRANSAKTION

Transaktionen können abgebrochen und zum ursprünglichen Punkt oder Sicherungspunkt in der Transaktion zurückgesetzt werden.

Es wird verwendet, um alle geänderten Daten zu löschen, die seit dem Start der Transaktion oder bis zum Sicherungspunkt generiert wurden. Es gibt auch die von der Transaktion gehaltenen Ressourcen frei.

TRANSAKTION SPEICHERN

Die SAVE TRANSACTION-Anweisung setzt den Sicherungspunkt innerhalb der Transaktion.

Syntax:

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable} [;]

Zum Beispiel:

CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; IF @TranCounter = 0 COMMIT TRANSACTION; IF @tranCounter = 1 ROLLBACK TRANSACTION ProcedureSave; GO

Im obigen Code wird die Savepoint-Transaktion innerhalb der Prozedur erstellt. Es wird nur dann zum Rollback verwendet, wenn Datenänderungen von der gespeicherten Prozedur generiert werden, wenn eine gültige Transaktion gestartet wurde, bevor die Prozedur ausgeführt wird.

@@TRANCOUNT in Transaktion

@@TRANCOUNT ist eine Systemfunktion, die den numerischen Wert der Transaktionsanweisung zurückgibt, die in der aktuellen Verbindung auftritt.

Zum Beispiel:

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT

Ergebnis:

Beispiel mit @@TRANCOUNT mit ROLLBACK

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT ROLLBACK PRINT @@TRANCOUNT

Ergebnis:

Transaktion markieren

Die Transaktionsmarkierung ist nur nützlich, wenn der Benutzer bereit ist, kürzlich festgeschriebene Transaktionen zu verlieren oder die zugehörige Datenbank auscheckt.

Das Markieren von Transaktionen nach einem Zeitplan in jeder einzelnen verwandten Datenbank erstellt eine gemeinsame Kette von Wiederherstellungspunkten in der Datenbank.

Bedenken bei der Verwendung von Marked Transaction:

Eine Transaktionsmarkierung verbraucht physischen Speicherplatz, verwenden Sie sie nur für Transaktionen, die für die Datenbankwiederherstellungsstrategie wichtig sind.

Wenn die markierte Transaktion festgeschrieben wird, wird die Zeile der logmarkhistory-Tabelle in der msdb-Tabelle hinzugefügt.

Wenn sich die markierte Transaktion über mehrere Datenbanken auf verschiedenen Servern oder auf demselben Server erstreckt, müssen Markierungen in den Datensätzen aller betroffenen Datenbanken aufgezeichnet werden.

Markierte Transaktion erstellen

Um eine markierte Transaktion zu erstellen, kann der Benutzer die BEGIN TRANSACTION-Anweisungssyntax mit der WITH MARK [DESCRIPTION]-Klausel verwenden.

Die Transaktion zeichnet die Markierungsbeschreibung, den Namen, den Benutzer, die Datenbank, Datums- und Uhrzeitinformationen und die Protokollfolgenummer (LSN) auf.

Schritte zum Erstellen einer markierten Transaktion in einer Reihe von Datenbanken:

• Der Name der Transaktion in der BEGIN TRAN-Anweisung und unter Verwendung der WITH MARK-Klausel.
• Führen Sie ein Update für alle Datenbanken im Satz durch.

Zum Beispiel:

USE AdventureWorks2019; GO BEGIN TRANSACTION ListPriceUpdate WITH MARK 'UPDATE Product List prices'; GO UPDATE Production.Product SET ListPrice = ListPrice * 1.20 WHERE ProductNumber LIKE 'BK-%'; GO COMMIT TRANSACTION ListPriceUpdate; GO

Unterschied zwischen impliziter und expliziter Transaktion

Implizit	Explizit
Die Transaktion wird von SQL Server für jede DDL- und DML-Anweisung verwaltet	Vom Compiler definierte Transaktion
DML- und DDL-Anweisungen werden implizit unter Transaktion ausgeführt	Besteht aus einer DML-Anweisung und wird als Abfrageeinheit ausgeführt.
Der SQL-Server setzt die gesamte Anweisung zurück	Schließen Sie keine SELECT-Anweisungen ein, da sie die Daten nicht ändern.

Isolationsstufe (Isolationsebene)

Transaktionen definieren eine Isolationsschicht, die den Grad definiert, in dem eine Transaktion von Daten- oder Ressourcenänderungen isoliert werden muss, die von anderen Transaktionen vorgenommen werden.

Isolationsstufen werden unter Bedingungen definiert, die gleichzeitige Effekte wie Dirty Reads zulassen.

Die Transaktionsisolationsstufe steuert Folgendes:

• Wenn die Daten gelesen werden, sind Sperren vorhanden und welche Art von Sperren sind erforderlich?
• Wie lange werden Lesesperren gehalten?
• Wenn eine Leseoperation, die auf eine Zeile verweist, von einer anderen Transaktion geändert wird, tritt eine der folgenden Situationen ein:
  • Blockieren, bis der einzigartige Verriegelungsmechanismus der Reihe entriegelt ist.
  • Rufen Sie die festgeschriebene Version der Zeile ab, die zu Beginn der Transaktion oder Anweisung vorhanden war.
  • Nicht festgeschriebene Daten lesen

Transaktionen erfordern jederzeit einen eindeutigen Schlüssel für alle Daten, die sie regeln. Anschließend hält es diese Sperre, bis die Transaktion abgeschlossen ist, unabhängig von der für diese Transaktion festgelegten Isolationsstufe.

Isolationsstufen:

Isolationsstufe	Schmutziges Lesen	Nicht wiederholbares Lesen
Lies engagiert	Nein	Ja
Ungebunden lesen	Ja	Nein
Schnappschuss	Nein	Nein
Wiederholbares Lesen	Nein	Nein
Serialisierbar	Nein	Nein

Reichweite und Arten von Schlössern

Liste gängiger Sperrtypen in SQL Ser

Verriegelter Zustand	Bezeichnung
Aktualisieren	Verwendung in Ressourcen zur Updatevorbereitung
Geteilt	Wird zum Lesen von Vorgängen verwendet, ohne Daten wie die Anweisung SELECT zu ändern
Beabsichtigen	Wird verwendet, um eine Kaskadensperre einzurichten
Exklusiv	Wird für Datenbearbeitungsoperationen wie INSERT, UPDATE, DELETE verwendet.
BULK-UPDATE	Wird beim Kopieren großer Datenmengen in eine Tabelle verwendet.
Schema	Wird verwendet, wenn der Vorgang vom Tabellenschema abhängt

Sperren aktualisieren

Diese Sperren vermeiden Deadlock-Situationen. Es serialisiert Transaktionen, die Transaktion liest die Daten, erwirbt eine gemeinsame Sperre für die Zeile oder Seite, und das Ändern der Daten erfordert das Umwandeln der Sperre in eine exklusive Sperre.

Gemeinsame Schlösser

Diese Sperren ermöglichen es parallelen Transaktionen, eine Ressource unter Parallelitätssteuerung zu lesen.

Gemeinsam genutzte Sperren geben Ressourcen frei, sobald ein Lesevorgang abgeschlossen ist, außer dass die Isolationsschicht einem wiederholten Lesevorgang oder höher zugewiesen ist.

Exklusive Schlösser

Diese Sperren verhindern den gleichzeitigen Zugriff auf Ressourcen innerhalb der Transaktion.

Durch die Verwendung der exklusiven Sperre kann keine Transaktion die Daten ändern, und der Lesevorgang wird nur über die nicht festgeschriebene Isolationsschicht oder den NOLOCK-Modus geplant.

DML-Anweisungen wie INSERT, UPDATE, DELETE werden verwendet, um Daten zu ändern.

Absichtssperren

Die Rolle der Intend-Sperre:

• Verhindert, dass andere Transaktionen Daten auf Ressourcen höherer Ebenen auf eine Weise ändern, die Sperren auf niedrigerer Ebene ungültig machen würde.
• um die Effizienz der Datenbank-Engine bei der Identifizierung von Schlüsselkonflikten mit höherer Granularität zu verbessern.

Liste der Beschreibungen der Absichtssperre:

Verriegelter Zustand	Bezeichnung
Gemeinsame Absicht (IS)	Für einige Ressourcen der unteren Ebene ist ein gemeinsam genutzter Sperrschutz erforderlich.
Absicht exklusiv (IX)	In einigen Ressourcen der unteren Ebene ist ein exklusiver Sperrschutz erforderlich. IX ist eine Obermenge (eine Menge von Mengen, die aus einer anderen Menge bestehen) von IS, die die gemeinsamen Sperren schützt, die auf der unteren Ressourcenschicht erforderlich sind.
Geteilt mit Intent Exclusive (SIX)	Der Schutz gemeinsam genutzter Sperren ist über Ressourcen hinweg erforderlich, die in der Hierarchie niedriger sind, und beabsichtigt exklusive Sperren in einigen Ressourcen der unteren Schicht. Gleichzeitige IS-Sperren (gleichzeitige IS-Sperren) sind für Ressourcen der obersten Ebene aktiviert.
Absichtsaktualisierung (IU)	Um die erforderlichen Sperren in allen Ressourcen der unteren Schicht zu schützen. IU-Sperren werden nur für Seitenressourcen verwendet. IU-Sperren werden in IX-Sperren konvertiert, wenn eine Aktualisierungsoperation durchgeführt wird.
Shared Intent Update (SIU)	Bietet eine Kombination aus S- und IU-Sperren, da separate Sperren erhalten und beide Sperren gleichzeitig gehalten werden.
Update-Intent exklusiv (UIX)	Bietet eine Kombination aus U- und IX-Sperren, da separate Sperren erhalten und beide Sperren gleichzeitig gehalten werden.

Bulk-Update-Sperren

Massenaktualisierungssperren werden verwendet, wenn eine große Datenmenge in die Tabelle kopiert wird. Diese Sperren ermöglichen die gleichzeitige Ausführung mehrerer Threads, um Massendaten nacheinander in eine Tabelle zu laden.

Schemasperren

Schemaänderungssperren werden in Datenbankmodul beim Ausführen von DDL-Vorgängen wie dem Löschen von Tabellen oder Spalten verwendet.

Schemastabilitätsschlüssel werden von der Datenbank-Engine beim Kompilieren und Ausführen von Abfragen verwendet.

Schlüsselbereichssperren

Diese Art von Sperren schützt die Liste der im RRset dargestellten Datensätze.

Schlüsselbereichssperren verhindern Phantomlesevorgänge. new stimmt mit der gesuchten Bedingung für Transaktion A überein. Wenn A dieselbe Bedingung erneut ausführt, erhält sie einen nicht einheitlichen Datensatz.)

Transaktionen verwalten

SQL Server implementiert Transaktionen mit unterschiedlichen Bereichen, die die ACID- Eigenschaften dieser Transaktionen garantieren.

In der Praxis bedeutet dies, Sperren als Grundlage für Transaktionen zu verwenden, um gemeinsam genutzte Datenbankressourcen abzufragen und Interferenzen zwischen Transaktionen zu verhindern.

Transaktionsprotokoll

Das Transaktionsprotokoll ist eine wichtige Komponente der Datenbank. Wenn das System abstürzt, stellt das Transaktionsprotokoll die Wiederherstellung der Daten in den richtigen Zustand sicher.

Das Transaktionsprotokoll sollte nicht gelöscht oder verschoben werden, bis der Benutzer seine Konsequenzen versteht.

Vom Transaktionsprotokoll unterstützte Operationen:

• Wiederherstellung einzelner Transaktionen.
• Rollback einer nicht abgeschlossenen Transaktion beim Start von SQL Server.
• Unterstützung für die Transaktionsreplikation
• Disaster-Recovery-Lösung zur Unterstützung von Systemen mit hohen Leistungsanforderungen.
• Stellen Sie Dateien, Datenbanken, Dateigruppen wieder her oder leiten Sie Seiten bis zum Fehlerpunkt weiter.

Transaktionsprotokoll kürzen

Durch das Abschneiden des Durchsuchungsprotokolls wird der von der Protokolldatei belegte Speicher freigegeben, um die Protokollierung fortzusetzen. Protokolle werden automatisch abgeschnitten, wenn die folgenden Ereignisse auftreten:

• In einem einfachen Wiederherstellungsmodell nach (Checkpoint) dem Checkpoint.
• Ein Modell der Massenwiederherstellung und der vollständigen Wiederherstellung, wenn seit der letzten Sicherung Checkpoing auftritt.

Wenn die Protokolle längere Zeit aktiv sind, wird das Transaktionsprotokoll verzögert und kann den Systemspeicher füllen. Das Abschneiden von Protokollen kann aus verschiedenen Gründen langsam sein. Benutzer können herausfinden, ob etwas das Abschneiden des Übertragungsprotokolls verhindert, indem sie die Spalten log_reuse_wait_desc und log_reuse_wait in der Katalogansicht von sys.databases abfragen.

Beschreibung des 2-spaltigen Wertes:

Log_reuse_wait	Log_reuse_wait_desc	Beschreiben
0	NICHTS	Gibt an, dass es mehr als eine wiederverwendbare virtuelle Protokolldatei darstellt
Erste	KONTROLLPUNKT	Stellt fest, dass seit der letzten Protokollkürzung keine Prüfpunkte aufgetreten sind oder dass der Protokolltitel nicht aus der virtuellen Protokolldatei verschoben wurde
2	LOG_BACKUP	Geben Sie die erforderliche Protokollsicherung an, bevor Sie die Protokollkürzung durchführen.
3	ACTIVE_BACKUP_OR_RESTORE	Gibt an, dass eine Sicherung oder Wiederherstellung ausgeführt wird.
4	ACTIVE_TRANSACTION	Bestimmt die aktive Transaktion.
5	DATABASE_MIRRORING	Bestimmt, dass die Datenbankspiegelung angehalten oder im Hochleistungsmodus ist, die Spiegeldatenbank befindet sich hinter der Hauptdatenbank