O tranzacție în SQL Server este a grup secvenţial de declaraţii sau interogări pentru a efectua sarcini individuale sau multiple într-o bază de date. Fiecare tranzacție poate avea o singură operație de citire, scriere, actualizare sau ștergere sau o combinație a tuturor acestor operațiuni. Fiecare tranzacție trebuie să aibă loc două lucruri în SQL Server:
- Fie toate modificările au succes atunci când tranzacția este efectuată.
- Sau, toate modificările sunt anulate atunci când tranzacția este anulată.
O tranzacție nu poate avea succes până când toate operațiunile din set nu sunt finalizate. Înseamnă că, dacă orice argument eșuează, operațiunea tranzacției va eșua. Fiecare tranzacție începe cu prima instrucțiune SQL executabilă și se termină atunci când găsește un commit sau rollback, fie explicit, fie implicit. Acesta folosește COMMIT sau ROLLBACK instrucțiuni în mod explicit, precum și implicit atunci când este utilizată o instrucțiune DDL.
Reprezentarea picturală de mai jos explică procesul tranzacției:
Următorul exemplu va explica conceptul de tranzacție:
Acest exemplu va folosi sistemul de baze de date bancare pentru a explica conceptul de tranzacție. Să presupunem că un client al unei bănci dorește să retragă bani din contul său folosind modul ATM. ATM-ul poate realiza această operațiune în trei pași:
- The primul pas este de a verifica disponibilitatea sumei solicitate în cont.
- The al doilea pas deduce suma din cont dacă suma este disponibilă și apoi actualizează soldul contului.
- The al treilea pas este să scrieți operațiunea de retragere a banilor în fișierul jurnal. Acest pas scrie despre tranzacția fie reușită, fie eșuată. Dacă a avut succes, scrieți modificarea datelor în baza de date. În caz contrar, tranzacția va fi revenită la starea anterioară.
Principiul de bază din spatele tranzacțiilor este că, dacă una dintre instrucțiuni returnează o eroare, întregul set de modificări este anulat pentru a asigura integritatea datelor. Și dacă tranzacțiile au succes, toate modificările vor fi permanente în baza de date. Prin urmare, dacă există o întrerupere a curentului sau alte probleme la retragerea banilor de la un bancomat, tranzacțiile garantează că soldul nostru rămâne constant. O declarație de tranzacție realizează cel mai bine aceste operațiuni, deoarece cele patru proprietăți cheie ale tranzacției fac ca toate operațiunile să fie mai precise și mai consecvente. Cele patru proprietăți ale tranzacției sunt denumite ACID.
Proprietățile tranzacției
Proprietățile tranzacției sunt denumite proprietate ACID (Atomicity, Consistency, Isolation, Durability), care sunt discutate în detaliu mai jos:
var global în js
Atomicitate: Această proprietate asigură că toate declarațiile sau operațiunile incluse în tranzacție trebuie efectuate cu succes. În caz contrar, întreaga tranzacție va fi anulată și toate operațiunile vor fi redate în starea anterioară atunci când orice operațiune nu reușește.
Consecvență: Această proprietate asigură că baza de date își schimbă starea numai atunci când o tranzacție va fi efectuată cu succes. De asemenea, este responsabil pentru protejarea datelor împotriva blocărilor.
Izolare: Această proprietate garantează că toate tranzacțiile sunt izolate de alte tranzacții, ceea ce înseamnă că fiecare operațiune din tranzacție este operată independent. De asemenea, se asigură că declarațiile sunt transparente unul pentru celălalt.
Durabilitate: Această proprietate garantează că rezultatul tranzacțiilor comise persistă în baza de date în mod permanent, chiar dacă sistemul se blochează sau eșuează.
Moduri de tranzacție în SQL Server
Există trei moduri diferite de tranzacție pe care SQL Server le poate folosi:
Mod de tranzacție de confirmare automată: Este modul implicit de tranzacție al SQL Server. Acesta va evalua fiecare instrucțiune SQL ca o tranzacție, iar rezultatele sunt confirmate sau anulate în consecință. Astfel, declarațiile de succes sunt imediat comise, în timp ce declarațiile eșuate sunt imediat anulate.
Modul de tranzacție implicit. Acest mod permite SQL Server să înceapă tranzacția implicită pentru fiecare instrucțiune DML, dar necesită în mod explicit utilizarea comenzilor commit sau rollback la sfârșitul instrucțiunilor.
Mod de tranzacție explicit: Acest mod este definit de utilizator, ceea ce ne permite să identificăm cu exactitate punctele de început și de sfârșit ale unei tranzacții. Se va anula automat în cazul unei erori fatale.
Controlul tranzacțiilor
Următoarele sunt comenzile folosite pentru a controla tranzacțiile:
NOTĂ: Putem folosi numai instrucțiuni DML (INSERT, UPDATE și DELETE) pentru comenzile Transaction Control Language. Nu le putem folosi în timp ce creăm sau aruncăm tabele, deoarece aceste operațiuni sunt trimise automat în baza de date.
Statul Tranzacției
Indică modul în care se desfășoară tranzacțiile pe parcursul vieții lor. Acesta descrie starea curentă a tranzacției, precum și modul în care tranzacția va fi procesată în viitor. Aceste stări definesc regulile care determină dacă o tranzacție se comite sau se anulează.
Să descriem fiecare stare de tranzacție în SQL Server:
sistem de operare linux
Stare activă: Tranzacția este într-o stare activă în timp ce instrucțiunile tranzacției sunt executate. Se schimbă în „stat parțial angajat” dacă toate operațiunile de „citire și scriere” sunt finalizate fără erori. Dacă vreo instrucțiune eșuează, se schimbă în „starea eșuată”.
Angajat parțial: Când toate operațiunile de citire și scriere sunt finalizate, modificarea se face în memoria principală sau în memoria tampon local. Statul ar merge la „stat angajat” dacă modificările sunt făcute permanente în baza de date. În caz contrar, trece la „starea eșuată”.
Stat eșuat: O tranzacție trece în starea eșuată atunci când o instrucțiune a oricărei tranzacții eșuează sau o modificare permanentă a bazei de date eșuează.
Stare avortată: Tranzacția trece de la a 'stat eșuat' la un „stat avortat” când apare orice fel de defecțiune. Modificările sunt eliminate sau anulate, deoarece aceste modificări sunt făcute doar în memoria tampon locală sau în memoria principală în stările anterioare.
Stat comis: O tranzacție este finalizată și intră în această stare atunci când modificările devin permanente în baza de date și se încheie în „stare terminată”.
Stat terminat: Dacă nu există rollback și tranzacția este în „stare comisă”, sistemul este consistent și pregătit pentru o nouă tranzacție în timp ce cea veche este terminată.
Implementarea tranzacției în SQL Server
Să luăm câteva exemple pentru a înțelege cum putem implementa tranzacția în SQL Server. Aici vom folosi 'Produs' tabel pentru a demonstra toate stările tranzacției.
Următoarele scripturi SQL creează tabelul Produs în baza de date selectată:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Apoi, executați scripturile de mai jos pentru a insera date în acest 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);
Executați instrucțiunea SELECT pentru a verifica datele:
Exemplu de tranzacție COMMIT
Este o idee bună să împărțiți instrucțiunile SQL utilizate în tranzacție în mai multe părți logice. Și apoi, putem decide dacă să comităm sau să derulăm înapoi datele. Următorii pași ilustrează crearea unei tranzacții:
- Începeți tranzacția folosind ÎNCEPE TRANZACȚIA comanda.
- Scrieți instrucțiunile SQL și împărțiți-le în funcție de nevoile noastre
- Folosește COMMIT extras pentru a finaliza tranzacția și a salva definitiv modificările.
Mai jos sunt comenzile care explică operațiunile COMMIT în 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
Dacă nu se găsește nicio eroare, vom vedea următoarea ieșire în care fiecare instrucțiune SQL a tranzacției este executată independent:
Instrucțiunile INSERT și UPDATE nu pot fi anulate după ce tranzacția a fost efectuată. Când verificăm tabelul după operația de comitere, vom vedea următoarele date:
fir.distruge
Exemplu de tranzacție ROLLBACK
Vom folosi comanda ROLLBACK pentru a anula orice tranzacție care nu a fost încă salvată în baza de date și a reveni la punctul în care a început tranzacția. Următorul exemplu explică operația ROLLBACK în 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
Odată ce executăm tranzacția de mai sus, putem vedea că va fi executată cu succes. Cu toate acestea, nu va afecta nicio modificare în baza de date deoarece până când nu executăm instrucțiunile COMMIT sau ROLLBACK, modificările nu pot deveni permanente. Prin urmare, avem opțiunea de a folosi comanda de tranzacție ROLLBACK pentru a derula înapoi toate operațiunile bazei de date. Iată declarația completă a tranzacției:
-- 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
Utilizarea variabilei globale @@Error în tranzacții:
Această variabilă este folosit pentru a verifica dacă există sau nu o eroare. Exemplul de mai jos explică conceptul său. Aici vom începe mai întâi tranzacția folosind comanda BEGIN și apoi vom scrie două instrucțiuni de inserare. În continuare, vom folosi variabila globală de sistem @@EROARE în Declarația IF pentru a verifica o eroare. Dacă valoarea este mai mare decât 0, înseamnă că există o eroare. Acum, tranzacția este rollback; în caz contrar, tranzacția este angajată.
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
Când tranzacția de mai sus este executată, vom observa că a fost anulată. Se datorează încercării noastre de a insera o valoare duplicată în coloana Cheie primară.
Tranzacție de returnare automată
Majoritatea tranzacțiilor conțin mai mult de o interogare. În timpul executării tranzacției, dacă oricare dintre instrucțiunile SQL produce o eroare, nu au loc modificări în baza de date, iar instrucțiunile rămase nu sunt executate. Acest concept este cunoscut sub numele de Tranzacție Auto Rollback în SQL Server. Să folosim un exemplu simplu pentru a demonstra acest proces.
exemple de programe python
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Această tranzacție produce următorul rezultat:
În această ieșire, putem vedea că instrucțiunea insert a fost executată cu succes. Cu toate acestea, executarea instrucțiunii de actualizare a găsit o eroare din cauza problemei de conversie a tipului de date. În acest caz, SQL Server nu permite nicio modificare în baza de date, ceea ce înseamnă că operația de inserare nu adaugă nicio valoare, iar instrucțiunea select nu este executată.
Punct de salvare în Tranzacții
Punctul de salvare inserează un semn special într-o tranzacție care ne permite să anulăm toate modificările executate după punctul de salvare. De asemenea, este folosit pentru a anula orice parte specifică a tranzacției, mai degrabă decât întreaga tranzacție. O putem defini folosind SALVAȚI TRANZACȚIA nume_sp afirmație. Următorul exemplu va explica utilizarea punctului de salvare în tranzacțiile care comită instrucțiunea de inserare și anulează instrucțiunea de ștergere.
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;
Vedeți rezultatul de mai jos, unde putem vedea că id-ul produsului 116 este șters și 117 este inserat în prima ieșire. Cu toate acestea, în a doua ieșire, operația de ștergere este anulată din cauza punctului de salvare.
Cum se eliberează un punct de salvare într-o tranzacție?
Eliberare punct de salvare este utilizat pentru a elimina punctul de salvare numit din tranzacția curentă fără a reveni la rezultatele interogărilor executate după punctul de salvare. MySQL are această comandă, dar SQL Server nu oferă nicio comandă pentru a elibera un punct de salvare. În schimb, acestea sunt eliberate automat la sfârșitul unei tranzacții de commit sau rollback, astfel încât nu trebuie să ne facem griji pentru ele intermediar.
Tranzacție implicită în SQL Server
Putem defini o tranzacție implicită activând opțiunea IMPLICIT_TRANSACTIONS. Următorul exemplu va explica cu ușurință acest concept:
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
În această tranzacție, am folosit două opțiuni @@OPTION și @@TRANCOUNT. @@OPTOPN oferă informații cu privire la opțiunile SET curente, iar @@TRANCOUNT furnizează instrucțiunea BEGIN TRANSACTION în sesiunea curentă.
Acum, executarea tranzacției va returna rezultatul de mai jos:
Tranzacție explicită în SQL Server
O tranzacție explicită trebuie definită prin comanda BEGIN TRANSACTION deoarece identifică punctul de pornire al tranzacției explicite. Putem defini tranzacția explicită în SQL Server după cum urmează:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
În sintaxă, opțiunea trans_name indică un nume unic al unei tranzacții. The @trans_name_var indică o variabilă definită de utilizator care stochează numele tranzacției. În cele din urmă, cel MARCĂ opțiunea ne permite să marchem o anumită tranzacție în fișierul jurnal.
Tranzacția explicită prin comanda BEGIN TRANSACTION a obținut o blocare în funcție de nivelul de izolare al resurselor legate de tranzacție. Ajută la reducerea problemelor de blocare. Vezi exemplul de mai jos:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Iată rezultatul:
Tranzacție marcată în SQL Server
Tranzacția marcată este utilizată pentru a adăuga o descriere la o anumită tranzacție în fișierele jurnal. Îl putem folosi ca punct de recuperare în locul unei date și ore atunci când restaurăm o bază de date la o stare anterioară. Trebuie să știm că marca este adăugată în fișierele jurnal numai atunci când tranzacția marcată modifică baza de date. Putem înțelege conceptul său folosind următorul exemplu.
testarea software-ului și tipurile
Să presupunem că am modificat accidental baza de date și nu cunoaștem momentul exact al modificării datelor; în acest caz, recuperarea datelor poate dura mult timp. Cu toate acestea, dacă folosim tranzacțiile marcate, acesta poate fi un instrument util pentru a determina momentul exact al modificărilor datelor.
Următoarea sintaxă ilustrează tranzacția marcată în SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Aici trebuie să definim numele tranzacției și apoi să adăugăm opțiunea CU MARCĂ. În exemplul de mai jos, vom șterge înregistrările și vom adăuga marcajul în fișierul jurnal:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory tabelul este inclus în baza de date msdb și stochează informații cu privire la fiecare tranzacție marcată care a fost efectuată. Executați instrucțiunea de mai jos pentru a obține detaliile din tabelul logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Tranzacție numită în SQL Server
De asemenea, putem oferi un nume pentru tranzacția noastră în SQL Server. Este întotdeauna recomandat să utilizați tranzacția numită atunci când lucrați cu mai multe tranzacții într-o singură interogare. Exemplul de mai jos explică cum să redenumești o tranzacție:
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
Iată rezultatul:
Concluzie
Acest articol va oferi o privire de ansamblu completă a tranzacției în instrucțiunile SQL Server. Tranzacțiile sunt utile în sistemele de baze de date relaționale, deoarece asigură integritatea bazei de date.