Tranzacţii

O tranzacţie poate fi considerată ca o succesiune de instrucţiuni SQL executate de un singur utilizator (instrucţiuni de consultare şi modificare a bazei de date). Execuţia operaţiilor de modificare a unei baze de date trebuie controlată deoarece există unele succesiuni de operaţii care trebuie să fie executate obligatoriu împreună pentru a se păstra consistenţa bazei de date (ex. transferul unei cantităţi monetare dintr-un cont în alt cont). Serverul de date trebuie să garanteze că execuţia intrucţiunilor grupate într-o tranzacţie se poate realiza în întregime. Dacă cel putin una din operaţii nu se poate executa, atunci trebuie anulate toate operaţiile din tranzacţie.

După execuţia întregii liste de operaţii dintr-o tranzacţie trebuie precizat că operaţiile de modificare trebuie anulate sau trebuie păstrate.

Fiecare server de date trebuie să aibă şi facilităţi de recuperare la erorile care pot să afecteze baza de date.

Tipuri de erori:

• Erori ale utilizatorilor, de ex. ştergerea (din greşeală) a unui tabel. Se poate reveni la situaţia existentă înainte de execuţia operaţiei de ştergere.
• Eroare la execuţia unei instrucţiuni sql (eroare de sintaxă, sau nu există drepturi suficiente). Efectul instrucţiunilor anterioare celei care a generat eroarea (dacă eventual unele date au fost deja schimbate) este automat anulat.
• Un utilizator nu mai poate continua activitatea la server (apare o deconectare a acestuia). In acest caz un proces Oracle (PMON sau SQL*Net) detectează situaţia şi anulează efectul tranzacţiei neterminate.
• Erori hardware care generează întreruperea activităţii la server. La reluarea activităţii se analizează tranzacţiile nefinalizate şi se anulează efectul acestora (se folosesc fişiere redo log).
• Distrugerea suportului bazei de date. Se poate folosi o salvare anterioară a bazei de date şi eventuale fişiere redo log arhivate.

Rolul tranzacţiilor:

• Recuperarea la erori
• Controlul accesului concurent

O tranzacţie trebuie să respecte cele patru proprietăţi (ACID):

1. Atomică - şirul de modificări efectuate într-o tranzacţie este indivizibil, deci aceste modificări (incluse în tranzacţie) fie sunt executate împreună, fie sunt anulate (se comportă ca o singură operaţie).
2. Consistentă - modificările din tranzacţie preiau o bază de date consistentă şi o predau, la final, tot într-o stare consistentă (sunt respectate toate restricţiile definite pentru baza de date).
3. Izolată - fiecare tranzacţie se execută independent (operaţiile de modificare dintr-o tranzacţie nu pot fi intercalate cu alte operaţii de modificare a aceloraşi date din baza de date, operaţii aflate într-o altă tranzacţie). Ca o consecinţă, modificările efectuate într-o tranzacţie nu depind de altă tranzacţie, deci două tranzacţii asupra aceloraşi date se execută serial.
4. Durabilă - după finalizarea unei tranzacţii şi păstrarea modificărilor, acestea devin permanente, chiar dacă are loc o "cădere" a sistemului (serverul de date trebuie să recupereze aceste modificări).

O modalitate de a respecta proprietăţile tranzacţiilor, în cazul unui acces concurent, este de a construi un plan de execuţie echivalent cu un plan neconcurent (care ar corespunde la o execuţie "serială" a tranzacţiilor. Un astfel de plan presupune că după ce o tranzacţie a devenit activă, deci a început să se execute prima operaţie din aceasta, celelalte tranzacţii cerute spre execuţie nu pot fi executate (sunt trecute într-o stare de aşteptare până la terminarea primei tranzacţii).

Deoarece multe tranzacţii actionează asupra unor date diferite, serverele de date permit intercalarea operaţiilor de gestiune a datelor, chiar dacă se efectuează din tranzacţii diferite. Această posibilitate de lucru creşte numărul de tranzacţii efectuate într-o anumită perioadă de timp prin folosirea resurselor serverului în timp ce o tranzacţie aflată în execuţie necesită date de pe suport. Pentru a păstra consistenţa bazei de date, un astfel de plan de execuţie în paralel a mai multor tranzacţii trebuie să fie echivalent cu unul serial (care este corect). Crearea unui astfel de plan se face de un planificator (scheduler) a operaţiilor de modificare a bazei de date.

Două operaţii asupra bazei de date (citire, modificare), din tranzacţii diferite, intră în conflict dacă vizează aceeaşi dată, din care cel puţin una din operaţii este o operaţie de modificare. Pentru a elimina astfel de conflicte este necesar un mecanism pentru asigurarea izolării. Un astfel de mecanism se poate realiza prin blocarea datelor, care (în mare) este:

• Dacă o tranzacţie cere o operaţie asupra unei date, planificatorul va încerca în prealabil blocarea datei respective pentru această operaţie.
• La o astfel de cerere de blocare, planificatorul verifică dacă data respectivă nu este deja blocată într-un mod conflictual cu cererea curentă, caz în care cererea de blocare nu este realizată şi operaţia este trecută într-o fază de aşteptare până la eliminarea blocajului conflictual, sau se generează o eroare.

Pentru a se asigura serializabilitatea (realizarea unui plan de execuţie echivalent cu unul serial, deci un plan de execuţie corect) este necesar ca tranzacţiile să respecte protocolul de blocare în două faze (într-o prima fază se fac numai blocări, după care urmează deblocări de date). Cerinţele acestui protocol trebuie respectate de aplicaţii. Acest protocol nu asigură că două tranzacţii generează impas.

Nivelele de izolare a tranzacţiilor

Mecanismul de blocare amintit (pentru a asigura serializabilitatea), şi care păstrează consistenţa datelor, este foarte restrictiv (dacă sunt multe tranzacţii concurente, atunci multe vor fi în starea de aşteptare). Pentru a asigura un acces mai mare la date (care să crească numărul de tranzacţii care se execută), multe SGBD-uri oferă anumite nivele de izolare pentru operaţiile de acces la date (citire, scriere), deci se slăbesc regulile de blocare. Cu aceste nivele mai scăzute de izolare pot apare unele probleme (fenomene):

• citirile inconsistente (dirty read) - o tranzacţie citeşte date care au fost rescrise de o altă tranzacţie nefinalizată, iar finalizarea se poate încheia cu anularea modificărilor deja citite de tranzacţia curentă;
• citirile nerepetitive (nonrepeatable read) - o tranzacţie reciteşte date (pe care tot ea le-a citit mai devreme) şi constată că o altă tranzacţie a modificat sau sters aceste date, deci această nouă citire nu furnizează aceleaşi date în aceeaşi tranzacţie;
• citirile fantomă (phantom read) - o tranzacţie reciteşte date (pe care tot ea le-a citit mai devreme) şi constată că o altă tranzacţie a adăugat noi înregistrări, deci această nouă citire furnizează date suplimentare;

In standardul SQL sunt definite patru nivele de izolare:

• Citiri nesalvate (Read uncommitted, sau "haos") - pentru citiri nu există restricţii (blocări), deci se pot citi date modificate şi chiar nesalvate (posibil să fie anulate mai târziu de tranzacţia care le-a creat).
  Pentru un anumit utilizator baza de date poate fi chiar într-o stare temporar inconsistentă.
• Citiri salvate (Read committed) - se vor bloca numai înregistrările care se modifică (până la salvarea modificărilor), nu şi cele care se citesc
• Citiri repetabile (Repeatable read) - va bloca numai înregistrarile efectiv citite, dar asupra altor înregistrări din acelaşi tabel se pot efectua operaţii de gestiune
• Seriabilitate (Serializable) - este cazul general descris mai sus. La accesul unei date, aceasta este blocată exclusiv până la terminarea tranzacţiei.

Posibilele anomalii care apar pentru diversele nivele de izolare sunt marcate cu "+" în tableul următor:

Nivele de izolare în Oracle

• Citiri salvate - read committed: este nivelul de izolare implicit din Oracle. Fiecare tranzacţie va vedea doar datele care au fost salvate de altă tranzacţie înainte de a fi citite de tranzacţia curentă. Din acest mod de rezolvare a modificărilor rezultă că o tranzacţie va citi numai date consistente. Serverul Oracle nu verifică dacă alte tranzacţii modifică datele citite anterior în tranzacţia curentă şi care au fost deja modificate şi salvate de altă tranzacţie între cele două interogări SQL (deci pot apare citiri nerepetabile şi citirile fantomă).
• Tranzactii serializabile - serializable: permite ca o tranzacţie să vadă numai acele modificări care au fost salvate înainte de începerea tranzacţiei curente, la care se adaugă modificările efectuate de tranzacţia curentă. In acest caz nu pot apare citiri nerepetabile sau citirile fantomă.
• Read-only: în care o tranzacţie vede numai modificările salvate înainte de începutul ei. In tranzactie pot apare numai instrucţiuni SELECT (dacă se folosesc proceduri, atunci se pot utiliza instrucţiuni SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE), deci nu sunt permise operaţii de modificare (INSERT, UPDATE, DELETE).

Precizarea nivelului de izolare a unei tranzacţii Oracle se face la începutul acesteia prin una din instructiunile:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

Nivelul de izolare a unei tranzacţii se poate modifica, în timpul execuţiei, prin una din instrucţiunile:

ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED;

Se observă că nu se poate face schimbarea nivelului de izolare la READ ONLY în timpul execuţiei tranzacţiei.

Crearea tranzacţiilor Oracle

In SQL există două comenzi care pot să fie folosite pentru a preciza sfârşitul unei tranzacţii:

• commit [work] - toate modificările efectuate în tranzacţie sunt salvate
• rollback - toate modificările efectuate în tranzacţie sunt anulate

O tranzacţie începe automat la execuţia primei instrucţiuni SQL care se execută, sau a unei instrucţiuni SET TRANSACTION, şi se termină prin una din instrucţiunile precedente.
Intr-o tranzacţie nu se pot folosi două sau mai multe instrucţiuni SET TRANSACTION.

După precizarea unui mod de terminare a tranzacţiei, prin una din cele două modalităţi, începe o nouă tranzacţie prin acelaşi procedeu.

La sfârşitul normal al unei sesiuni de lucru se execută implicit un "commit", iar dacă sesiunea se termină anormal, atunci modificările efectuate în ultima tranzacţie se pierd, deci se execută un rollback.
La nivel de SQL*Plus putem folosi comanda:

set autocommit {on | off | nr}

pentru a preciza că după fiecare operaţie de modificare se face automat un commit sau nu, sau numărul de modificări după care se face un commit automat.

Dacă există multe instrucţiuni sql într-o tranzacţie, atunci se pot pune marcaje intermediare (savepoints), care se declară. In acest fel se poate diviza o tranzacţie în părţi mai mici. Se poate anula efectul instrucţiunilor de modificare din întreaga tranzacţie, sau până la un anumit punct de salvare. Reluarea execuţiei instrucţiunilor se poate face de la un astfel de punct de reluare. Instrucţiunea SQL:

savepoint nume

va marca un astfel de punct de referinţă în lista de eliminări ale modificarilor dintr-o tranzacţie. Pentru utilizarea unei astfel de referinţe avem instrucţiunea:

roolback [work] to [savepoint] nume

care elimină modificările efectuate de la punctul de referinţă menţionat şi şterge punctele de referinţă definite după acesta.
Instrucţiunea commit şterge toate punctele de referinţă.

Din cele precizate mai sus rezultă următoarele operaţii care se fac la execuţia instrucţiunii COMMIT:

• toate modificările efectuate în tranzacţie sunt salvate
• elimină toate punctele de salvare din tranzactie (definite cu savepoint)
• elimină tranzacţia
• elimină toate blocările precizate în tranzacţie

Modificările efectuate într-o tranzacţie sunt păstrate în diverse buffere ale serverului (deci modificările sunt temporare). Aceste modificări se salvează efectiv în baza de date la execuţia unei instrucţiuni COMMIT (după care datele se şterg din buffere şi refacerea unor date anterioare nu mai este posibilă). Stergerea acestor buffere se face şi la executarea instructiunii ROLLBACK. Prin acest mod de păstrare a modificărilor se asigură:

• utilizatorul care face modificări în baza de date vede aceste modificări pe timpul tranzacţiei curente (vede efectele operaţiilor de modificare din tranzacţia curentă)
• alţi utilizatori ai bazei de date nu văd rezultatele operaţiilor de modificare în tranzacţia utilizatorului curent.
• înregistrările în care au apărut modificări sunt blocate, deci alţi utilizatori nu pot face modificări ale datelor din aceste înregistrări. Aceste blocări sunt păstrate de sistem până la finalizarea tranzacţiei.

Observaţie. Instrucţiunile ROLLBACK, COMMIT şi SAVEPOINT nu pot fi folosite în triggere.

Exemple

create table stud(nume varchar2(30), prenume varchar2(30), cnp char(13));

Exemplul 1:

insert into stud select nume,prenume,cnp from studenti where substr(cnp,2,2)='89';
commit;
/* adaugarea inregistrarilor este pastrata in baza de date */
select count(*) from stud;  
/* va furniza numarul de inregistrari adaugate */
delete from stud;
select count(*) from stud;  
/* nu exista inregistrari */
commit;
select count(*) from stud;  
/* stergerile se pastreaza */

Exemplul 2:

delete from stud;
insert into stud select nume,prenume,cnp from studenti where substr(cnp,2,2)='89';
commit;
select count(*) from stud;
/* va furniza numarul de inregistrari existente */
delete from stud;
select count(*) from stud;
/* nu exista inregistrari */
rollback;
select count(*) from stud;
/* stergerile se anuleaza, se da numarul de inregistrari din baza de date */

Exemplul 3, apariţie "Nonrepeatable Read" şi "Phantom Read" (implicit apare nivelul de izolare READ COMMITTED):

delete from stud;
insert into stud select nume,prenume,cnp from studenti where substr(cnp,2,2)='89';
commit;
select * from stud where cnp='2890113420002';
/* se obtin datele pentru 's1' */

update stud set nume='Pop' where cnp='2890113420002';
insert into stud(nume,prenume,cnp) values('Pop','Ion','1');
commit;
/* se actualizeaza datele pentru 's1' la 's2', se adauga 's3' */

select * from stud where cnp='2890113420002';
/* se obtin datele pentru 's2', in aceeasi tranzactie, cu aceeasi interogare */
/* cu o interogare:
select * from stud
se obtine o inregistrare noua */

O tranzacţie se poate defini ca read-only. In acest caz instrucţiuni INSERT, UPDATE, DELETE şi SELECT cu FOR UPDATE nu sunt permise.

Exemplul 4:

commit;
SET TRANSACTION READ ONLY ;
/* tipul tranzactiei */
SELECT COUNT(*) FROM stud;
/* se furnizeaza valoarea */
UPDATE stud SET nume='Popescu' WHERE cnp='2';
/* ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction */

Exemplul 5:

commit;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
declare
  n char(30);
begin
  delete from stud where cnp in ('3','4');
  insert into stud select nume,prenume,cnp from studenti where substr(cnp,2,2)='90';
  select nume into n from stud;
end;
/
select * from stud;

La final se furnizează toate datele din tabelul stud, deoarece blocul produce o eroare (cu instrucţiunea select se obţin mai multe înregistrări) şi se generează rollback, deci efectul instrucţiunilor delete şi insert este anulat.

Exemplul 6:

delete from stud;
INSERT INTO stud(cnp,nume,prenume) VALUES ('1','Aa','Bb');
INSERT INTO stud(cnp,nume,prenume) VALUES ('2','Xx','Yy');
commit;
update stud set prenume='Cc' where cnp='1';
select * from stud;

NUME                 PRENUME              CNP         
-------------------- -------------------- -------------
Aa                   Cc                   1             
Xx                   Yy                   2 

update stud set prenume='Zz' where cnp='2';
select * from stud;

NUME                 PRENUME              CNP         
-------------------- -------------------- -------------
Aa                   Bb                   1             
Xx                   Zz                   2

update stud set prenume='Uu' where cnp='2';
-- se asteapta deblocarea inregistrarii

update stud set prenume='Vv' where cnp='1';
-- se asteapta deblocarea inregistrarii

SQL Error: ORA-00060: deadlock detected while waiting for resource
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

-- trebuie anulate modificarile 
--    deoarece unele nu s-au executat
ROLLBACK;

update se executa

select * from stud;

NUME                 PRENUME              CNP         
-------------------- -------------------- -------------
Aa                   Vv                   1             
Xx                   Zz                   2

commit;
select * from stud;

NUME                 PRENUME              CNP         
-------------------- -------------------- -------------
Aa                   Bb                   1             
Xx                   Yy                   2 

commit;

select * from stud;

NUME                 PRENUME              CNP         
-------------------- -------------------- -------------
Aa                   Vv                   1             
Xx                   Zz                   2

Serverul Oracle asigură consistenţa datelor la citire în timpul execuţiei unei instrucţiuni SQL, adică se garantează că toate datele regăsite de o interogare provin dintr-un singur moment al existenţei bazei de date, deci datele sunt consistente. Această afirmaţie este adevărată şi pentru subinterogările incluse în instrucţiunea sql.

Alegerea nivelului de izolare în aplicaţii

1. Tranzacţiile serializabile nu se recomandă în cazul în care acestea cer un timp mare de executie deoarece e posibil ca multe tranzacţii să fie blocate sau să fie anulate din cauza impasului.
2. Dacă nivelul de concurenţă este mai mic, atunci nivelul de izolare serializabil este cel mai avantajos deoarece asigură consistenţa datelor şi nu apar fenomenele "citirile nerepetitive" şi "citirile fantomă".
3. Deoarece multe tranzacţii au timpul de execuţie mic (cerinţă care se recomandă), se poate folosi nivelul de izolare read committed (deoarece se asigură execuţia concurentă a multor tranzacţii, chiar dacă cele două fenomene amintite pot să apară). Din această cauză nivelul de izolare read committed este cel implicit în Oracle.

Tipuri de blocaje în Oracle

In afara facilităţilor de stabilire a unui nivel de izolare la tranzacţii, serverul Oracle permite şi blocări explicite, la nivel de înregistrare sau la nivel de tabel, pentru a controla mai bine execuţia tranzacţilor concurente. Sarcina alegerii blocajelor necesare (în tranzacţii) cade în sarcina dezvoltatorilor de aplicaţii.
Blocările explicite se realizează prin instrucţiunea:

LOCK TABLE tabel IN tip MODE [{NOWAIT | WAIT intreg}]

Cu WAIT intreg se precizează că se poate aştepta un număr precizat de secunde pentru realizarea blocării, iar cu NOWAIT se precizează că nu se aşteaptă dacă blocarea cerută nu se poate realiza. Dacă blocarea nu se poate executa, atunci se generează o eroare. Fără WAIT sau NOWAIT se aşteaptă până în momentul în care operaţia de blocare se poate realiza.

In Oracle se folosesc două moduri de blocare:

• modul exclusiv - util în tranzacţiile care modifică date. Prima tranzacţie care blochează exclusiv o resursă poate utiliza numai ea această resursă pentru modificare, până la terminarea tranzacţiei. Altă tranzacţie poate numai citi datele.
• modul partajat - permite un grad mai mare de concurenţă, mai multe tranzacţii pot obţine această blocare. Acest mod de blocare este util pentru a preveni ca altă tranzacţie să modifice date. Blocarea realizată de o tranzacţie este mentinută până la terminarea tranzacţiei.

• de modificare a datelor (DML): insert, update, delete, select for update
• de modificare a dicţionarului bazei de date (DDL): create, alter, drop

Blocarea poate fi făcută la nivel de:

• linie - are efect la linii (înregistrări) individuale. Acest mod de blocare oferă cel mai mare grad de concurenţă. Pentru ca o înregistrare să se modifice este necesar ca aceasta să fie blocată (înregistrarea sau tabelul care o conţine). La modificarea unei înregistrări se face şi o blocare exclusivă a acesteia astfel încât ea să nu mai poată fi modificată de altă tranzacţie. O tranzacţie care are o blocare pentru o linie obţine şi o anumită blocare pentru întregul tabel, pentru a preveni ca o altă tranzacţie să elimine tabelul sau să schimbe definiţia acestuia.
• tabel - are efect la întregul tabel. Această blocare este utilă pentru operaţii de modificare a datelor din întregul tabel sau pentru a preveni execuţia de instrucţiuni DDL pe tabelul curent.

Asa cum s-a precizat, respectarea protocolului de blocare în două faze nu elimină apariţia impasului (deadlock). Serverul Oracle detectează situaţiile de impas şi anulează una din tranzacţii (face un rollback). In plus, trimite un mesaj de eroare la client.

Tipurile de blocare sunt:

(RS):  LOCK TABLE tabel IN ROW SHARE MODE;
(RX):  LOCK TABLE tabel IN ROW EXCLUSIVE MODE;
(S):   LOCK TABLE tabel IN SHARE MODE;
(SRX): LOCK TABLE tabel IN SHARE ROW EXCLUSIVE MODE;
(X):   LOCK TABLE tabel IN EXCLUSIVE MODE;

1. Partajat la nivel de înregistrare (Row Share - RS): cere blocarea uneia sau a mai multor înregistrări în mod partajat, pentru modificarea lor.
   Alte tranzacţii pot interoga orice înregistrare şi pot insera, modifica, şterge înregistrări concurente din acelaşi tabel, dar nu pot bloca exclusiv tabelul (modul X).
   Acest tip de blocare se realizează explicit prin instrucţiunea:
   

   LOCK TABLE tabel IN ROW SHARE MODE;
   

   sau implicit prin utilizarea unui cursor cu clauza FOR UPDATE, sub forma::
   

   CURSOR nume IS
      instructiune_select
         FOR UPDATE [OF lista_coloane] [NOWAIT];
   

   Inregistrările regăsite în cursor se pot modifica sau şterge cu instrucţiuni de forma:
   

   update tabel set ... where current of cursor;
   
   delete from tabel where current of cursor;
   

   La o folosire a cursorului se face şi blocarea înregistrărilor ce vor fi regăsite de cursor (şi care se vor modifica). Se continuă procedura dacă se pot realiza blocările, altfel se aşteaptă sau se generează o eroare. Deblocarea se face la finalul tranzacţiei.
   

   Exemplul 7:
   

   Tranzacţia T1	Tranzacţia T2
   -- se foloseste tabelul -- create table stud(nume varchar2(30), prenume varchar2(30), cnp char(13)); delete from stud; INSERT INTO stud(cnp,nume,prenume) VALUES ('1','Aa','Bb'); INSERT INTO stud(cnp,nume,prenume) VALUES ('2','Xx','Yy'); commit; select * from stud where cnp in ('1','2') for update nowait; -- se realizeaza blocarea celor doua inregistrari -- rezultat NUME PRENUME CNP -------------------- -------------------- ------------- Aa Bb 1 Xx Yy 2
   	select * from stud; -- inregistrarile blocate de T1 se pot citi -- rezultat NUME PRENUME CNP -------------------- -------------------- ------------- Aa Bb 1 Xx Yy 2 update stud set prenume='Dd' where cnp='1'; -- actualizarea este in asteptare, inregistrarea este inca blocata
   update stud set prenume='Cc' where cnp='1'; -- actualizarea din T1 se face commit;
   	-- actualizarea din T2 se face -- tranzactia nu e terminata, o inreg. e blocata
   select * from stud where cnp in ('1','2') for update nowait; -- apare mesajul: SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 00054. 00000 - "resource busy and acquire with NOWAIT specified" *Cause: Resource interested is busy. *Action: Retry if necessary.
   	commit; -- tranzactia se termina, se face deblocarea

   Deoarece prin "select for update" se face blocarea tuturor înregistrărilor precizate înainte de actualizare, se consideră că această variantă de lucru este blocarea pesimistă.
   Spre deosebire de această variantă, blocarea optimistă presupune că datele care se doresc modificate nu generează conflicte, deci nu sunt necesare blocări, iar dacă unele modificări nu se pot realiza, atunci întreaga tranzacţie se anulează şi se reia execuţia ei.
2. Exclusiv la nivel de înregistrare (Row Exclusive - RX): cere blocarea unor înregistrări, în mod exclusiv, pentru modificarea lor. Blocarea se face la execuţia instrucţiunii de modificare.
   

   Alte tranzacţii pot să efectueze operaţii de citire sau modificare asupra altor înregistrări care nu s-au modificat, dar nu pot să facă o blocare: S, SRX, X (deci este mai restrictivă ca ROW SHARE).
   Acest tip de blocare se realizează implicit prin una din instrucţiunile:
   

   INSERT INTO  ...;
   UPDATE ... ;
   DELETE FROM ...;
   

   sau explicit prin:
   

   LOCK TABLE tabel IN ROW EXCLUSIVE MODE;
   

3. Partajat la nivel de tabel (Share - S), care se realizează explicit prin instrucţiunea:
   

   LOCK TABLE tabel IN SHARE MODE;
   

   Alte tranzacţii pot să consulte date din tabel, sau să facă o blocare partajată la nivel de linie (implicită sau explicită), sau o blocare de tip SHARE, dar nu pot să facă actualizări. Dacă mai multe tranzacţii au obţinut acest tip de blocare asupra unui tabel, atunci nici una nu va putea executa operaţii de modificare. Intr-o astfel de tranzacţie se pot face modificări numai dacă nu mai există alte tranzacţii cu acelaşi tip de blocare asupra tabelului.

4. Partajat la nivel de tabel şi exclusiv la nivel de linie (Share Row Exclusive - SRX) se realizează prin:
   

   LOCK TABLE tabel IN SHARE ROW EXCLUSIVE MODE;
   

   O singură tranzacţie poate obţine acest mod de blocare asupra unui tabel. Ea poate face modificări, iar alte tranzacţii pot numai citi date. Pe lângă citire, o altă tranzacţie poate bloca partajat înregistrări dar fără să modifice date.

5. Exclusiv la nivel de tabel (Exclusive - X): este cel mai restrictiv tip de blocare şi se poate obţine prin instrucţiunea:
   

   LOCK TABLE tabel IN EXCLUSIVE MODE;
   

   O altă tranzacţie poate numai interoga date.

Observaţie. Orice tip de blocare permite consultarea datelor de către altă tranzacţie.
Instrucţiunile de modificare a dicţionarului bazei de date nu solicită blocări explicite, dar pentru execuţie au nevoie de o blocare exclusivă. Pentru execuţie se aşteaptă finalizarea tranzacţilor care au blocat componentele asupra cărora acţionează.

Viewuri utile

• V$TRANSACTION - tranzacţiile active
• V$SESSION - date despre sesiunile curente
• V$LOCK - blocările existente

Tranzacţii autonome

O tranzacţie curentă T este suspendată în momentul în care se apelează procedura/blocul anonim care conţine o tranzacţie autonomă TA.

O tranzacţie anonimă trebuie să fie declarată într-un bloc PL/SQL (bloc anonim, funcţie, procedură, metoda unui obiect). In partea de declaraţii trebuie să se adauge instrucţiunea (directivă de compilare):

PRAGMA AUTONOMOUS_TRANSACTION;

Exemplu de procedură:

PROCEDURE nume_procedura IS
  PRAGMA AUTONOMOUS_TRANSACTION;
	...
BEGIN
   modificari: insert/update/delete
   commit;
END;

Observaţie. O tranzacţie autonomă nu poate consulta tabele modificate în tranzacţia părinte şi pentru care nu s-a precizat modul de terminare (commit/rollback).

Exemplu pentru a vedea diferenţa dintre o tranzacţie autonomă şi un obişnuită.

CREATE TABLE test_t(a NUMBER(2));

INSERT INTO test_t (a) VALUES (1);
INSERT INTO test_t (a) VALUES (2);

SELECT * FROM test_t;

Ultima instrucţiune SELECT furnizează două înregistrări.
Vom folosi (pentru execuţie) o tranzacţie autonomă, definită într-un bloc anonim (poate fi memorată într-o procedură):

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  i number;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO test_t (a) VALUES (i);
  END LOOP;
  COMMIT;
END;

Acest bloc se va executa. Pentru că blocul s-a terminat de executat fără erori, înregistrările adăugate aici se vor păstra (se execută commit la final). Cu instrucţiunea:

SELECT * FROM test_t;

se vor obţine 10 înregistrări.
Dacă se execută instrucţiunile:

ROLLBACK;
SELECT * FROM test_t;

atunci se vor obţine numai 8 înregistrări, deoarece cele adăugate în tranzacţia curentă sunt eliminate.
Dacă se execută din nou aceleaşi instrucţiuni, fără să se utilizeze tranzacţia autonomă:

drop table test_t;
CREATE TABLE test_t(a NUMBER(2));

INSERT INTO test_t (a) VALUES (1);
INSERT INTO test_t (a) VALUES (2);

SELECT * FROM test_t;
DECLARE
  i number;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO test_t (a) VALUES (i);
  END LOOP;
  COMMIT;
END;
/
ROLLBACK;
SELECT * FROM test_t;

atunci în final vor fi 10 înregistrări în tabel (modificările au fost salvate în blocul PL/SQL).

Tranzacţiile autonome se recomandă a fi folosite pentru execuţia unor operaţii care nu se fac în alte tranzacţii şi care trebuie efectuate indiferent de modul de terminare a tranzacţiilor. Se poate utiliza, de exemplu, pentru urmărirea operaţiilor din diverse tranzacţii.

Vom crea un tabel pentru inventarierea erorilor din anumite tranzacţii:

CREATE TABLE erori(
  utilizator varchar2(20),
  data date,
  mesaj VARCHAR2(2000)
);

Pentru introducerea de înregistrări în acest tabel vom folosi procedura:

CREATE OR REPLACE PROCEDURE adauga_eroare(utilizator IN VARCHAR2, mesaj IN VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO erori (utilizator, data, mesaj) VALUES (utilizator, SYSDATE, mesaj);
  COMMIT;
END;

Vom folosi tabelul anterior:

CREATE TABLE test_t(a NUMBER(2));

Vom adăuga înregistrări în tabelul test_t folosind următorul bloc:

BEGIN
  INSERT INTO test_t values (1);
  INSERT INTO test_t values (11);
  INSERT INTO test_t values (111);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    adauga_eroare(user, SQLERRM);
    ROLLBACK;
END;

Deoarece în acest bloc apare o eroare (tratată în partea EXCEPTION), toate modificările sunt anulate (în EXCEPTION apare ROLLBACK), dar în tabelul erori apare o înregistrare.

Utilizare tranzacţii în PHP

• bool oci_execute(resource $id_instructiune [, int parametru])

  care execută o instrucţiune (identificată de primul argument) pregătită de oci_parse, iar parametru precizează modul în care sunt tratate modificările din baza de date (dacă se execută astfel de instrucţiuni). Valorile posibile pentru acest parametru sunt:
  • OCI_COMMIT_ON_SUCCESS - valoare implicită, se face un COMMIT automat dacă nu au apărut erori
  • OCI_DEFAULT - care nu execută un COMMIT automat după execuţia instrucţiunii, deci modificările nu sunt memorate în baza de date. Dacă scriptul curent nu execută o comandă COMMIT, atunci modificările sunt pierdute.

• bool oci_commit(resource $id_instructiune)

  care va executa o instrucţiune COMMIT. Valoarea furnizată de funcţie este TRUE în cazul succesului sau FALSE în cazul în care salvarea modificărilor nu s-a putut realiza.

• bool oci_rollback(resource $id_instructiune)

  care va executa o instrucţiune ROLLBACK, deci modificările anterioare din tranzacţia curentă sunt anulate. Valoarea furnizată de funcţie este TRUE sau FALSE (operaţia s-a put sau nu s-a putut executa).

• Exemplul 1. Execuţia unor modificări într-o bază de date cu diverse variante de finalizare a tranzacţiilor.
  

  <?php
  function executa($con, $query) { 
    $s = oci_parse($con, $query); 
    oci_execute($s, OCI_DEFAULT); 
    oci_fetch_all($s, $r);   //toate valorile se obtin intr-o matrice
    echo "<pre>"; 
    var_dump($r);            //extrage matricea 
    echo "</pre>"; 
  } 
  
  $con1 = oci_connect("master", "master", "orcl10");     //prima conectiune
  
  $x = oci_parse($con1, "insert into test_t values (51)"); 
  $r = oci_execute($x, OCI_DEFAULT);  // implicit nu apare COMMIT 
  executa($con1, "select * from test_t"); 
  
  $con2 = oci_new_connect("master", "master", "orcl10"); //a doua conectiune
  executa($con2, "select * from test_t"); 
  //$con2 nu vede inregistrarea inserata anterior de $con1
  $x = oci_parse($con2, "insert into test_t values (61)"); 
  $r = oci_execute($x, OCI_DEFAULT);  // implicit nu apare COMMIT 
  
  executa($con1, "select * from test_t"); 
  //$con1 vede numai inregistrarea introdusa de ea
  oci_commit($con2);
  executa($con1, "select * from test_t"); 
  //$con1 vede toate inregistrarile
  
  oci_close($con1);
  oci_close($con2);
  
  //inregistrarea adaugata de $con1 se pierde (nu exista COMMIT)
  //inregistrarea adaugata de $con2 se pastreaza
  
  ?>
  

• Exemplul 2. Obţinerea de informaţii privind timpul de execuţie a unor modificări în baza de date.
  

  <?php
  //presupunem creat tabelul:
  //create table test_t1 (id number, text varchar2(100));
  
  //comparatie intre doua tipuri de salvare a modificarilor
  
  function executa1($con, $query) { 
    $s = oci_parse($con, $query); 
    oci_execute($s, OCI_DEFAULT); //nu se executa commit automat
  } 
  
  function executa2($con, $query) { 
    $s = oci_parse($con, $query); 
    oci_execute($s);              //commit automat
  } 
  
  function nr_inreg($con){
    $idi = oci_parse($con, "select count(*) nr from test_t1");
    oci_execute($idi);
    $r=oci_fetch_row($idi);
    echo "Nr.de inreg.: " . $r[0] . "<br>";
  }
  
  $con = oci_connect("leon", "leon", "orcl");  
  executa2($con, "delete from test_t1");
  
  $timp1 = microtime(TRUE);
  echo $timp1."<br>";
  for ($i=1; $i <=1000; $i++){
    executa1($con, "insert into test_t1 values(".$i.", to_char(sysdate,'dd.mm.yyyy hh:mi:ss'))");
  }
  $timp2 = microtime(TRUE);
  nr_inreg($con);
  echo "Timpul de executie dupa adaugare: ".round($timp2-$timp1,3) . " secunde<br>";
  
  oci_commit($con);
  $timp2 = microtime(TRUE);
  //nr_inreg($con);
  echo "Timpul de executie dupa adaugare si commit la final: <b>".round($timp2-$timp1,3) . " secunde</b><br>";
  
  executa2($con, "delete from test_t1");
  
  $timp3 = microtime(TRUE);
  for ($i=1; $i<=1000; $i++){
    executa2($con, "insert into test_t1 values(".$i.", to_char(sysdate,'dd.mm.yyyy hh:mi:ss'))");
  }
  
  $timp4 = microtime(TRUE);
  nr_inreg($con);
  echo "Timpul de executie dupa adaugare si commit la fiecare adaugare: <b>".round($timp4-$timp3,3) . " secunde</b>";
  
  executa2($con, "delete from test_t1");
  
  oci_close($con);
  
  ?>