En transaktion i SQL Server er en sekventiel gruppe af udsagn eller forespørgsler at udføre enkelte eller flere opgaver i en database. Hver transaktion kan have en enkelt læse-, skrive-, opdaterings- eller sletningsoperation eller en kombination af alle disse operationer. Hver transaktion skal ske to ting i SQL Server:
- Enten lykkes alle ændringer, når transaktionen er forpligtet.
- Eller alle ændringer fortrydes, når transaktionen rulles tilbage.
En transaktion kan ikke lykkes, før alle handlingerne i sættet er afsluttet. Det betyder, at hvis et argument mislykkes, vil transaktionsoperationen mislykkes. Hver transaktion begynder med den første eksekverbare SQL-sætning og slutter, når den finder en commit eller rollback, enten eksplicit eller implicit. Den bruger BEGÅ eller TILBAGE udsagn eksplicit, såvel som implicit, når en DDL-sætning bruges.
Nedenstående billedgengivelse forklarer transaktionsprocessen:
Følgende eksempel vil forklare begrebet en transaktion:
Dette eksempel vil bruge bankdatabasesystemet til at forklare begrebet en transaktion. Antag, at en bankkunde ønsker at hæve penge fra deres konto ved at bruge ATM-tilstand. Hæveautomaten kan udføre denne operation i de tre trin:
- Det første skridt er at kontrollere tilgængeligheden af det ønskede beløb på kontoen.
- Det andet trin trækker beløbet fra kontoen, hvis beløbet er til rådighed og opdaterer derefter kontosaldoen.
- Det tredje trin er at skrive pengehævningsoperationen i logfilen. Dette trin skriver om, at transaktionen enten er vellykket eller mislykket. Hvis det lykkes, skal du skrive dataændringen i databasen. Ellers vil transaktionen blive rullet tilbage til sin tidligere tilstand.
Det grundlæggende princip bag transaktioner er, at hvis et af udsagn returnerer en fejl, rulles hele sæt ændringer tilbage for at sikre dataintegritet. Og hvis transaktionerne lykkes, vil alle ændringer være permanente på databasen. Derfor, hvis der er strømafbrydelse eller andre problemer, når du hæver penge fra en hæveautomat, garanterer transaktioner, at vores saldo forbliver konsistent. En transaktionserklæring udfører disse operationer bedst, fordi transaktionens fire nøgleegenskaber gør alle operationer mere nøjagtige og konsistente. Transaktionens fire egenskaber omtales som ACID.
Transaktionsegenskaber
Transaktionsegenskaberne omtales som ACID (Atomicitet, Konsistens, Isolation, Durability) ejendom, som diskuteres i detaljer nedenfor:
Atomicitet: Denne egenskab sikrer, at alle udsagn eller operationer, der er inkluderet i transaktionen, skal udføres korrekt. Ellers vil hele transaktionen blive afbrudt, og alle operationer rulles tilbage til deres tidligere tilstand, når en operation mislykkes.
Konsistens: Denne egenskab sikrer, at databasens tilstand kun ændres, når en transaktion vil blive gennemført med succes. Den er også ansvarlig for at beskytte data mod nedbrud.
Isolation: Denne ejendom garanterer, at alle transaktioner er isoleret fra andre transaktioner, hvilket betyder, at hver operation i transaktionen udføres uafhængigt. Det sikrer også, at udsagn er gennemsigtige for hinanden.
Holdbarhed: Denne egenskab garanterer, at resultatet af forpligtede transaktioner forbliver i databasen permanent, selvom systemet går ned eller fejler.
forårsstøvle annotationer
Transaktionstilstande i SQL Server
Der er tre forskellige transaktionstilstande, som SQL Server kan bruge:
Auto-commit Transaktionstilstand: Det er SQL Servers standardtransaktionstilstand. Det vil evaluere hver SQL-sætning som en transaktion, og resultaterne forpligtes eller rulles tilbage i overensstemmelse hermed. Således begås de vellykkede udsagn straks, mens de mislykkede udsagn straks rulles tilbage.
Implicit transaktionstilstand. Denne tilstand tillader SQL Server at starte den implicitte transaktion for hver DML-sætning, men den kræver eksplicit brugen af commit- eller rollback-kommandoer i slutningen af sætningerne.
Eksplicit transaktionstilstand: Denne tilstand er defineret af brugeren, som giver os mulighed for at identificere en transaktions start- og slutpunkter nøjagtigt. Det vil automatisk afbryde i tilfælde af en fatal fejl.
Transaktionskontrol
Følgende er de kommandoer, der bruges til at kontrollere transaktioner:
BEMÆRK: Vi kan kun bruge DML-sætninger (INSERT, UPDATE og DELETE) til kommandoer i Transaction Control Language. Vi kan ikke bruge dem, mens vi opretter eller sletter tabeller, fordi disse operationer er forpligtet til databasen automatisk.
Transaktionstilstand
Det angiver, hvordan transaktioner forløber i løbet af deres levetid. Den beskriver den aktuelle status for transaktionen, samt hvordan transaktionen vil blive behandlet i fremtiden. Disse stater definerer reglerne, der bestemmer, om en transaktion forpligter eller afbrydes.
Lad os beskrive hver transaktionstilstand i SQL Server:
Aktiv tilstand: Transaktionen er i en aktiv tilstand, mens transaktionens instruktioner udføres. Det ændrer sig til 'delvist forpligtet stat' hvis alle 'læse og skriv'-operationer udføres uden fejl. Hvis en instruktion mislykkes, ændres den til 'mislykket tilstand'.
Delvist engageret: Når alle læse- og skriveoperationer er afsluttet, foretages ændringen til hovedhukommelsen eller den lokale buffer. Staten ville gå til 'forpligtet tilstand' hvis ændringerne er permanente i databasen. Ellers går det til 'mislykket tilstand'.
Mislykket tilstand: En transaktion går til den mislykkede tilstand, når en transaktions instruktion mislykkes, eller en permanent ændring af databasen mislykkes.
Aborteret stat: Transaktionen flytter fra en 'mislykket tilstand' til en 'afbrudt tilstand' når nogen form for fejl opstår. Ændringerne fjernes eller rulles tilbage, fordi disse ændringer kun foretages til den lokale buffer eller hovedhukommelse i tidligere tilstande.
Forpligtet stat: En transaktion er fuldført og går i denne tilstand, når ændringerne gøres permanente på databasen og afsluttes i 'termineret tilstand'.
Afsluttet tilstand: Hvis der ikke er nogen tilbagerulning, og transaktionen er i 'forpligtet stat' systemet er konsistent og klar til en ny transaktion, mens den gamle afsluttes.
Implementering af Transaktion i SQL Server
Lad os tage nogle eksempler for at forstå, hvordan vi kan implementere transaktionen i SQL Server. Her vil vi bruge 'Produkt' tabel for at vise alle transaktionstilstande.
Følgende SQL-scripts opretter produkttabellen i den valgte database:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Udfør derefter nedenstående scripts for at indsætte data i denne tabel:
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);
Udfør SELECT-sætningen for at bekræfte dataene:
Eksempel på COMMIT-transaktion
Det er en god idé at opdele de SQL-sætninger, der bruges i transaktionen, i flere logiske dele. Og så kan vi beslutte, om vi vil forpligte eller rulle dataene tilbage. Følgende trin illustrerer at oprette en transaktion:
- Start transaktionen ved hjælp af START TRANSAKTIONEN kommando.
- Skriv SQL-sætningerne og opdel dem ud fra vores behov
- Brug BEGÅ erklæring for at fuldføre transaktionen og gemme ændringerne permanent.
Nedenfor er kommandoerne, der forklarer COMMIT-handlingerne 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
Hvis der ikke findes nogen fejl, vil vi se følgende output, hvor hver SQL-sætning af transaktionen udføres uafhængigt:
INSERT- og UPDATE-sætningerne kan ikke rulles tilbage, efter at transaktionen er blevet gennemført. Når vi verificerer tabellen efter commit-operation, vil vi se følgende data:
Eksempel på ROLLBACK-transaktion
Vi vil bruge kommandoen ROLLBACK til at fortryde alle transaktioner, der endnu ikke er blevet gemt i databasen, og vende tilbage til det punkt, hvor transaktionen begyndte. Følgende eksempel forklarer ROLLBACK-handlingen 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 udført ovenstående transaktion, kan vi se, at den vil blive eksekveret med succes. Det vil dog ikke påvirke nogen ændringer i databasen, for indtil vi ikke udfører COMMIT eller ROLLBACK-sætningen, kan ændringerne ikke blive permanente. Derfor har vi mulighed for at bruge ROLLBACK-transaktionskommandoen til at rulle alle databaseoperationer tilbage. Her er den fulde transaktionsopgørelse:
-- 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
Brug af @@Error Global variabel i transaktioner:
Denne variabel er bruges til at kontrollere, om der er en fejl eller ej. Nedenstående eksempel forklarer konceptet. Her vil vi først starte transaktionen ved at bruge BEGIN-kommandoen og derefter skrive to insert-sætninger. Dernæst vil vi bruge den globale systemvariabel @@FEJL i IF erklæring for at kontrollere en fejl. Hvis værdien er større end 0, betyder det, at der er en fejl. Nu er transaktionen rollback; ellers er transaktionen forpligtet.
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 ovenstående transaktion er udført, vil vi bemærke, at den er blevet rullet tilbage. Det skyldes vores forsøg på at indsætte en duplikatværdi i kolonnen Primærnøgle.
Automatisk tilbagerulningstransaktion
De fleste af transaktionerne indeholder mere end én forespørgsel. Hvis nogen af SQL-sætningerne producerer en fejl under udførelse af transaktionen, sker der ingen ændringer i databasen, og de resterende sætninger udføres ikke. Dette koncept er kendt som en automatisk rollback-transaktion i SQL Server. Lad os bruge et simpelt eksempel til at demonstrere denne proces.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Denne transaktion producerer følgende output:
I dette output kan vi se, at insert-sætningen blev udført med succes. Men udførelse af opdateringssætningen fandt en fejl på grund af datatypekonverteringsproblemet. I dette tilfælde tillader SQL Server ingen ændringer i databasen, hvilket betyder, at indsættelsesoperationen ikke tilføjer nogen værdi, og select-sætningen udføres ikke.
Gempunkt i transaktioner
Savepointet indsætter et særligt mærke i en transaktion, der giver os mulighed for at rulle tilbage alle ændringer, der er udført efter savepoint. Det bruges også til at rulle enhver specifik del af transaktionen tilbage i stedet for hele transaktionen. Vi kan definere det ved at bruge GEM TRANSAKTION sp_navn udmelding. Følgende eksempel vil forklare brugen af savepoint i transaktioner, der begår insert-sætningen og tilbagefører delete-sætningen.
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 nedenstående resultat, hvor vi kan se produkt-id 116 er slettet, og 117 er indsat i det første output. Men i det andet output rulles sletningsoperationen tilbage på grund af lagringspunktet.
Hvordan frigiver man et savepoint i en transaktion?
Frigivelse af lagringspunkt bruges til at fjerne det navngivne lagringspunkt fra den aktuelle transaktion uden at tilbageføre resultaterne af forespørgsler udført efter lagringspunktet. MySQL har denne kommando, men SQL Server giver ikke nogen kommando til at frigive et savepoint. I stedet frigives de automatisk i slutningen af en commit- eller rollback-transaktion, så vi behøver ikke at bekymre os om dem i mellemtiden.
Implicit transaktion i SQL Server
Vi kan definere en implicit transaktion ved at aktivere indstillingen IMPLICIT_TRANSACTIONS. Følgende eksempel vil nemt forklare dette koncept:
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 denne transaktion har vi brugt to muligheder @@OPTION og @@TRANCOUNT. @@OPTOPN giver information om aktuelle SET-indstillinger, og @@TRANCOUNT giver BEGIN TRANSACTION-sætningen i den aktuelle session.
Nu vil udførelse af transaktionen returnere nedenstående output:
Eksplicit transaktion i SQL Server
En eksplicit transaktion skal defineres gennem kommandoen BEGIN TRANSACTION, fordi den identificerer startpunktet for den eksplicitte transaktion. Vi kan definere den eksplicitte transaktion i SQL Server som nedenfor:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
I syntaksen angiver indstillingen trans_name et unikt navn på en transaktion. Det @trans_name_var angiver en brugerdefineret variabel, der gemmer transaktionsnavnet. Endelig MÆRKE mulighed giver os mulighed for at markere en specifik transaktion i logfilen.
Den eksplicitte transaktion gennem kommandoen BEGIN TRANSACTION opnåede en lås afhængigt af isolationsniveauet for de transaktionsrelaterede ressourcer. Det hjælper med at reducere låseproblemer. Se nedenstående eksempel:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Her er outputtet:
erstatte fra streng i java
Markeret transaktion i SQL Server
Den markerede transaktion bruges til at tilføje en beskrivelse til en specifik transaktion i logfilerne. Vi kan bruge det som et gendannelsespunkt i stedet for en dato og et klokkeslæt, når vi gendanner en database til en tidligere tilstand. Vi skal vide, at mærket kun tilføjes i logfilerne, når den markerede transaktion ændrer databasen. Vi kan forstå dets koncept ved at bruge følgende eksempel.
Antag, at vi har ændret databasen ved et uheld, og vi kender ikke det nøjagtige tidspunkt for dataændringen; i så fald kan datagendannelse tage lang tid. Men hvis vi bruger de markerede transaktioner, kan det være et nyttigt værktøj til at bestemme den nøjagtige timing af dataændringer.
Følgende syntaks illustrerer den markerede transaktion i SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Her skal vi definere navnet på transaktionen og derefter tilføje MED MÆRK mulighed. I nedenstående eksempel skal vi slette poster og tilføje mærket i logfilen:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
Det logmærkehistorie tabel er inkluderet i msdb database og gemmer information om hver markerede transaktion, der er blevet begået. Udfør nedenstående erklæring for at få detaljerne fra logmarkhistory-tabellen:
SELECT * FROM msdb.dbo.logmarkhistory
Navnet Transaktion i SQL Server
Vi kan også give et navn til vores transaktion i SQL Server. Det anbefales altid at bruge den navngivne transaktion, når du arbejder med mange transaktioner i en enkelt forespørgsel. Nedenstående eksempel forklarer, hvordan man omdøber 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
Her er outputtet:
Konklusion
Denne artikel vil give et komplet overblik over transaktionen i SQL Server-sætninger. Transaktioner er nyttige i relationelle databasesystemer, fordi de sikrer databaseintegritet.