Eine Transaktion in SQL Server ist eine sequentielle Gruppe von Anweisungen oder Abfragen um einzelne oder mehrere Aufgaben in einer Datenbank auszuführen. Jede Transaktion kann einzelne Lese-, Schreib-, Aktualisierungs- oder Löschvorgänge oder eine Kombination aller dieser Vorgänge umfassen. Bei jeder Transaktion müssen in SQL Server zwei Dinge geschehen:
- Entweder sind alle Änderungen erfolgreich, wenn die Transaktion festgeschrieben wird.
- Oder alle Änderungen werden rückgängig gemacht, wenn die Transaktion zurückgesetzt wird.
Eine Transaktion kann erst erfolgreich sein, wenn alle Vorgänge im Satz abgeschlossen sind. Das bedeutet, dass der Transaktionsvorgang fehlschlägt, wenn ein Argument fehlschlägt. Jede Transaktion beginnt mit der ersten ausführbaren SQL-Anweisung und endet, wenn sie explizit oder implizit ein Commit oder Rollback findet. Es nutzt die BEGEHEN oder ROLLBACK Anweisungen explizit sowie implizit, wenn eine DDL-Anweisung verwendet wird.
Die folgende bildliche Darstellung erläutert den Transaktionsprozess:
Das folgende Beispiel soll das Konzept einer Transaktion erläutern:
In diesem Beispiel wird das Bankendatenbanksystem verwendet, um das Konzept einer Transaktion zu erläutern. Angenommen, ein Bankkunde möchte im Geldautomatenmodus Geld von seinem Konto abheben. Der Geldautomat kann diesen Vorgang in drei Schritten durchführen:
- Der erster Schritt besteht darin, die Verfügbarkeit des angeforderten Betrags auf dem Konto zu prüfen.
- Der zweiter Schritt bucht den Betrag vom Konto ab, wenn der Betrag verfügbar ist, und aktualisiert dann den Kontostand.
- Der dritter Schritt besteht darin, den Geldabhebungsvorgang in die Protokolldatei zu schreiben. Dieser Schritt gibt an, dass die Transaktion entweder erfolgreich oder fehlgeschlagen ist. Bei Erfolg schreiben Sie die Datenänderung in die Datenbank. Andernfalls wird die Transaktion in ihren vorherigen Zustand zurückgesetzt.
Das Grundprinzip von Transaktionen besteht darin, dass, wenn eine der Anweisungen einen Fehler zurückgibt, der gesamte Änderungssatz zurückgesetzt wird, um die Datenintegrität sicherzustellen. Und wenn die Transaktionen erfolgreich sind, werden alle Änderungen dauerhaft in der Datenbank gespeichert. Sollte es also beim Abheben von Geld an einem Geldautomaten zu einem Stromausfall oder anderen Problemen kommen, garantieren Transaktionen, dass unser Guthaben konstant bleibt. Eine Transaktionsanweisung führt diese Vorgänge am besten aus, da die vier Schlüsseleigenschaften der Transaktion alle Vorgänge genauer und konsistenter machen. Die vier Eigenschaften der Transaktion werden als ACID bezeichnet.
Transaktionseigenschaften
Die Transaktionseigenschaften werden als ACID-Eigenschaften (Atomicity, Consistency, Isolation, Durability) bezeichnet, die im Folgenden ausführlich erläutert werden:
Atomarität: Diese Eigenschaft stellt sicher, dass alle in der Transaktion enthaltenen Anweisungen oder Vorgänge erfolgreich ausgeführt werden müssen. Andernfalls wird die gesamte Transaktion abgebrochen und alle Vorgänge werden in ihren vorherigen Zustand zurückgesetzt, wenn ein Vorgang fehlschlägt.
Konsistenz: Diese Eigenschaft stellt sicher, dass der Datenbankstatus nur dann geändert wird, wenn eine Transaktion erfolgreich festgeschrieben wird. Es ist auch für den Schutz der Daten vor Abstürzen verantwortlich.
Isolierung: Diese Eigenschaft garantiert, dass alle Transaktionen von anderen Transaktionen isoliert sind, was bedeutet, dass jeder Vorgang in der Transaktion unabhängig ausgeführt wird. Es stellt außerdem sicher, dass die Aussagen untereinander transparent sind.
Haltbarkeit: Diese Eigenschaft garantiert, dass das Ergebnis festgeschriebener Transaktionen dauerhaft in der Datenbank erhalten bleibt, auch wenn das System abstürzt oder ausfällt.
Transaktionsmodi in SQL Server
Es gibt drei verschiedene Transaktionsmodi, die SQL Server verwenden kann:
Word-Symbolleiste für den Schnellzugriff
Auto-Commit-Transaktionsmodus: Dies ist der Standardtransaktionsmodus des SQL Servers. Es wertet jede SQL-Anweisung als Transaktion aus und die Ergebnisse werden entsprechend festgeschrieben oder zurückgesetzt. Daher werden die erfolgreichen Anweisungen sofort festgeschrieben, während die fehlgeschlagenen Anweisungen sofort zurückgesetzt werden.
Impliziter Transaktionsmodus. Dieser Modus ermöglicht es SQL Server, die implizite Transaktion für jede DML-Anweisung zu starten, erfordert jedoch explizit die Verwendung von Commit- oder Rollback-Befehlen am Ende der Anweisungen.
Explizite Transaktionsmodus: Dieser Modus wird vom Benutzer definiert und ermöglicht es uns, den Anfangs- und Endpunkt einer Transaktion genau zu identifizieren. Im Falle eines schwerwiegenden Fehlers wird der Vorgang automatisch abgebrochen.
Transaktionskontrolle
Die folgenden Befehle werden zur Steuerung von Transaktionen verwendet:
HINWEIS: Wir können nur DML-Anweisungen (INSERT, UPDATE und DELETE) für Transaction Control Language-Befehle verwenden. Wir können sie beim Erstellen oder Löschen von Tabellen nicht verwenden, da diese Vorgänge automatisch in der Datenbank festgeschrieben werden.
Transaktionsstatus
Es gibt an, wie Transaktionen während ihrer Lebensdauer ablaufen. Es beschreibt den aktuellen Status der Transaktion sowie die zukünftige Abwicklung der Transaktion. Diese Zustände definieren die Regeln, die bestimmen, ob eine Transaktion festgeschrieben oder abgebrochen wird.
Lassen Sie uns jeden Transaktionsstatus in SQL Server beschreiben:
Aktiver Zustand: Die Transaktion befindet sich in einem aktiven Zustand, während die Anweisungen der Transaktion ausgeführt werden. Es ändert sich zum „teilweise engagierter Staat“ wenn alle Lese- und Schreibvorgänge fehlerfrei abgeschlossen wurden. Wenn eine Anweisung fehlschlägt, wechselt sie in den Status „fehlgeschlagen“.
Teilweise engagiert: Wenn alle Lese- und Schreibvorgänge abgeschlossen sind, erfolgt die Änderung im Hauptspeicher oder lokalen Puffer. Der Staat würde gehen 'verpflichteter Staat' wenn die Änderungen dauerhaft in der Datenbank vorgenommen werden. Andernfalls geht es in den „fehlgeschlagenen Zustand“ über.
Gescheiterter Staat: Eine Transaktion geht in den Status „Fehlgeschlagen“ über, wenn die Anweisung einer Transaktion fehlschlägt oder eine dauerhafte Änderung in der Datenbank fehlschlägt.
Abgebrochener Zustand: Die Transaktion bewegt sich von a 'gescheiterter Staat' zu einem „abgebrochener Zustand“ wenn irgendeine Art von Fehler auftritt. Die Änderungen werden entfernt oder rückgängig gemacht, da diese Änderungen in vorherigen Zuständen nur am lokalen Puffer oder Hauptspeicher vorgenommen wurden.
Zeichenfolge in C++
Beteiligter Staat: Eine Transaktion ist abgeschlossen und geht in diesen Zustand über, wenn die Änderungen dauerhaft in der Datenbank vorgenommen und in der Datenbank beendet werden 'beendeter Zustand'.
Beendeter Zustand: Wenn kein Rollback erfolgt und die Transaktion in der 'festgelegter Staat' Das System ist konsistent und bereit für eine neue Transaktion, während die alte beendet wird.
Implementierung der Transaktion in SQL Server
Nehmen wir einige Beispiele, um zu verstehen, wie wir die Transaktion in SQL Server implementieren können. Hier verwenden wir die 'Produkt' Tabelle zur Darstellung aller Transaktionsstatus.
Die folgenden SQL-Skripte erstellen die Produkttabelle in der ausgewählten Datenbank:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Führen Sie als Nächstes die folgenden Skripte aus, um Daten in diese Tabelle einzufügen:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Führen Sie die SELECT-Anweisung aus, um die Daten zu überprüfen:
Beispiel einer COMMIT-Transaktion
Es empfiehlt sich, die in der Transaktion verwendeten SQL-Anweisungen in mehrere logische Teile zu unterteilen. Und dann können wir entscheiden, ob wir die Daten festschreiben oder zurücksetzen. Die folgenden Schritte veranschaulichen das Erstellen einer Transaktion:
- Starten Sie die Transaktion mit TRANSAKTION BEGINNEN Befehl.
- Schreiben Sie die SQL-Anweisungen und teilen Sie sie entsprechend unseren Anforderungen auf
- Benutzen Sie die BEGEHEN -Anweisung, um die Transaktion abzuschließen und die Änderungen dauerhaft zu speichern.
Nachfolgend finden Sie die Befehle, die die COMMIT-Operationen in SQL Server erläutern:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Wenn kein Fehler gefunden wird, sehen wir die folgende Ausgabe, in der jede SQL-Anweisung der Transaktion unabhängig ausgeführt wird:
Die INSERT- und UPDATE-Anweisungen können nicht zurückgesetzt werden, nachdem die Transaktion festgeschrieben wurde. Wenn wir die Tabelle nach dem Festschreibungsvorgang überprüfen, sehen wir die folgenden Daten:
Wo sind die Browsereinstellungen?
Beispiel einer ROLLBACK-Transaktion
Mit dem ROLLBACK-Befehl machen wir alle Transaktionen rückgängig, die noch nicht in der Datenbank gespeichert wurden, und kehren zu dem Punkt zurück, an dem die Transaktion begonnen hat. Das folgende Beispiel erläutert die ROLLBACK-Operation in SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Sobald wir die obige Transaktion ausgeführt haben, können wir sehen, dass sie erfolgreich ausgeführt wird. Es wirkt sich jedoch nicht auf Änderungen in der Datenbank aus, da die Änderungen nicht dauerhaft werden können, solange wir die COMMIT- oder ROLLBACK-Anweisung nicht ausführen. Daher haben wir die Möglichkeit, den Transaktionsbefehl ROLLBACK zu verwenden, um alle Datenbankoperationen rückgängig zu machen. Hier ist die vollständige Transaktionsabrechnung:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Verwendung der globalen Variablen @@Error in Transaktionen:
Diese Variable ist Wird verwendet, um zu überprüfen, ob ein Fehler vorliegt oder nicht. Das folgende Beispiel erläutert das Konzept. Hier starten wir zunächst die Transaktion mit dem BEGIN-Befehl und schreiben dann zwei Insert-Anweisungen. Als nächstes verwenden wir die globale Systemvariable @@FEHLER im IF-Anweisung einen Fehler überprüfen. Wenn der Wert größer als 0 ist, liegt ein Fehler vor. Jetzt wird die Transaktion zurückgesetzt. andernfalls wird die Transaktion festgeschrieben.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Wenn die obige Transaktion ausgeführt wird, werden wir feststellen, dass sie zurückgesetzt wurde. Dies liegt an unserem Versuch, einen doppelten Wert in die Primärschlüsselspalte einzufügen.
Automatische Rollback-Transaktion
Die meisten Transaktionen enthalten mehr als eine Abfrage. Wenn während der Ausführung der Transaktion eine der SQL-Anweisungen einen Fehler erzeugt, werden keine Änderungen in der Datenbank vorgenommen und die übrigen Anweisungen werden nicht ausgeführt. Dieses Konzept wird in SQL Server als Auto-Rollback-Transaktion bezeichnet. Lassen Sie uns diesen Prozess anhand eines einfachen Beispiels veranschaulichen.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Diese Transaktion erzeugt die folgende Ausgabe:
In dieser Ausgabe können wir sehen, dass die Insert-Anweisung erfolgreich ausgeführt wurde. Beim Ausführen der Update-Anweisung wurde jedoch ein Fehler aufgrund des Datentypkonvertierungsproblems festgestellt. In diesem Fall lässt der SQL Server keine Änderungen in der Datenbank zu, was bedeutet, dass der Einfügevorgang keinen Wert hinzufügt und die Select-Anweisung nicht ausgeführt wird.
Speicherpunkt in Transaktionen
Der Sicherungspunkt fügt eine spezielle Markierung in eine Transaktion ein, die es uns ermöglicht, alle nach dem Sicherungspunkt ausgeführten Änderungen rückgängig zu machen. Es wird auch verwendet, um einen bestimmten Teil der Transaktion und nicht die gesamte Transaktion rückgängig zu machen. Wir können es definieren, indem wir die verwenden TRANSAKTION SPEICHERN sp_name Stellungnahme. Das folgende Beispiel erläutert die Verwendung von Savepoint in Transaktionen, bei denen die Einfügeanweisung festgeschrieben und die Löschanweisung zurückgesetzt wird.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Sehen Sie sich das folgende Ergebnis an, in dem wir sehen können, dass die Produkt-ID 116 gelöscht und 117 in die erste Ausgabe eingefügt wurde. In der zweiten Ausgabe wird der Löschvorgang jedoch aufgrund des Sicherungspunkts rückgängig gemacht.
Wie gebe ich einen Sicherungspunkt in einer Transaktion frei?
Der Release-Savepoint wird verwendet, um den benannten Savepoint aus der aktuellen Transaktion zu entfernen, ohne die Ergebnisse der nach dem Savepoint ausgeführten Abfragen rückgängig zu machen. MySQL verfügt über diesen Befehl, SQL Server stellt jedoch keinen Befehl zum Freigeben eines Sicherungspunkts bereit. Stattdessen werden sie am Ende einer Commit- oder Rollback-Transaktion automatisch freigegeben, sodass wir uns zwischenzeitlich nicht um sie kümmern müssen.
Implizite Transaktion in SQL Server
Wir können eine implizite Transaktion definieren, indem wir die Option IMPLICIT_TRANSACTIONS aktivieren. Das folgende Beispiel soll dieses Konzept leicht erklären:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Bei dieser Transaktion haben wir zwei Optionen genutzt @@OPTION und @@TRANCOUNT. @@OPTOPN stellt Informationen zu den aktuellen SET-Optionen bereit und @@TRANCOUNT stellt die BEGIN TRANSACTION-Anweisung in der aktuellen Sitzung bereit.
Wenn Sie nun die Transaktion ausführen, wird die folgende Ausgabe zurückgegeben:
Explizite Transaktion in SQL Server
Eine explizite Transaktion muss über den Befehl BEGIN TRANSACTION definiert werden, da er den Startpunkt der expliziten Transaktion identifiziert. Wir können die explizite Transaktion in SQL Server wie folgt definieren:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
In der Syntax gibt die Option trans_name einen eindeutigen Namen einer Transaktion an. Der @trans_name_var gibt eine benutzerdefinierte Variable an, die den Transaktionsnamen speichert. Endlich, das MARKIEREN Mit dieser Option können wir eine bestimmte Transaktion in der Protokolldatei markieren.
Die explizite Transaktion über den Befehl BEGIN TRANSACTION hat abhängig von der Isolationsstufe der transaktionsbezogenen Ressourcen eine Sperre erworben. Es hilft, Sperrprobleme zu reduzieren. Sehen Sie sich das folgende Beispiel an:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Hier ist die Ausgabe:
Markierte Transaktion in SQL Server
Die markierte Transaktion wird verwendet, um einer bestimmten Transaktion in den Protokolldateien eine Beschreibung hinzuzufügen. Wir können ihn als Wiederherstellungspunkt anstelle von Datum und Uhrzeit verwenden, wenn wir eine Datenbank in einen früheren Zustand zurückversetzen. Wir müssen wissen, dass die Markierung nur dann in den Protokolldateien hinzugefügt wird, wenn die markierte Transaktion die Datenbank ändert. Wir können sein Konzept anhand des folgenden Beispiels verstehen.
Angenommen, wir haben die Datenbank versehentlich geändert und kennen den genauen Zeitpunkt der Datenänderung nicht. In diesem Fall kann die Datenwiederherstellung lange dauern. Wenn wir jedoch die markierten Transaktionen verwenden, kann dies ein nützliches Werkzeug zur Bestimmung des genauen Zeitpunkts von Datenänderungen sein.
Die folgende Syntax veranschaulicht die markierte Transaktion in SQL Server:
Beispiel für eine Java-Karte
BEGIN TRANSACTION trans_name WITH MARK 'description';
Hier müssen wir den Namen der Transaktion definieren und dann die Option WITH MARK hinzufügen. Im folgenden Beispiel löschen wir Datensätze und fügen die Markierung in der Protokolldatei hinzu:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
Der logmarkhistory Die Tabelle ist im Lieferumfang enthalten msdb-Datenbank und speichert Informationen zu jeder markierten Transaktion, die festgeschrieben wurde. Führen Sie die folgende Anweisung aus, um die Details aus der Logmarkhistory-Tabelle abzurufen:
SELECT * FROM msdb.dbo.logmarkhistory
Benannte Transaktion in SQL Server
Wir können unserer Transaktion auch einen Namen in SQL Server geben. Es wird immer empfohlen, die benannte Transaktion zu verwenden, wenn Sie mit vielen Transaktionen in einer einzelnen Abfrage arbeiten. Das folgende Beispiel erklärt, wie eine Transaktion umbenannt wird:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Hier ist die Ausgabe:
Abschluss
Dieser Artikel gibt einen vollständigen Überblick über die Transaktion in SQL Server-Anweisungen. Transaktionen sind in relationalen Datenbanksystemen hilfreich, da sie die Datenbankintegrität sicherstellen.