Transakcija v SQL Server je a zaporedno skupino stavkov ali poizvedb za izvajanje ene ali več nalog v bazi podatkov. Vsaka transakcija ima lahko eno operacijo branja, pisanja, posodabljanja ali brisanja ali kombinacijo vseh teh operacij. Vsaka transakcija se mora v strežniku SQL zgoditi dve stvari:
- Ali so vse spremembe uspešne, ko je transakcija potrjena.
- Ali pa so vse spremembe razveljavljene, ko je transakcija povrnjena.
Transakcija ne more biti uspešna, dokler niso dokončane vse operacije v nizu. To pomeni, da če kateri koli argument ne uspe, transakcijska operacija ne bo uspela. Vsaka transakcija se začne s prvim izvedljivim stavkom SQL in konča, ko najde potrditev ali povrnitev, eksplicitno ali implicitno. Uporablja ZAVEZI oz POVRAT stavkov eksplicitno, pa tudi implicitno, ko je uporabljen stavek DDL.
Spodnji slikovni prikaz pojasnjuje postopek transakcije:
Naslednji primer pojasnjuje koncept transakcije:
Ta primer bo za razlago koncepta transakcije uporabil sistem bančne baze podatkov. Recimo, da želi stranka banke dvigniti denar s svojega računa v načinu bankomata. Bankomat lahko izvede to operacijo v treh korakih:
- The Prvi korak je preveriti razpoložljivost zahtevanega zneska na računu.
- The drugi korak odtegne znesek z računa, če je znesek na voljo, in nato posodobi stanje na računu.
- The tretji korak je zapisati operacijo dviga denarja v dnevniško datoteko. Ta korak piše, da je transakcija uspešna ali neuspešna. Če je uspešen, zapišite spremembo podatkov v bazo podatkov. V nasprotnem primeru bo transakcija povrnjena v prejšnje stanje.
Osnovno načelo transakcij je, da če eden od stavkov vrne napako, se celoten niz sprememb povrne nazaj, da se zagotovi celovitost podatkov. In če bodo transakcije uspešne, bodo vse spremembe v bazi podatkov trajne. Če torej pride do izpada električne energije ali drugih težav pri dvigu denarja na bankomatu, transakcije zagotavljajo, da naše stanje ostane dosledno. Izjava o transakciji najbolje izvaja te operacije, ker štiri ključne lastnosti transakcije naredijo vse operacije natančnejše in doslednejše. Štiri lastnosti transakcije se imenujejo ACID.
Lastnosti transakcije
Lastnosti transakcije se imenujejo lastnost ACID (Atomicity, Consistency, Isolation, Durability), ki je podrobneje obravnavana spodaj:
Atomičnost: Ta lastnost zagotavlja, da morajo biti vsi stavki ali operacije, vključene v transakcijo, uspešno izvedene. V nasprotnem primeru bo celotna transakcija prekinjena in vse operacije se povrnejo v prejšnje stanje, ko katera koli operacija ne uspe.
Konsistentnost: Ta lastnost zagotavlja, da baza podatkov spremeni stanje le, ko bo transakcija uspešno opravljena. Odgovoren je tudi za zaščito podatkov pred zrušitvami.
tipkopis foreach
Izolacija: Ta lastnost zagotavlja, da so vse transakcije ločene od drugih transakcij, kar pomeni, da se vsaka operacija v transakciji izvaja neodvisno. Zagotavlja tudi, da so izjave med seboj pregledne.
Vzdržljivost: Ta lastnost zagotavlja, da rezultat odobrenih transakcij trajno ostane v bazi podatkov, tudi če se sistem zruši ali odpove.
Transakcijski načini v SQL Serverju
Obstajajo trije različni načini transakcij, ki jih lahko uporablja SQL Server:
Samodejni način transakcije: To je privzeti transakcijski način strežnika SQL. Vsak stavek SQL bo ovrednotil kot transakcijo, rezultati pa bodo ustrezno odobreni ali povrnjeni. Tako so uspešni stavki takoj odobreni, medtem ko so neuspeli stavki takoj povrnjeni.
Način implicitne transakcije. Ta način omogoča SQL Serverju, da začne implicitno transakcijo za vsak stavek DML, vendar izrecno zahteva uporabo ukazov za potrditev ali povrnitev na koncu stavkov.
Eksplicitni način transakcije: Ta način določi uporabnik, kar nam omogoča natančno identifikacijo začetne in končne točke transakcije. V primeru usodne napake se samodejno prekine.
Nadzor transakcij
Za nadzor transakcij se uporabljajo naslednji ukazi:
OPOMBA: Za ukaze Transaction Control Language lahko uporabljamo samo stavke DML (INSERT, UPDATE in DELETE). Ne moremo jih uporabiti med ustvarjanjem ali izpuščanjem tabel, ker so te operacije samodejno predane bazi podatkov.
Stanje transakcije
Prikazuje, kako potekajo transakcije v času trajanja. Opisuje trenutno stanje transakcije in tudi, kako bo transakcija obdelana v prihodnosti. Ta stanja definirajo pravila, ki določajo, ali se transakcija potrdi ali prekine.
Naj opišemo vsako stanje transakcije v strežniku SQL:
Aktivno stanje: Transakcija je v aktivnem stanju, medtem ko se izvajajo navodila transakcije. Spremeni se v 'delno predano stanje' če so vse operacije branja in pisanja zaključene brez napak. Če katero koli navodilo ne uspe, se spremeni v 'neuspešno stanje.'
Delno predano: Ko so vse operacije branja in pisanja zaključene, se izvede sprememba v glavnem pomnilniku ali lokalnem medpomnilniku. Država bi šla na 'zavezana država' če so spremembe trajne v bazi podatkov. V nasprotnem primeru gre v 'failed state'.
Neuspešno stanje: Transakcija preide v neuspešno stanje, ko katero koli navodilo transakcije ne uspe ali trajna sprememba baze podatkov ne uspe.
Prekinjeno stanje: Transakcija se premakne iz a 'propadla država' do an 'prekinjeno stanje' ko pride do kakršne koli okvare. Spremembe so odstranjene ali povrnjene, ker so te spremembe narejene samo v lokalnem medpomnilniku ali glavnem pomnilniku v prejšnjih stanjih.
Predano stanje: Transakcija je dokončana in preide v to stanje, ko so spremembe v bazi podatkov trajne in končane v 'prekinjeno stanje'.
Končano stanje: Če ni povratka in je transakcija v 'predana država,' sistem je konsistenten in pripravljen na novo transakcijo, medtem ko je stara prekinjena.
Implementacija transakcije v SQL Server
Vzemimo nekaj primerov, da bomo razumeli, kako lahko izvedemo transakcijo v SQL Server. Tukaj bomo uporabili 'Izdelek' tabelo za prikaz vseh stanj transakcije.
Naslednji skripti SQL ustvarijo tabelo izdelkov v izbrani bazi podatkov:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Nato izvedite spodnje skripte, da vstavite podatke v to tabelo:
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);
Izvedite stavek SELECT, da preverite podatke:
kaj je poseben znak
Primer transakcije COMMIT
Dobro je razdeliti izjave SQL, uporabljene v transakciji, na več logičnih delov. Nato se lahko odločimo, ali bomo podatke potrdili ali povrnili. Naslednji koraki ponazarjajo ustvarjanje transakcije:
- Začnite transakcijo z uporabo ZAČNI TRANSAKCIJO ukaz.
- Napišite stavke SQL in jih razdelite glede na naše potrebe
- Uporabi ZAVEZI izjavo za dokončanje transakcije in trajno shranjevanje sprememb.
Spodaj so ukazi, ki pojasnjujejo operacije COMMIT v strežniku 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
Če napaka ni najdena, bomo videli naslednji izhod, kjer se vsak stavek SQL transakcije izvede neodvisno:
Stavkov INSERT in UPDATE po potrditvi transakcije ni mogoče povrniti nazaj. Ko preverimo tabelo po operaciji potrditve, bomo videli naslednje podatke:
Primer transakcije ROLLBACK
Z ukazom ROLLBACK bomo razveljavili vse transakcije, ki še niso bile shranjene v bazi podatkov, in se vrnili na točko, kjer se je transakcija začela. Naslednji primer pojasnjuje operacijo ROLLBACK v strežniku 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
Ko izvedemo zgornjo transakcijo, lahko vidimo, da bo uspešno izvedena. Vendar pa ne bo vplivalo na nobene spremembe v bazi podatkov, ker dokler ne izvedemo stavka COMMIT ali ROLLBACK, spremembe ne morejo postati trajne. Zato imamo možnost uporabe transakcijskega ukaza ROLLBACK za povrnitev vseh operacij baze podatkov. Tukaj je celotna izjava o transakciji:
-- 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
Uporaba globalne spremenljivke @@Error v transakcijah:
Ta spremenljivka je uporablja za preverjanje, ali je napaka ali ne. Spodnji primer pojasnjuje njegov koncept. Tukaj bomo najprej začeli transakcijo z ukazom BEGIN in nato napisali dva stavka za vstavljanje. Nato bomo uporabili globalno sistemsko spremenljivko @@NAPAKA v izjava IF da preverite napako. Če je vrednost večja od 0, to pomeni, da je prišlo do napake. Zdaj je transakcija povrnjena; sicer je transakcija odobrena.
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
Ko bo zgornja transakcija izvedena, bomo opazili, da je bila povrnjena nazaj. To je posledica našega poskusa vstavljanja podvojene vrednosti v stolpec Primarni ključ.
Samodejna povrnitev transakcije
Večina transakcij vsebuje več kot eno poizvedbo. Če kateri od stavkov SQL med izvajanjem transakcije povzroči napako, v bazi podatkov ne pride do nobenih sprememb in preostali stavki se ne izvedejo. Ta koncept je znan kot transakcija samodejnega povrnitve v SQL Server. Za prikaz tega postopka uporabimo preprost primer.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Ta transakcija ustvari naslednje rezultate:
V tem izhodu lahko vidimo, da je bil stavek vstavljanja uspešno izveden. Vendar je pri izvajanju stavka za posodobitev prišlo do napake zaradi težave s pretvorbo podatkovnega tipa. V tem primeru strežnik SQL ne dovoljuje nobenih sprememb v bazi podatkov, kar pomeni, da operacija vstavljanja ne doda nobene vrednosti in stavek select se ne izvede.
Točka shranjevanja v transakcijah
Točka shranjevanja v transakcijo vstavi posebno oznako, ki nam omogoča povrnitev vseh sprememb, izvedenih po točki shranjevanja. Uporablja se tudi za povrnitev katerega koli določenega dela transakcije namesto celotne transakcije. Lahko ga definiramo z uporabo SHRANI TRANSAKCIJO sp_name izjava. Naslednji primer bo razložil uporabo točke shranjevanja v transakcijah, ki potrdijo stavek vstavi in povrnejo stavek za brisanje.
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;
Oglejte si spodnji rezultat, kjer lahko vidimo, da je ID izdelka 116 izbrisan in 117 vstavljen v prvi izhod. Vendar pa je v drugem izhodu operacija brisanja povrnjena nazaj zaradi točke shranjevanja.
Kako sprostiti točko shranjevanja v transakciji?
Sprostitev točke shranjevanja se uporablja za odstranitev imenovane točke shranjevanja iz trenutne transakcije brez razveljavitve rezultatov poizvedb, izvedenih po točki shranjevanja. MySQL ima ta ukaz, vendar SQL Server ne zagotavlja nobenega ukaza za sprostitev točke shranjevanja. Namesto tega se samodejno sprostijo ob koncu transakcije potrditve ali povrnitve, tako da nam ni treba skrbeti zanje vmes.
Implicitna transakcija v SQL Serverju
Implicitno transakcijo lahko definiramo tako, da omogočimo možnost IMPLICIT_TRANSACTIONS. Naslednji primer bo zlahka razložil ta 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
Pri tej transakciji smo uporabili dve možnosti @@OPTION in @@TRANCOUNT. @@OPTOPN zagotavlja informacije o trenutnih možnostih SET, @@TRANCOUNT pa zagotavlja stavek BEGIN TRANSACTION v trenutni seji.
Zdaj bo izvedba transakcije vrnila spodnji rezultat:
Eksplicitna transakcija v strežniku SQL
Eksplicitno transakcijo je treba definirati z ukazom BEGIN TRANSACTION, ker identificira začetno točko eksplicitne transakcije. Eksplicitno transakcijo v strežniku SQL lahko definiramo takole:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
V sintaksi možnost trans_name označuje edinstveno ime transakcije. The @trans_name_var označuje uporabniško definirano spremenljivko, ki shrani ime transakcije. Končno, MARK možnost nam omogoča, da označimo določeno transakcijo v dnevniški datoteki.
Eksplicitna transakcija prek ukaza BEGIN TRANSACTION je pridobila zaklepanje glede na raven izolacije virov, povezanih s transakcijo. Pomaga zmanjšati težave z zaklepanjem. Oglejte si spodnji primer:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Tukaj je rezultat:
Označena transakcija v strežniku SQL
Označena transakcija se uporablja za dodajanje opisa določeni transakciji v dnevniških datotekah. Uporabimo ga lahko kot obnovitveno točko namesto datuma in ure, ko bazo podatkov obnavljamo v prejšnje stanje. Vedeti moramo, da se oznaka doda v dnevniške datoteke le, ko označena transakcija spremeni bazo podatkov. Njegov koncept lahko razumemo na naslednjem primeru.
css prvi otrok
Recimo, da smo bazo podatkov pomotoma spremenili in ne vemo točnega trenutka spremembe podatkov; v tem primeru lahko obnovitev podatkov traja dolgo. Če pa uporabimo označene transakcije, je lahko uporabno orodje za določanje natančnega časa sprememb podatkov.
Naslednja sintaksa ponazarja označeno transakcijo v strežniku SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Tukaj moramo določiti ime transakcije in nato dodati možnost Z OZNAKO. V spodnjem primeru bomo izbrisali zapise in dodali oznako v dnevniško datoteko:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory tabela je vključena v baza podatkov msdb in shranjuje informacije o vsaki označeni transakciji, ki je bila odobrena. Izvedite spodnji stavek, da dobite podrobnosti iz tabele logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Imenovana transakcija v strežniku SQL
Prav tako lahko zagotovimo ime za našo transakcijo v SQL Server. Pri delu z več transakcijami v eni poizvedbi je vedno priporočljivo uporabiti imenovano transakcijo. Spodnji primer pojasnjuje, kako preimenovati transakcijo:
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
Tukaj je rezultat:
Zaključek
Ta članek bo podal popoln pregled transakcije v stavkih SQL Server. Transakcije so koristne v sistemih relacijskih baz podatkov, ker zagotavljajo celovitost baze podatkov.