En transaktion i SQL Server är en sekventiell grupp av uttalanden eller frågor att utföra enstaka eller flera uppgifter i en databas. Varje transaktion kan ha enstaka läs-, skriv-, uppdaterings- eller raderingsoperationer eller en kombination av alla dessa operationer. Varje transaktion måste ske två saker i SQL Server:
när uppfanns skolan
- Antingen lyckas all modifiering när transaktionen är genomförd.
- Eller så ångras alla ändringar när transaktionen återställs.
En transaktion kan inte lyckas förrän alla operationer i uppsättningen är slutförda. Det betyder att om något argument misslyckas, kommer transaktionsoperationen att misslyckas. Varje transaktion börjar med den första körbara SQL-satsen och slutar när den hittar en commit eller rollback, antingen explicit eller implicit. Den använder BEGÅ eller RULLA TILLBAKA uttalanden explicit, såväl som implicit när en DDL-sats används.
Nedanstående bildrepresentation förklarar transaktionsprocessen:
Följande exempel kommer att förklara konceptet med en transaktion:
Detta exempel kommer att använda bankdatabasen för att förklara konceptet med en transaktion. Anta att en bankkund vill ta ut pengar från sitt konto genom att använda bankomatläget. Uttagsautomaten kan utföra denna operation i de tre stegen:
- De första steget är att kontrollera tillgängligheten för det begärda beloppet på kontot.
- De andra steg drar beloppet från kontot om beloppet finns tillgängligt och uppdaterar sedan kontosaldot.
- De tredje steget är att skriva pengauttagsoperationen i loggfilen. Detta steg skriver om att transaktionen antingen är framgångsrik eller misslyckad. Om det lyckas, skriv dataändringen i databasen. Annars kommer transaktionen att återställas till sitt tidigare tillstånd.
Grundprincipen bakom transaktioner är att om ett av påståendena returnerar ett fel, återställs hela uppsättningen av ändringar för att säkerställa dataintegriteten. Och om transaktionerna blir framgångsrika kommer alla ändringar att vara permanenta i databasen. Om det blir strömavbrott eller andra problem när du tar ut pengar från en bankomat, garanterar transaktioner att vårt saldo förblir konsekvent. En transaktionssats utför dessa operationer bäst eftersom transaktionens fyra nyckelegenskaper gör alla operationer mer exakta och konsekventa. Transaktionens fyra egenskaper kallas ACID.
Transaktionsegenskaper
Transaktionsegenskaperna kallas ACID (Atomicitet, Konsistens, Isolation, Durability), som diskuteras i detalj nedan:
Atomicitet: Den här egenskapen säkerställer att alla uttalanden eller operationer som ingår i transaktionen måste utföras framgångsrikt. Annars kommer hela transaktionen att avbrytas, och alla operationer rullas tillbaka till sitt tidigare tillstånd när någon operation misslyckas.
Konsistens: Den här egenskapen säkerställer att databasen ändrar status endast när en transaktion kommer att genomföras framgångsrikt. Det är också ansvarigt för att skydda data från krascher.
Isolering: Den här egenskapen garanterar att alla transaktioner är isolerade från andra transaktioner, vilket innebär att varje operation i transaktionen drivs oberoende. Det säkerställer också att uttalanden är transparenta för varandra.
Varaktighet: Den här egenskapen garanterar att resultatet av genomförda transaktioner kvarstår i databasen permanent även om systemet kraschar eller misslyckades.
Transaktionslägen i SQL Server
Det finns tre olika transaktionslägen som SQL Server kan använda:
Auto-commit transaktionsläge: Det är SQL Servers standardtransaktionsläge. Den kommer att utvärdera varje SQL-sats som en transaktion, och resultaten committeras eller rullas tillbaka i enlighet med detta. De framgångsrika uttalandena begås omedelbart, medan de misslyckade uttalandena omedelbart rullas tillbaka.
Implicit transaktionsläge. Det här läget tillåter SQL Server att påbörja den implicita transaktionen för varje DML-sats, men det kräver uttryckligen användning av commit- eller rollback-kommandon i slutet av satserna.
Explicit transaktionsläge: Detta läge definieras av användaren som gör att vi kan identifiera en transaktions start- och slutpunkter exakt. Den avbryts automatiskt vid ett allvarligt fel.
Transaktionskontroll
Följande är kommandon som används för att kontrollera transaktioner:
OBS: Vi kan endast använda DML-satser (INSERT, UPDATE och DELETE) för kommandon för Transaction Control Language. Vi kan inte använda dem när vi skapar eller släpper tabeller eftersom dessa operationer kopplas till databasen automatiskt.
Transaktionsstatus
Den indikerar hur transaktioner går under deras livstid. Den beskriver det aktuella tillståndet för transaktionen samt hur transaktionen kommer att behandlas i framtiden. Dessa stater definierar reglerna som avgör om en transaktion genomförs eller avbryts.
Låt oss beskriva varje transaktionstillstånd i SQL Server:
Aktivt tillstånd: Transaktionen är i ett aktivt tillstånd medan transaktionens instruktioner exekveras. Det ändras till 'delvis engagerad stat' om alla 'läs och skriv'-operationer slutförs utan fel. Om någon instruktion misslyckas ändras den till 'misslyckat tillstånd'.
Delvis engagerad: När alla läs- och skrivoperationer är slutförda görs ändringen till huvudminnet eller den lokala bufferten. Staten skulle gå till 'engagemang' om ändringarna görs permanenta i databasen. Annars går det till 'misslyckat tillstånd'.
Misslyckat tillstånd: En transaktion går till det misslyckade tillståndet när en transaktions instruktion misslyckas eller en permanent modifiering av databasen misslyckas.
Aborterad stat: Transaktionen flyttas från a 'misslyckat tillstånd' till en 'avbruten stat' när någon form av misslyckande inträffar. Ändringarna tas bort eller rullas tillbaka eftersom dessa ändringar endast görs i den lokala bufferten eller huvudminnet i tidigare tillstånd.
Engagerad stat: En transaktion är klar och går in i detta tillstånd när ändringarna görs permanenta i databasen och avslutas i 'avslutat tillstånd'.
Avslutat tillstånd: Om det inte finns någon återställning och transaktionen är i 'engagemang', systemet är konsekvent och redo för en ny transaktion medan den gamla avslutas.
Implementering av transaktion i SQL Server
Låt oss ta några exempel för att förstå hur vi kan implementera transaktionen i SQL Server. Här kommer vi att använda 'Produkt' tabell för att visa alla transaktionstillstånd.
Följande SQL-skript skapar produkttabellen i den valda databasen:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Kör sedan följande skript för att infoga data i den här tabellen:
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);
Utför SELECT-satsen för att verifiera data:
Exempel på COMMIT-transaktion
Det är en bra idé att dela upp SQL-satserna som används i transaktionen i flera logiska delar. Och sedan kan vi bestämma om vi ska överföra eller återställa data. Följande steg illustrerar hur du skapar en transaktion:
- Starta transaktionen med hjälp av BÖRJA TRANSAKTIONEN kommando.
- Skriv SQL-satserna och dela upp dem utifrån våra behov
- Använd BEGÅ uttalande för att slutföra transaktionen och spara ändringarna permanent.
Nedan är kommandona som förklarar COMMIT-operationerna i SQL Server:
-- 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
Om inget fel hittas kommer vi att se följande utdata där varje SQL-sats för transaktionen exekveras oberoende:
INSERT- och UPDATE-satserna kan inte återställas efter att transaktionen har genomförts. När vi verifierar tabellen efter commit-operation kommer vi att se följande data:
Exempel på ROLLBACK-transaktion
Vi kommer att använda kommandot ROLLBACK för att ångra alla transaktioner som ännu inte har sparats i databasen och återgå till den punkt där transaktionen började. Följande exempel förklarar ROLLBACK-operationen i 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
När vi har utfört transaktionen ovan kan vi se att den kommer att genomföras framgångsrikt. Det kommer dock inte att påverka några ändringar i databasen eftersom ändringarna inte kan bli permanenta förrän vi inte kör COMMIT eller ROLLBACK-satsen. Därför har vi möjlighet att använda transaktionskommandot ROLLBACK för att återställa alla databasoperationer. Här är hela transaktionsbeskrivningen:
-- 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
Användning av @@Error Global variabel i transaktioner:
Denna variabel är används för att kontrollera om det finns ett fel eller inte. Exemplet nedan förklarar dess koncept. Här kommer vi först att starta transaktionen med kommandot BEGIN och sedan skriva två insert-satser. Därefter kommer vi att använda den globala systemvariabeln @@FEL i IF uttalande för att kontrollera ett fel. Om värdet är större än 0 betyder det att det finns något fel. Nu är transaktionen återställd; annars är transaktionen genomförd.
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
När ovanstående transaktion utförs kommer vi att märka att den har återställts. Det beror på vårt försök att infoga ett duplicerat värde i kolumnen Primärnyckel.
Automatisk återställningstransaktion
De flesta av transaktionerna innehåller mer än en fråga. När transaktionen körs, om någon av SQL-satserna ger ett fel, sker inga ändringar i databasen och de återstående satserna exekveras inte. Detta koncept är känt som en automatisk återställningstransaktion i SQL Server. Låt oss använda ett enkelt exempel för att demonstrera denna process.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Denna transaktion ger följande utdata:
I denna utdata kan vi se att insert-satsen kördes framgångsrikt. Men när uppdateringssatsen kördes hittades ett fel på grund av problemet med datatypskonvertering. I det här fallet tillåter inte SQL Server några ändringar i databasen, vilket innebär att infogningsoperationen inte tillför något värde och select-satsen exekveras inte.
Sparpunkt i transaktioner
Sparpunkten infogar ett speciellt märke i en transaktion som gör att vi kan återställa alla ändringar som utförs efter sparpunkten. Det används också för att rulla tillbaka någon specifik del av transaktionen snarare än hela transaktionen. Vi kan definiera det genom att använda SPARA TRANSACTION sp_name påstående. Följande exempel kommer att förklara användningen av savepoint i transaktioner som utför insert-satsen och återställer delete-satsen.
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;
Se resultatet nedan där vi kan se produkt-id 116 raderas och 117 infogas i den första utgången. Men i den andra utgången rullas borttagningsoperationen tillbaka på grund av sparpunkten.
Hur släpper man en räddningspunkt i en transaktion?
Släpp räddningspunkt används för att ta bort den namngivna räddningspunkten från den aktuella transaktionen utan att återställa resultaten av förfrågningar som körs efter räddningspunkten. MySQL har det här kommandot, men SQL Server tillhandahåller inget kommando för att frigöra en räddningspunkt. Istället släpps de automatiskt i slutet av en commit- eller återställningstransaktion, så vi behöver inte oroa oss för dem i mellantiden.
Implicit transaktion i SQL Server
Vi kan definiera en implicit transaktion genom att aktivera alternativet IMPLICIT_TRANSACTIONS. Följande exempel förklarar detta koncept enkelt:
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
I den här transaktionen har vi använt två alternativ @@OPTION och @@TRANCOUNT. @@OPTOPN tillhandahåller information om aktuella SET-alternativ, och @@TRANCOUNT tillhandahåller BEGIN TRANSACTION-satsen i den aktuella sessionen.
När transaktionen nu utförs returneras följande utdata:
Explicit transaktion i SQL Server
En explicit transaktion måste definieras genom kommandot BEGIN TRANSACTION eftersom den identifierar startpunkten för den explicita transaktionen. Vi kan definiera den explicita transaktionen i SQL Server enligt nedan:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
I syntaxen indikerar alternativet trans_name ett unikt namn på en transaktion. De @trans_name_var indikerar en användardefinierad variabel som lagrar transaktionsnamnet. Slutligen, den MARK alternativet låter oss markera en specifik transaktion i loggfilen.
Den explicita transaktionen genom kommandot BEGIN TRANSACTION fick ett lås beroende på isoleringsnivån för de transaktionsrelaterade resurserna. Det hjälper till att minska låsproblem. Se exemplet nedan:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Här är utgången:
Markerad transaktion i SQL Server
Den markerade transaktionen används för att lägga till en beskrivning av en specifik transaktion i loggfilerna. Vi kan använda den som en återställningspunkt i stället för ett datum och en tid när vi återställer en databas till ett tidigare tillstånd. Vi måste veta att märket läggs till i loggfilerna endast när den markerade transaktionen ändrar databasen. Vi kan förstå dess koncept med följande exempel.
Anta att vi har modifierat databasen av misstag, och vi vet inte det exakta ögonblicket för dataändringen; i så fall kan dataåterställning ta lång tid. Men om vi använder de markerade transaktionerna kan det vara ett användbart verktyg för att bestämma den exakta tidpunkten för dataändringar.
Följande syntax illustrerar den markerade transaktionen i SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Här måste vi definiera namnet på transaktionen och sedan lägga till WITH MARK alternativet. I exemplet nedan kommer vi att ta bort poster och lägga till märket i loggfilen:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
De logmarkhistoria tabellen ingår i msdb databas och lagrar information om varje märkt transaktion som har begåtts. Utför satsen nedan för att få detaljerna från logmarkhistory-tabellen:
SELECT * FROM msdb.dbo.logmarkhistory
Namngiven Transaktion i SQL Server
Vi kan också ge ett namn för vår transaktion i SQL Server. Det rekommenderas alltid att använda den namngivna transaktionen när du arbetar med många transaktioner i en enda fråga. Exemplet nedan förklarar hur man byter namn på en transaktion:
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
Här är utgången:
Slutsats
Den här artikeln kommer att ge en fullständig översikt över transaktionen i SQL Server-satser. Transaktioner är användbara i relationsdatabassystem eftersom de säkerställer databasens integritet.