Transakcija u SQL Serveru je a sekvencijalna skupina iskaza ili upita za obavljanje jednog ili više zadataka u bazi podataka. Svaka transakcija može imati pojedinačne operacije čitanja, pisanja, ažuriranja ili brisanja ili kombinaciju svih ovih operacija. Svaka transakcija mora dogoditi dvije stvari u SQL Serveru:
- Ili su sve izmjene uspješne kada se transakcija izvrši.
- Ili se sve izmjene poništavaju kada se transakcija vraća.
Transakcija ne može biti uspješna dok se ne završe sve operacije u skupu. To znači da ako bilo koji argument ne uspije, operacija transakcije neće uspjeti. Svaka transakcija počinje s prvom izvršnom SQL naredbom i završava kada pronađe commit ili rollback, eksplicitno ili implicitno. Koristi se POČINITI ili POVRATAK izjave eksplicitno, kao i implicitno kada se koristi DDL izjava.
Slikovni prikaz u nastavku objašnjava proces transakcije:
Sljedeći primjer objasnit će koncept transakcije:
Ovaj primjer će koristiti bankovni sustav baze podataka za objašnjenje koncepta transakcije. Pretpostavimo da korisnik banke želi podići novac sa svog računa korištenjem ATM moda. Bankomat može izvršiti ovu operaciju u tri koraka:
- The Prvi korak je provjeriti raspoloživost traženog iznosa na računu.
- The drugi korak skida iznos s računa ako je iznos dostupan i zatim ažurira stanje računa.
- The treći korak je zapisati operaciju povlačenja novca u datoteku dnevnika. Ovaj korak piše o tome je li transakcija uspješna ili neuspješna. Ako je uspješno, zapišite izmjenu podataka u bazu podataka. U suprotnom, transakcija će se vratiti u prethodno stanje.
Osnovno načelo iza transakcija je da ako jedna od izjava vrati pogrešku, cijeli skup promjena se vraća kako bi se osigurao integritet podataka. A ako transakcije postanu uspješne, sve će promjene biti trajne u bazi podataka. Stoga, ako dođe do nestanka struje ili drugih problema prilikom podizanja novca s bankomata, transakcije jamče da će naš saldo ostati dosljedan. Izjava o transakciji najbolje izvodi ove operacije jer četiri ključna svojstva transakcije čine sve operacije točnijim i dosljednijim. Četiri svojstva transakcije nazivaju se ACID.
Transakcijska svojstva
Svojstva transakcije nazivaju se svojstvima ACID (Atomicity, Consistency, Isolation, Durability), koja se detaljno razmatraju u nastavku:
Valentnost: Ovo svojstvo osigurava da se sve izjave ili operacije uključene u transakciju moraju uspješno izvesti. U suprotnom, cijela transakcija će biti prekinuta, a sve operacije se vraćaju u prethodno stanje kada bilo koja operacija ne uspije.
Dosljednost: Ovo svojstvo osigurava da baza podataka mijenja stanje samo kada se transakcija uspješno izvrši. Također je odgovoran za zaštitu podataka od padova.
Izolacija: Ovo svojstvo jamči da su sve transakcije izolirane od drugih transakcija, što znači da se svaka operacija u transakciji izvodi neovisno. Također osigurava transparentnost izjava jedna prema drugoj.
Izdržljivost: Ovo svojstvo jamči da rezultat izvršenih transakcija trajno ostaje u bazi podataka čak i ako se sustav sruši ili otkaže.
Načini transakcije u SQL Serveru
Postoje tri različita načina transakcije koje SQL Server može koristiti:
Način automatske transakcije: To je zadani način transakcije SQL Servera. Procijenit će svaku SQL naredbu kao transakciju, a rezultati se prema tome predaju ili vraćaju unatrag. Tako se uspješne izjave odmah predaju, dok se neuspješne izjave odmah vraćaju.
Način implicitne transakcije. Ovaj način rada omogućuje SQL Serveru da započne implicitnu transakciju za svaku DML naredbu, ali izričito zahtijeva korištenje naredbi za commit ili rollback na kraju naredbi.
Eksplicitni način transakcije: Ovaj način definira korisnik koji nam omogućuje da točno identificiramo početnu i završnu točku transakcije. Automatski će se prekinuti u slučaju fatalne pogreške.
Kontrola transakcija
Sljedeće su naredbe koje se koriste za kontrolu transakcija:
značajke serije panda
NAPOMENA: Možemo koristiti samo DML izjave (INSERT, UPDATE i DELETE) za naredbe Transaction Control Language. Ne možemo ih koristiti dok stvaramo ili ispuštamo tablice jer se te operacije automatski predaju bazi podataka.
Stanje transakcije
Pokazuje kako se transakcije odvijaju tijekom njihovog trajanja. Opisuje trenutno stanje transakcije kao i način na koji će se transakcija obrađivati u budućnosti. Ta stanja definiraju pravila koja određuju hoće li se transakcija obvezati ili prekinuti.
Opišimo stanje svake transakcije u SQL Serveru:
Aktivno stanje: Transakcija je u aktivnom stanju dok se izvršavaju instrukcije transakcije. Mijenja se u 'djelomično predano stanje' ako su sve operacije 'čitanja i pisanja' završene bez grešaka. Ako bilo koja instrukcija ne uspije, mijenja se u 'neuspješno stanje'.
Djelomično predano: Kada su sve operacije čitanja i pisanja dovršene, promjena se vrši u glavnoj memoriji ili lokalnom međuspremniku. Država bi išla na 'predana država' ako su promjene u bazi podataka trajne. U protivnom ide u 'failed state'.
Neuspjelo stanje: Transakcija prelazi u neuspjelo stanje kada bilo koja transakcijska instrukcija ne uspije ili trajna izmjena baze podataka ne uspije.
Prekinuto stanje: Transakcija se kreće od a 'propala država' do an 'prekidno stanje' kada se dogodi bilo kakav kvar. Promjene se uklanjaju ili vraćaju jer su te promjene napravljene samo u lokalnom međuspremniku ili glavnoj memoriji u prethodnim stanjima.
koliko gradova ima u nas
Predano stanje: Transakcija je dovršena i prelazi u ovo stanje kada su promjene u bazi podataka trajne i prekinute u 'završeno stanje'.
Prekinuto stanje: Ako nema vraćanja i transakcija je u 'predana država,' sustav je konzistentan i spreman za novu transakciju dok se stara raskida.
Implementacija Transakcije u SQL Serveru
Uzmimo neke primjere da bismo razumjeli kako možemo implementirati transakciju u SQL Serveru. Ovdje ćemo koristiti 'Proizvod' tablica za prikaz svih stanja transakcija.
Sljedeće SQL skripte stvaraju tablicu proizvoda u odabranoj bazi podataka:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Zatim izvršite donje skripte za umetanje podataka u ovu tablicu:
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);
Izvršite naredbu SELECT za provjeru podataka:
Primjer transakcije COMMIT
Dobra je ideja podijeliti SQL izjave koje se koriste u transakciji u više logičkih dijelova. A zatim možemo odlučiti hoćemo li predati ili vratiti podatke. Sljedeći koraci ilustriraju stvaranje transakcije:
- Započnite transakciju pomoću ZAPOČNI TRANSAKCIJU naredba.
- Napišite SQL naredbe i podijelite ih prema našim potrebama
- Koristiti POČINITI izjavu za dovršetak transakcije i trajno spremanje promjena.
Ispod su naredbe koje objašnjavaju operacije COMMIT u SQL Serveru:
-- 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
Ako se ne pronađe nikakva pogreška, vidjet ćemo sljedeći izlaz gdje se svaka SQL izjava transakcije izvršava neovisno:
Izjave INSERT i UPDATE ne mogu se vratiti nakon što je transakcija predana. Kada provjerimo tablicu nakon operacije predaje, vidjet ćemo sljedeće podatke:
Primjer ROLLBACK transakcije
Koristit ćemo naredbu ROLLBACK za poništavanje svih transakcija koje još nisu spremljene u bazu podataka i povratak na točku na kojoj je transakcija započela. Sljedeći primjer objašnjava operaciju ROLLBACK u SQL Serveru:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Nakon što izvršimo gornju transakciju, možemo vidjeti da će biti uspješno izvršena. Međutim, to neće utjecati na promjene u bazi podataka jer sve dok ne izvršimo naredbu COMMIT ili ROLLBACK, promjene ne mogu postati trajne. Stoga imamo mogućnost korištenja transakcijske naredbe ROLLBACK za vraćanje svih operacija baze podataka. Ovdje je potpuna 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
Upotreba globalne varijable @@Error u transakcijama:
Ova varijabla je koristi se za provjeru postoji li greška ili ne. Donji primjer objašnjava njegov koncept. Ovdje ćemo prvo pokrenuti transakciju pomoću naredbe BEGIN, a zatim napisati dvije izjave za umetanje. Zatim ćemo koristiti varijablu globalnog sustava @@GREŠKA u IF izjava provjeriti grešku. Ako je vrijednost veća od 0, to znači da postoji neka greška. Sada je transakcija vraćena; u suprotnom, transakcija je predana.
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
Kada se gornja transakcija izvrši, primijetit ćemo da je vraćena. To je zbog našeg pokušaja da umetnemo duplikat vrijednosti u stupac Primarni ključ.
Automatsko vraćanje transakcije
Većina transakcija sadrži više od jednog upita. Tijekom izvođenja transakcije, ako bilo koja od SQL naredbi proizvede pogrešku, u bazi podataka se ne događaju izmjene, a preostale naredbe se ne izvršavaju. Ovaj koncept poznat je kao Auto Rollback Transaction u SQL Serveru. Upotrijebimo jednostavan primjer da demonstriramo ovaj 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
Ova transakcija proizvodi sljedeći izlaz:
U ovom izlazu možemo vidjeti da je naredba umetanja uspješno izvršena. Međutim, izvršavanje naredbe ažuriranja pronašlo je pogrešku zbog problema s konverzijom tipa podataka. U ovom slučaju SQL Server ne dopušta nikakve promjene u bazi podataka, što znači da operacija umetanja ne dodaje nikakvu vrijednost, a naredba select se ne izvršava.
linux koji
Točka spremanja u Transakcijama
Točka spremanja umeće posebnu oznaku u transakciju koja nam omogućuje vraćanje svih promjena izvršenih nakon točke spremanja. Također se koristi za vraćanje bilo kojeg određenog dijela transakcije, a ne cijele transakcije. Možemo ga definirati pomoću SPREMI TRANSAKCIJU sp_name izjava. Sljedeći primjer će objasniti korištenje točke spremanja u transakcijama koje predaju naredbu umetanja i vraćaju naredbu brisanja.
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;
Pogledajte donji rezultat gdje možemo vidjeti da je ID proizvoda 116 izbrisan, a 117 umetnut u prvi izlaz. Međutim, u drugom izlazu, operacija brisanja je vraćena zbog točke spremanja.
Kako osloboditi točku spremanja u transakciji?
Otpuštanje točke spremanja koristi se za uklanjanje imenovane točke spremanja iz trenutne transakcije bez vraćanja rezultata upita izvršenih nakon točke spremanja. MySQL ima ovu naredbu, ali SQL Server ne daje nikakvu naredbu za oslobađanje točke spremanja. Umjesto toga, automatski se oslobađaju na kraju transakcije predaje ili vraćanja, tako da ne moramo brinuti o njima u međuvremenu.
Implicitna transakcija u SQL Serveru
Implicitnu transakciju možemo definirati uključivanjem opcije IMPLICIT_TRANSACTIONS. Sljedeći primjer će lako objasniti ovaj 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
U ovoj transakciji koristili smo dvije opcije @@OPTION i @@TRANCOUNT. @@OPTOPN pruža informacije o trenutnim opcijama SET, a @@TRANCOUNT daje naredbu BEGIN TRANSACTION u trenutnoj sesiji.
Sada će izvršavanje transakcije vratiti donji izlaz:
Eksplicitna transakcija u SQL Serveru
Eksplicitna transakcija mora biti definirana putem naredbe BEGIN TRANSACTION jer ona identificira početnu točku eksplicitne transakcije. Eksplicitnu transakciju u SQL Serveru možemo definirati na sljedeći način:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
U sintaksi, opcija trans_name označava jedinstveni naziv transakcije. The @trans_name_var označava korisnički definiranu varijablu koja pohranjuje naziv transakcije. Konačno, OCJENA opcija nam omogućuje označavanje određene transakcije u log datoteci.
Eksplicitna transakcija putem naredbe BEGIN TRANSACTION dobila je zaključavanje ovisno o razini izolacije resursa povezanih s transakcijom. Pomaže u smanjenju problema s zaključavanjem. Pogledajte primjer u nastavku:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Evo rezultata:
Označena transakcija u SQL Serveru
Označena transakcija koristi se za dodavanje opisa određenoj transakciji u datotekama dnevnika. Možemo ga koristiti kao točku oporavka umjesto datuma i vremena pri vraćanju baze podataka u prethodno stanje. Moramo znati da se oznaka dodaje u datoteke dnevnika samo kada označena transakcija modificira bazu podataka. Njegov koncept možemo razumjeti koristeći sljedeći primjer.
Pretpostavimo da smo slučajno modificirali bazu podataka i ne znamo točan trenutak promjene podataka; u tom slučaju oporavak podataka može potrajati dugo. Međutim, ako koristimo označene transakcije, to može biti koristan alat za određivanje točnog vremena izmjene podataka.
Sljedeća sintaksa ilustrira označenu transakciju u SQL Serveru:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Ovdje moramo definirati naziv transakcije i zatim dodati opciju S OZNAKOM. U primjeru ispod, izbrisat ćemo zapise i dodati oznaku u datoteku dnevnika:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkpovijest tablica je uključena u msdb baza podataka i pohranjuje podatke o svakoj označenoj transakciji koja je izvršena. Izvršite naredbu u nastavku da biste dobili pojedinosti iz tablice logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Imenovana transakcija u SQL Serveru
Također možemo dati naziv za našu transakciju u SQL Serveru. Uvijek se preporučuje korištenje imenovane transakcije kada radite s više transakcija u jednom upitu. Primjer u nastavku objašnjava kako preimenovati transakciju:
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
Evo rezultata:
Zaključak
Ovaj će članak dati potpuni pregled transakcije u izjavama SQL Servera. Transakcije su korisne u sustavima relacijskih baza podataka jer osiguravaju integritet baze podataka.