Een transactie in SQL Server is een opeenvolgende groep uitspraken of vragen om enkele of meerdere taken in een database uit te voeren. Elke transactie kan afzonderlijke lees-, schrijf-, update- of verwijderbewerkingen hebben, of een combinatie van al deze bewerkingen. Elke transactie moet twee dingen gebeuren in SQL Server:
- Ofwel is alle wijziging succesvol wanneer de transactie is vastgelegd.
- Of alle wijzigingen worden ongedaan gemaakt wanneer de transactie wordt teruggedraaid.
Een transactie kan pas succesvol zijn als alle bewerkingen in de set zijn voltooid. Het betekent dat als een argument mislukt, de transactie mislukt. Elke transactie begint met de eerste uitvoerbare SQL-instructie en eindigt wanneer er een commit of rollback wordt aangetroffen, expliciet of impliciet. Het maakt gebruik van de VERBINDEN of TERUGROLLEN uitspraken expliciet, maar ook impliciet wanneer een DDL-instructie wordt gebruikt.
De onderstaande afbeelding legt het transactieproces uit:
Het volgende voorbeeld legt het concept van een transactie uit:
In dit voorbeeld wordt het bankdatabasesysteem gebruikt om het concept van een transactie uit te leggen. Stel dat een bankklant geld van zijn rekening wil opnemen via de ATM-modus. De geldautomaat kan deze handeling in de drie stappen uitvoeren:
- De eerste stap is om de beschikbaarheid van het gevraagde bedrag op de rekening te controleren.
- De tweede stap trekt het bedrag van de rekening af als het bedrag beschikbaar is en werkt vervolgens het rekeningsaldo bij.
- De derde stap is om de geldopname in het logbestand te schrijven. Deze stap schrijft dat de transactie succesvol of mislukt is. Indien succesvol, schrijft u de gegevenswijziging in de database. Anders wordt de transactie teruggedraaid naar de vorige staat.
Het basisprincipe achter transacties is dat als een van de instructies een fout retourneert, de hele reeks wijzigingen wordt teruggedraaid om de gegevensintegriteit te garanderen. En als de transacties succesvol worden, zullen alle wijzigingen permanent in de database staan. Als er dus sprake is van een stroomstoring of andere problemen bij het opnemen van geld uit een geldautomaat, garanderen transacties dat ons saldo consistent blijft. Een transactieoverzicht voert deze bewerkingen het beste uit, omdat de vier belangrijkste eigenschappen van de transactie alle bewerkingen nauwkeuriger en consistenter maken. De vier eigenschappen van de transactie worden ACID genoemd.
Transactie-eigenschappen
De transactie-eigenschappen worden ACID-eigenschap (Atomiciteit, Consistentie, Isolatie, Duurzaamheid) genoemd, die hieronder in detail worden besproken:
Atomiciteit: Deze eigenschap zorgt ervoor dat alle instructies of bewerkingen die in de transactie zijn opgenomen, met succes moeten worden uitgevoerd. Anders wordt de hele transactie afgebroken en worden alle bewerkingen teruggezet naar hun vorige status wanneer een bewerking mislukt.
Samenhang: Deze eigenschap zorgt ervoor dat de database alleen van status verandert als een transactie met succes wordt doorgevoerd. Het is ook verantwoordelijk voor het beschermen van gegevens tegen crashes.
Isolatie: Deze eigenschap garandeert dat alle transacties geïsoleerd zijn van andere transacties, wat betekent dat elke bewerking in de transactie onafhankelijk wordt uitgevoerd. Het zorgt er ook voor dat uitspraken transparant zijn voor elkaar.
Duurzaamheid: Deze eigenschap garandeert dat het resultaat van vastgelegde transacties permanent in de database blijft bestaan, zelfs als het systeem crasht of faalt.
Transactiemodi in SQL Server
Er zijn drie verschillende transactiemodi die SQL Server kan gebruiken:
Transactiemodus automatisch vastleggen: Het is de standaardtransactiemodus van SQL Server. Het evalueert elke SQL-instructie als een transactie en de resultaten worden dienovereenkomstig vastgelegd of teruggedraaid. De succesvolle uitspraken worden dus onmiddellijk vastgelegd, terwijl de mislukte uitspraken onmiddellijk worden teruggedraaid.
Impliciete transactiemodus. In deze modus kan SQL Server de impliciete transactie voor elke DML-instructie starten, maar vereist dit expliciet het gebruik van commit- of rollback-opdrachten aan het einde van de instructies.
Expliciete transactiemodus: Deze modus wordt door de gebruiker gedefinieerd en stelt ons in staat het begin- en eindpunt van een transactie exact te identificeren. Het wordt automatisch afgebroken in geval van een fatale fout.
Transactiecontrole
Hieronder volgen de opdrachten die worden gebruikt om transacties te beheren:
OPMERKING: We kunnen alleen DML-instructies (INSERT, UPDATE en DELETE) gebruiken voor Transaction Control Language-opdrachten. We kunnen ze niet gebruiken bij het maken of verwijderen van tabellen, omdat deze bewerkingen automatisch in de database worden vastgelegd.
Transactiestatus
Het geeft aan hoe transacties gedurende hun levensduur verlopen. Het beschrijft de huidige status van de transactie en hoe de transactie in de toekomst zal worden verwerkt. Deze toestanden definiëren de regels die bepalen of een transactie wordt doorgevoerd of afgebroken.
Laten we elke transactiestatus in SQL Server beschrijven:
Actieve staat: De transactie bevindt zich in een actieve status terwijl de instructies van de transactie worden uitgevoerd. Het verandert in de 'gedeeltelijk toegewijde staat' als alle 'lees- en schrijf'-bewerkingen zonder fouten zijn voltooid. Als een instructie mislukt, verandert deze in de 'mislukte status'.
Gedeeltelijk toegewijd: Wanneer alle lees- en schrijfbewerkingen zijn voltooid, wordt de wijziging aangebracht in het hoofdgeheugen of de lokale buffer. De staat zou naar toe gaan 'geëngageerde staat' als de wijzigingen permanent in de database worden aangebracht. Anders gaat het naar de ‘failed state’.
als door Rudyard Kipling samenvatting
Mislukte staat: Een transactie gaat naar de status mislukt wanneer een transactie-instructie mislukt of een permanente wijziging in de database mislukt.
Afgebroken staat: De transactie gaat van a 'mislukte staat' aan een 'afgebroken staat' wanneer er zich een storing voordoet. De wijzigingen worden verwijderd of teruggedraaid omdat deze wijzigingen alleen worden aangebracht in de lokale buffer of het hoofdgeheugen in eerdere statussen.
Toegewijde staat: Een transactie is voltooid en krijgt deze status wanneer de wijzigingen permanent in de database worden aangebracht en in de database worden beëindigd 'beëindigde staat'.
Beëindigde staat: Als er geen terugdraaiing is en de transactie zich in de 'geëngageerde staat' het systeem is consistent en klaar voor een nieuwe transactie terwijl de oude wordt beëindigd.
Implementatie van transactie in SQL Server
Laten we enkele voorbeelden nemen om te begrijpen hoe we de transactie in SQL Server kunnen implementeren. Hier zullen we gebruik maken van de 'Product' tabel om alle transactiestatussen aan te tonen.
De volgende SQL-scripts maken de Product-tabel in de geselecteerde database:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Voer vervolgens de onderstaande scripts uit om gegevens in deze tabel in te voegen:
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);
Voer de SELECT-instructie uit om de gegevens te verifiëren:
Voorbeeld van COMMIT-transactie
Het is een goed idee om de SQL-instructies die in de transactie worden gebruikt, in meerdere logische delen te verdelen. En dan kunnen we beslissen of we de gegevens vastleggen of terugdraaien. De volgende stappen illustreren hoe u een transactie maakt:
- Start de transactie met behulp van de BEGIN TRANSACTIE commando.
- Schrijf de SQL-instructies en verdeel ze op basis van onze behoeften
- Gebruik de VERBINDEN verklaring om de transactie te voltooien en de wijzigingen permanent op te slaan.
Hieronder staan de opdrachten die de COMMIT-bewerkingen in SQL Server uitleggen:
-- 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
Als er geen fout wordt gevonden, zien we de volgende uitvoer waarbij elke SQL-transactieverklaring onafhankelijk wordt uitgevoerd:
De instructies INSERT en UPDATE kunnen niet worden teruggedraaid nadat de transactie is vastgelegd. Wanneer we de tabel na de commit-bewerking verifiëren, zien we de volgende gegevens:
Voorbeeld van een ROLLBACK-transactie
We zullen het ROLLBACK-commando gebruiken om transacties ongedaan te maken die nog niet in de database zijn opgeslagen en terug te keren naar het punt waar de transactie begon. In het volgende voorbeeld wordt de ROLLBACK-bewerking in SQL Server uitgelegd:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Zodra we de bovenstaande transactie hebben uitgevoerd, kunnen we zien dat deze met succes zal worden uitgevoerd. Het heeft echter geen invloed op eventuele wijzigingen in de database, want totdat we de COMMIT- of ROLLBACK-instructie niet uitvoeren, kunnen de wijzigingen niet permanent worden. Daarom hebben we de mogelijkheid om de ROLLBACK-transactieopdracht te gebruiken om alle databasebewerkingen terug te draaien. Hier is het volledige transactieoverzicht:
-- 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
Gebruik van @@Error Globale variabele in transacties:
Deze variabele is gebruikt om te controleren of er een fout is of niet. Het onderstaande voorbeeld legt het concept uit. Hier starten we eerst de transactie met behulp van het BEGIN-commando en schrijven vervolgens twee invoeginstructies. Vervolgens zullen we de globale systeemvariabele gebruiken @@FOUT in de ALS-verklaring om een fout te controleren. Als de waarde groter is dan 0, betekent dit dat er een fout is opgetreden. Nu wordt de transactie teruggedraaid; anders is de transactie vastgelegd.
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
Wanneer de bovenstaande transactie wordt uitgevoerd, zullen we merken dat deze is teruggedraaid. Dit komt door onze poging om een dubbele waarde in te voegen in de kolom Primaire sleutel.
Automatische terugdraaitransactie
De meeste transacties bevatten meer dan één zoekopdracht. Als tijdens het uitvoeren van de transactie een van de SQL-instructies een fout oplevert, vinden er geen wijzigingen plaats in de database en worden de overige instructies niet uitgevoerd. Dit concept staat bekend als een Auto Rollback-transactie in SQL Server. Laten we een eenvoudig voorbeeld gebruiken om dit proces te demonstreren.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Deze transactie levert de volgende uitvoer op:
In deze uitvoer kunnen we zien dat de insert-instructie met succes is uitgevoerd. Bij het uitvoeren van de update-instructie is echter een fout aangetroffen vanwege het probleem met de conversie van het gegevenstype. In dit geval staat de SQL Server geen wijzigingen in de database toe, wat betekent dat de invoegbewerking geen waarde toevoegt en de select-instructie niet wordt uitgevoerd.
Bewaarpunt in transacties
Het savepoint voegt een speciale markering toe aan een transactie waarmee we alle wijzigingen die na het savepoint zijn uitgevoerd, kunnen terugdraaien. Het wordt ook gebruikt om een specifiek deel van de transactie terug te draaien in plaats van de gehele transactie. We kunnen het definiëren door gebruik te maken van de TRANSACTIE OPSLAAN sp_naam stelling. In het volgende voorbeeld wordt het gebruik van savepoint uitgelegd in transacties die de insert-instructie vastleggen en de delete-instructie terugdraaien.
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;
Zie het onderstaande resultaat, waar we kunnen zien dat de product-ID 116 is verwijderd en 117 is ingevoegd in de eerste uitvoer. In de tweede uitvoer wordt de verwijderingsbewerking echter teruggedraaid vanwege het opslagpunt.
Hoe kan ik een savepoint vrijgeven in een transactie?
Release savepoint wordt gebruikt om het benoemde savepoint uit de huidige transactie te verwijderen zonder de resultaten van query's die na het savepoint zijn uitgevoerd, terug te draaien. MySQL heeft dit commando, maar SQL Server biedt geen commando om een savepoint vrij te geven. In plaats daarvan worden ze automatisch vrijgegeven aan het einde van een commit- of rollback-transactie, zodat we ons er tussentijds geen zorgen over hoeven te maken.
Impliciete transactie in SQL Server
We kunnen een impliciete transactie definiëren door de optie IMPLICIT_TRANSACTIONS in te schakelen. Het volgende voorbeeld zal dit concept eenvoudig uitleggen:
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
Bij deze transactie hebben we twee opties gebruikt @@OPTIE en @@TRANCOUNT. De @@OPTOPN biedt informatie over de huidige SET-opties, en @@TRANCOUNT biedt de BEGIN TRANSACTION-instructie in de huidige sessie.
Als u de transactie nu uitvoert, wordt de onderstaande uitvoer geretourneerd:
Expliciete transactie in SQL Server
Een expliciete transactie moet worden gedefinieerd via de opdracht BEGIN TRANSACTION, omdat deze het startpunt van de expliciete transactie identificeert. We kunnen de expliciete transactie in SQL Server als volgt definiëren:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
In de syntaxis geeft de optie trans_name een unieke naam van een transactie aan. De @trans_name_var geeft een door de gebruiker gedefinieerde variabele aan die de transactienaam opslaat. eindelijk, de MARKERING Met deze optie kunnen we een specifieke transactie in het logbestand markeren.
De expliciete transactie via de opdracht BEGIN TRANSACTION kreeg een vergrendeling afhankelijk van het isolatieniveau van de transactiegerelateerde bronnen. Het helpt vergrendelingsproblemen te verminderen. Zie het onderstaande voorbeeld:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Hier is de uitvoer:
Gemarkeerde transactie in SQL Server
De gemarkeerde transactie wordt gebruikt om een beschrijving toe te voegen aan een specifieke transactie in de logbestanden. We kunnen het gebruiken als herstelpunt in plaats van een datum en tijd bij het herstellen van een database naar een eerdere staat. We moeten weten dat de markering alleen in de logbestanden wordt toegevoegd wanneer de gemarkeerde transactie de database wijzigt. We kunnen het concept ervan begrijpen aan de hand van het volgende voorbeeld.
Stel dat we de database per ongeluk hebben gewijzigd en we weten niet het exacte moment van de gegevenswijziging; in dat geval kan het gegevensherstel lang duren. Als we echter de gemarkeerde transacties gebruiken, kan dit een handig hulpmiddel zijn om de exacte timing van gegevenswijzigingen te bepalen.
De volgende syntaxis illustreert de gemarkeerde transactie in SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Hier moeten we de naam van de transactie definiëren en vervolgens de optie MET MARK toevoegen. In het onderstaande voorbeeld gaan we records verwijderen en de markering toevoegen aan het logbestand:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
De logmarkgeschiedenis tabel is opgenomen in de msdb-database en slaat informatie op over elke gemarkeerde transactie die is gepleegd. Voer de onderstaande instructie uit om de details uit de logmarkhistory-tabel op te halen:
SELECT * FROM msdb.dbo.logmarkhistory
Benoemde transactie in SQL Server
We kunnen ook een naam opgeven voor onze transactie in SQL Server. Het wordt altijd aanbevolen om de benoemde transactie te gebruiken als u met veel transacties in één query werkt. In het onderstaande voorbeeld wordt uitgelegd hoe u de naam van een transactie kunt wijzigen:
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 is de uitvoer:
Conclusie
Dit artikel geeft een compleet overzicht van de transactie in SQL Server-instructies. Transacties zijn nuttig in relationele databasesystemen omdat ze de database-integriteit garanderen.