Transakcia na serveri SQL Server je a sekvenčná skupina príkazov alebo dotazov na vykonávanie jednej alebo viacerých úloh v databáze. Každá transakcia môže mať jednu operáciu čítania, zápisu, aktualizácie alebo vymazania alebo kombináciu všetkých týchto operácií. Každá transakcia sa musí na serveri SQL Server uskutočniť dvoma spôsobmi:
- Buď sú všetky úpravy úspešné, keď je transakcia potvrdená.
- Alebo sa všetky úpravy vrátia späť, keď sa transakcia vráti späť.
Transakcia nemôže byť úspešná, kým sa nedokončia všetky operácie v sade. Znamená to, že ak niektorý argument zlyhá, transakcia zlyhá. Každá transakcia začína prvým spustiteľným príkazom SQL a končí, keď nájde potvrdenie alebo vrátenie, či už explicitne alebo implicitne. Používa sa ZAMESTNAŤ SA alebo ROLLBACK explicitne, ako aj implicitne pri použití príkazu DDL.
Nižšie uvedené obrázkové znázornenie vysvetľuje proces transakcie:
Nasledujúci príklad vysvetlí koncept transakcie:
bash inak ak
Tento príklad použije bankový databázový systém na vysvetlenie pojmu transakcia. Predpokladajme, že zákazník banky chce vybrať peniaze zo svojho účtu pomocou režimu bankomatu. Bankomat môže túto operáciu vykonať v troch krokoch:
- The Prvý krok je skontrolovať dostupnosť požadovanej sumy na účte.
- The druhý krok odpočíta sumu z účtu, ak je suma k dispozícii, a potom aktualizuje zostatok na účte.
- The tretí krok je zapísať operáciu výberu peňazí do súboru denníka. Tento krok píše o úspešnej alebo neúspešnej transakcii. V prípade úspechu zapíšte úpravu údajov do databázy. V opačnom prípade sa transakcia vráti do predchádzajúceho stavu.
Základným princípom transakcií je, že ak jeden z príkazov vráti chybu, celá sada zmien sa vráti späť, aby sa zabezpečila integrita údajov. A ak budú transakcie úspešné, všetky zmeny budú v databáze trvalé. Ak teda dôjde k výpadku prúdu alebo iným problémom pri výbere peňazí z bankomatu, transakcie zaručia, že náš zostatok zostane konzistentný. Transakčný výpis najlepšie vykonáva tieto operácie, pretože štyri kľúčové vlastnosti transakcie robia všetky operácie presnejšími a konzistentnejšími. Štyri vlastnosti transakcie sa označujú ako ACID.
Vlastnosti transakcie
Vlastnosti transakcie sa označujú ako vlastnosť ACID (Atomicity, Consistency, Isolation, Durability), o ktorých sa podrobne hovorí nižšie:
Atómovosť: Táto vlastnosť zabezpečuje, že všetky príkazy alebo operácie zahrnuté v transakcii musia byť úspešne vykonané. V opačnom prípade bude celá transakcia prerušená a všetky operácie sa vrátia späť do predchádzajúceho stavu, keď zlyhá akákoľvek operácia.
Konzistencia: Táto vlastnosť zaisťuje, že databáza zmení stav iba vtedy, keď bude transakcia úspešne potvrdená. Je tiež zodpovedný za ochranu údajov pred pádmi.
Izolácia: Táto vlastnosť zaručuje, že všetky transakcie sú izolované od ostatných transakcií, čo znamená, že každá operácia v transakcii prebieha nezávisle. Zabezpečuje tiež, že vyhlásenia sú navzájom transparentné.
Trvanlivosť: Táto vlastnosť zaručuje, že výsledok potvrdených transakcií zostane v databáze trvalo, aj keď systém zlyhá alebo zlyhá.
Režimy transakcií na serveri SQL Server
Existujú tri rôzne režimy transakcií, ktoré môže SQL Server použiť:
Režim transakcie automatického potvrdenia: Je to predvolený transakčný režim servera SQL. Každý príkaz SQL vyhodnotí ako transakciu a výsledky sa podľa toho potvrdí alebo vrátia späť. Úspešné príkazy sú teda okamžite potvrdené, zatiaľ čo neúspešné príkazy sú okamžite vrátené späť.
Režim implicitnej transakcie. Tento režim umožňuje serveru SQL Server začať implicitnú transakciu pre každý príkaz DML, ale explicitne vyžaduje použitie príkazov na potvrdenie alebo vrátenie na konci príkazov.
Explicitný režim transakcie: Tento režim je definovaný používateľom, ktorý nám umožňuje presne identifikovať začiatok a koniec transakcie. V prípade fatálnej chyby sa automaticky preruší.
Kontrola transakcií
Nasledujú príkazy používané na riadenie transakcií:
POZNÁMKA: Pre príkazy jazyka Transaction Control Language môžeme použiť iba príkazy DML (INSERT, UPDATE a DELETE). Nemôžeme ich použiť pri vytváraní alebo rušení tabuliek, pretože tieto operácie sa do databázy zadávajú automaticky.
Stav transakcie
Udáva, ako prebiehajú transakcie počas ich životnosti. Popisuje aktuálny stav transakcie, ako aj spôsob spracovania transakcie v budúcnosti. Tieto stavy definujú pravidlá, ktoré určujú, či sa transakcia potvrdí alebo preruší.
Poďme popísať jednotlivé stavy transakcií na serveri SQL Server:
Aktívny stav: Počas vykonávania pokynov transakcie je transakcia v aktívnom stave. Zmení sa na „čiastočne angažovaný štát“ ak sú všetky operácie „čítania a zápisu“ dokončené bez chýb. Ak ktorákoľvek inštrukcia zlyhá, zmení sa na „stav zlyhania“.
Čiastočne odovzdané: Po dokončení všetkých operácií čítania a zápisu sa vykoná zmena v hlavnej pamäti alebo lokálnej vyrovnávacej pamäti. Štát by išiel do 'oddaný štát' ak sú zmeny v databáze trvalé. V opačnom prípade prejde do „stavu zlyhania“.
Neúspešný stav: Transakcia prejde do neúspešného stavu, keď zlyhá akákoľvek inštrukcia transakcie alebo zlyhá trvalá úprava v databáze.
Prerušený stav: Transakcia sa presúva z a 'zlyhaný stav' do an 'aborted state' keď dôjde k akýmkoľvek poruchám. Zmeny sa odstránia alebo vrátia späť, pretože tieto zmeny sa v predchádzajúcich stavoch vykonajú iba v lokálnej vyrovnávacej pamäti alebo hlavnej pamäti.
zeenat aman herec
Zaviazaný štát: Transakcia je dokončená a prejde do tohto stavu, keď sú zmeny v databáze trvalé a ukončené v „ukončený stav“.
Ukončený stav: Ak nedôjde k návratu a transakcia je v 'oddaný štát', systém je konzistentný a pripravený na novú transakciu, zatiaľ čo stará je ukončená.
Implementácia transakcie v SQL Server
Uveďme si niekoľko príkladov, aby sme pochopili, ako môžeme implementovať transakciu na serveri SQL Server. Tu použijeme 'produkt' tabuľku na zobrazenie všetkých stavov transakcií.
Nasledujúce skripty SQL vytvoria tabuľku Produkt vo vybranej databáze:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Potom vykonajte nasledujúce skripty na vloženie údajov do tejto tabuľky:
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);
Vykonajte príkaz SELECT na overenie údajov:
Príklad transakcie COMMIT
Príkazy SQL použité v transakcii je vhodné rozdeliť do viacerých logických častí. A potom sa môžeme rozhodnúť, či údaje potvrdíme alebo vrátime späť. Nasledujúce kroky ilustrujú vytvorenie transakcie:
objekt json v jazyku Java
- Začnite transakciu pomocou ZAČAŤ TRANSAKCIU príkaz.
- Napíšte SQL príkazy a rozdeľte ich podľa našich potrieb
- Použi ZAMESTNAŤ SA na dokončenie transakcie a trvalé uloženie zmien.
Nižšie sú uvedené príkazy, ktoré vysvetľujú operácie COMMIT v 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
Ak sa nenájde žiadna chyba, uvidíme nasledujúci výstup, kde sa každý príkaz SQL transakcie vykoná nezávisle:
Príkazy INSERT a UPDATE sa po potvrdení transakcie nedajú vrátiť späť. Keď overíme tabuľku po operácii potvrdenia, uvidíme nasledujúce údaje:
Príklad transakcie ROLLBACK
Príkaz ROLLBACK použijeme na vrátenie všetkých transakcií, ktoré ešte neboli uložené do databázy, a návrat do bodu, kde transakcia začala. Nasledujúci príklad vysvetľuje operáciu ROLLBACK v 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
Po vykonaní vyššie uvedenej transakcie vidíme, že bude úspešne vykonaná. Neovplyvní to však žiadne zmeny v databáze, pretože kým nevykonáme príkaz COMMIT alebo ROLLBACK, zmeny sa nemôžu stať trvalými. Preto máme možnosť použiť transakčný príkaz ROLLBACK na vrátenie všetkých databázových operácií. Tu je úplný výpis z transakcie:
-- 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
Použitie globálnej premennej @@Error v transakciách:
Táto premenná je slúži na kontrolu, či je chyba alebo nie. Nasledujúci príklad vysvetľuje jeho koncept. Tu najskôr spustíme transakciu pomocou príkazu BEGIN a potom napíšeme dva príkazy insert. Ďalej použijeme premennú globálneho systému @@CHYBA v vyhlásenie IF skontrolovať chybu. Ak je hodnota väčšia ako 0, znamená to, že došlo k chybe. Teraz sa transakcia vráti späť; inak je transakcia potvrdená.
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
Po vykonaní vyššie uvedenej transakcie si všimneme, že bola vrátená späť. Je to kvôli nášmu pokusu vložiť duplicitnú hodnotu do stĺpca Primárny kľúč.
Transakcia automatického vrátenia
Väčšina transakcií obsahuje viac ako jeden dopyt. Ak pri vykonávaní transakcie niektorý z príkazov SQL spôsobí chybu, v databáze sa nevykonajú žiadne úpravy a zvyšné príkazy sa nevykonajú. Tento koncept je známy ako automatická transakcia vrátenia zmien na serveri SQL Server. Na demonštráciu tohto procesu použijeme jednoduchý príklad.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Táto transakcia produkuje nasledujúci výstup:
javascript onload skript
V tomto výstupe môžeme vidieť, že príkaz insert bol vykonaný úspešne. Pri vykonávaní príkazu na aktualizáciu sa však našla chyba v dôsledku problému s konverziou typu údajov. V tomto prípade SQL Server nepovoľuje žiadne zmeny v databáze, čo znamená, že operácia vloženia nepridáva žiadnu hodnotu a príkaz select sa nevykoná.
Bod uloženia v Transakciách
Bod uloženia vkladá do transakcie špeciálnu značku, ktorá nám umožňuje vrátiť späť všetky zmeny vykonané po bode uloženia. Používa sa tiež na vrátenie akejkoľvek konkrétnej časti transakcie, nie celej transakcie. Môžeme ho definovať pomocou ULOŽIŤ TRANSAKCIU sp_name vyhlásenie. Nasledujúci príklad vysvetlí použitie bodu uloženia v transakciách, ktoré potvrdí príkaz insert a vráti príkaz delete.
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;
Pozrite si nižšie uvedený výsledok, kde vidíme, že ID produktu 116 je odstránené a 117 je vložené do prvého výstupu. V druhom výstupe sa však operácia vymazania vráti späť kvôli bodu uloženia.
Ako uvoľniť bod uloženia v transakcii?
Miesto uloženia uvoľnenia sa používa na odstránenie pomenovaného bodu uloženia z aktuálnej transakcie bez vrátenia výsledkov dotazov vykonaných po bode uloženia. MySQL má tento príkaz, ale SQL Server neposkytuje žiadny príkaz na uvoľnenie bodu uloženia. Namiesto toho sú automaticky uvoľnené na konci transakcie potvrdenia alebo vrátenia, takže sa o ne nemusíme starať.
Implicitná transakcia na serveri SQL Server
Implicitnú transakciu môžeme definovať povolením možnosti IMPLICIT_TRANSACTIONS. Nasledujúci príklad tento pojem jednoducho vysvetlí:
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
V tejto transakcii sme použili dve možnosti @@OPTION a @@TRANCOUNT. @@OPTOPN poskytuje informácie týkajúce sa aktuálnych možností SET a @@TRANCOUNT poskytuje príkaz BEGIN TRANSACTION v aktuálnej relácii.
Teraz vykonanie transakcie vráti nasledujúci výstup:
Explicitná transakcia na serveri SQL Server
Explicitná transakcia musí byť definovaná prostredníctvom príkazu BEGIN TRANSACTION, pretože identifikuje počiatočný bod explicitnej transakcie. Môžeme definovať explicitnú transakciu v SQL Server, ako je uvedené nižšie:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
Voľba trans_name v syntaxi označuje jedinečný názov transakcie. The @trans_name_var označuje používateľom definovanú premennú, v ktorej je uložený názov transakcie. Nakoniec, MARK možnosť nám umožňuje označiť konkrétnu transakciu v súbore denníka.
pripojenia v jave
Explicitná transakcia prostredníctvom príkazu BEGIN TRANSACTION získala zámok v závislosti od úrovne izolácie zdrojov súvisiacich s transakciou. Pomáha znižovať problémy so zámkom. Pozrite si nasledujúci príklad:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Tu je výstup:
Označená transakcia na serveri SQL Server
Označená transakcia sa používa na pridanie popisu ku konkrétnej transakcii v protokolových súboroch. Môžeme ho použiť ako bod obnovy namiesto dátumu a času pri obnove databázy do predchádzajúceho stavu. Musíme vedieť, že značka sa do protokolových súborov pridá iba vtedy, keď označená transakcia upraví databázu. Jeho koncept môžeme pochopiť pomocou nasledujúceho príkladu.
Predpokladajme, že sme omylom upravili databázu a nepoznáme presný moment zmeny údajov; v takom prípade môže obnova údajov trvať dlho. Ak však použijeme označené transakcie, môže to byť užitočný nástroj na určenie presného načasovania zmien údajov.
Nasledujúca syntax znázorňuje označenú transakciu na serveri SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Tu musíme definovať názov transakcie a potom pridať možnosť WITH MARK. V nižšie uvedenom príklade vymažeme záznamy a pridáme značku do súboru denníka:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory tabuľka je súčasťou databázy msdb a uchováva informácie týkajúce sa každej označenej transakcie, ktorá bola potvrdená. Vykonajte nižšie uvedené vyhlásenie, aby ste získali podrobnosti z tabuľky logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Pomenovaná transakcia na serveri SQL Server
Môžeme tiež poskytnúť názov našej transakcie na serveri SQL Server. Pri práci s mnohými transakciami v jednom dotaze sa vždy odporúča použiť pomenovanú transakciu. Nasledujúci príklad vysvetľuje, ako premenovať transakciu:
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
Tu je výstup:
Záver
Tento článok poskytne úplný prehľad transakcií v príkazoch SQL Server. Transakcie sú užitočné v relačných databázových systémoch, pretože zabezpečujú integritu databázy.