Transakcije su važna komponenta u kontekstu integriteta podataka u bazi. Zamislimo jednostavan primer: preuzimamo 100 dinara sa bankomata. Bankomat šalje podatak bazi da želimo da uzmemo novac. Oduzima 100 dinara sa našeg računa i šalje bankomatu poruku da je to uradio. U međuvremenu, nestaje struje i bankomat ne dobija informaciju da treba da nam isporuči novac. Ostajemo bez keša iz bankomata i bez 100 dinara na računu. Za ovakav problem najbolji bi bio mehanizam koji će celu proceduru da zapamti i ako ta procedura ne bude uspešno dovedena do kraja, vrati sve na početno stanje. Takav mehanizam postoji u bazama podatka i naziva se transakcija.

Za transakcije postoji i mnogo kraći, jasniji, i dosta čest opis: „sve ili ništa“.

Transakcije se dele na eksplicitne i implicitne.

Sve manipulacione naredbe koje smo do sada obradili jesu implicitne transakcije. One se odigravaju transakciono, u smislu toga da će ili biti urađene, ili neće biti urađene, ali svakako, neće biti urađene napola. Za pomenute transakcije kaže se da su u autocommit modu, jer mi ne utičemo na njih tokom njihovog trajanja i nakon uspešnog izvršavanja, automatski se potvrđuju. Ova opcija je izmenjiva, i moguće je preuzeti kontrolu i nad osnovnim manipulacionim naredbama.

Da bismo to uradili, koristimo sistemsku promenljivu autocommit (sistemske promenljive u SQL–u označavaju se sa @@). Kada je autocommit podešen na 0 (isključen), čak ni osnovne naredbe neće biti izvršene pre nego što mi to odobrimo. Tako, na primer, sledeći primer, zapravo, neće obrisati kupca broj 103, jer je autocommit isključen, a transakcija na kraju vraćena naredbom rollback:

set @@autocommit=0;
delete from customers where customernumber=103;
rollback;


Da smo izvršili select upit između delete i rollback naredbi, videli bismo da je kupac broj 103 obrisan. Ipak, ovo zapravo i ne bi bilo realno stanje, jer se cela transakcija ne smatra završenom pre potvrde ili odustajanja naredbama rollback ili commit. U ovom slučaju, naredba je rollback, pa brisanje neće ni biti izvršeno.

Na ovaj način mogli bismo rešiti problem bankomata tako što bismo izvršili brisanje u bazi, a zatim aktivirali isplatu na bankomatu i tek zatim potvrdili ceo taj proces i tako zatvorili kompletnu transakciju.

Podsetimo se da transakcije funkcionišu jedino sa InnoDB mehanizmom skladištenja.

Ako pogledamo transakciju iz našeg primera, videćemo da ona nema početak. Njen početak je, zapravo, početak skripte. Sve nakon početka skripte do naredbe rollback ili commit spada pod jednu transakciju. Kada se aktivira neka od ove dve naredbe, transakcija ponovo počinje, sve do sledeće naredbe rollback ili commit. To je zato što su ove transakcije implicitne.

Da bi jedna transakcija bila eksplicitna, potrebno je da naglasimo njen početak i kraj naredbama start transaction i rollback/commit:

start transaction
insert ....
commit transaction


Ni jedna manipulaciona naredba izvršena unutar start i commit bloka, neće biti uistinu izvršena pre nego što dođe do commit naredbe. Namerno napominjemo da su u pitanju manipulacione naredbe, jer je manipulacioni set praktično jedini set koji je moguće izvršavati transakciono. Kada bismo u jednu transakciju smestili neku DDL naredbu (na primer create), ona bi bila bezuslovno izvršena, bez mogućnosti opoziva.

Moguće je postaviti i kontrolne tačke transakcije i tako kontrolisati njen tok. Kontrolne tačke označavaju se ključnom rečju savepoint:

start transaction;
delete from mojatabela where id = 1;
savepoint obrisan1;
delete from mojatabela where id = 2;
savepoint obrisan2;
delete from mojatabela where id = 3;
savepoint obrisan3;
rollback to savepoint obrisan2;
commit;


Ova transakcija vratiće sve poteze do kontrolne tačke obrisan2, tako da će biti izvršeno sve do te kontrolne tačke (biće obrisani ID-ovi 1 i 2).

  

Konkurencija

Rukovanje podacima u bazi, osim problema integriteta podataka, ima i problem konkurencije. Vratimo se na primer bankomata. Recimo da je upravo počela transakcija, novac je skinut sa računa i čeka se da bude isplaćen korisniku bankomata kako bi transakcija bila zatvorena. U tom trenutku, ako bismo pogledali stanje računa, videli bismo da je umanjeno, iako transakcija još uvek nije izvršena. Što znači, da stanje nije realno, jer novac još uvek nije u rukama korisnika. Šta bi video službenik u banci ako bi baš u tom trenutku odlučio da pogleda stanje računa tog korisnika? Na ovo pitanje postoji nekoliko odgovora koji zavise od načina na koji se transakcija izvršava. Postoji četiri moguća scenarija za ovakvu situaciju.

 

Prljavo čitanje (Dirty Read/Uncommited Read)

Ovo se događa ako jedna transakcija pristupa podacima koji su pod drugom transakcijom. Postoji realna mogućnost da ovi podaci nisu tačni što može dovesti do komplikacija prilikom rada jedne i druge transakcije.

 

Non-Repeatable Read (Inconsistent Analysis)

Slučaj je sličan prethodnom. Događa se ukoliko jedna transakcija ponavlja jedno isto čitanje, dok u međuvremenu, druga transakcija izvršava izmene na podacima, zbog čega dolazi do nepravilnih podataka u prvoj transakciji.

 

Fantomsko čitanje (Phantom Read)

Događa se kada se u toku jedne transakcije dodaju podaci od strane druge transakcije. To dovodi do pojave neidentifikovanog reda, a samim tim i nepravilnosti u radu.

 

Izgubljeno ažuriranje (Lost Update)

Kada dve transakcije ažuriraju podatke update naredbom, podaci bivaju ažurirani prema poslednjoj izvršenoj transakciji. Recimo da postoji neka vrednost u bazi: 10 i prva transakcija želi da podigne vrednost za 10, a druga transakcija hoće da je podigne za 20. Prva transakcija očekuje krajnju vrednost od 20, dok druga očekuje krajnju vrednost od 30. Na kraju, vrednost će biti 40, jer će se izvršiti prva transakcija (i podići vrednost za 10), a zatim i druga (koja će podići za još 20), što verovatno nije željeni rezultat.

  

Zaključavanje (Locks)

Zaključavanje je tehnika koju koristimo da bismo predupredili probleme nabrojane u prethodnim pasusima. Oni predstavljaju načine ponašanja podataka u situaciji kada im pristupa više transakcija. Lokovi nisu fizički objekti koji se pamte u bazi, već logičke reprezentacije osobine entiteta, koje se nalaze u memoriji.

Dva osnovna oblika zaključavanja su shared (deljeno) i exclusive (ekskluzivno) zaključavanje. Razlika je u tome što, ako je deljeni resurs zaključan shared lock-om, sve transakcije mogu da ga čitaju, ali samo aktuelna transakcija (ona koja je aktivirala lock) može i da piše po njemu (ovi se lokovi dešavaju na nivou redova i traju samo onoliko koliko je potrebno da bude pročitan red. Karakteristični su za select upit). Sa druge strane, ukoliko dođe do aktivacije ekskluzivnog zaključavanja, ni jedna transakcija ne može pristupiti resursu, sve dok se transakcija, koja je aktivirala lock, ne završi. Ovaj oblik zaključavanja događa se prilikom insert, update i delete naredbi.

Ponekad, obično prilikom loše realizovane konkurencije, dolazi do pojave deadlock-a. To je slučaj kada dve transakcije blokiraju jedna drugu. Recimo da jedna transakcija drži zaključan resurs, na koji druga transakcija čeka kako bi nastavila sa izvršavanjem. A ta druga transakcija drži zaključan resurs, koji je potreban prvoj transakciji kako bi nastavila sa izvršavanjem. Konačno, obe transakcije beznadežno čekaju jedna drugu da izađe iz zatvorenog kruga.

 

Zaključavanje tabela

Jedan od načina da se osigura integritet podataka u jednoj tabeli, prilikom izvršavanja transakcije, jeste da se cela tabela zaključa. Ovo se može uraditi ručno naredbom lock table  (i unlock table za otključavanje).
Prilikom pozivanja ove naredbe, postoji nekoliko tipova zaključavanja koje možemo aktivirati:

  • read (svi mogu čitati, samo aktuelna transakcija može i pisati),
  • read local,
  • write (samo aktuelna transakcija može vršiti upis, ostale transakcije ne mogu ni da čitaju ni da upisuju) i
  • low_priority write (isto što i read, ali ne može se aktivirati sve dok sve druge transakcije ne završe akcije na tabeli).

  

Izolacioni nivoi (Isolated Levels)

Izolacioni nivoi su neka vrsta izolacionih šema pod kojima se transakcije izvršavaju. Ima ih četiri: serializable, repeatable read, read commited i read uncommited.

  • serializable
  • Podrazumeva najviši stupanj izolacije. Svaka transakcija je izolovana i drži resurse ekskluzivno.
  • repeatable read
  • Transakcija zaključava ekskluzivno resurse na kojima piše i deljeno (shared) resurse koje čita. Zaključavanje traje sve dok traje i transakcija, pa ne može doći do izmena u podacima u toku transakcije. Ovo je podrazumevani izolacioni nivo MySQL servera.
  • read commited
    Za razliku od repeatable read-a, odmah nakon iščitavanja resursa on biva otključan. Resursi na kojima se vrši upis, ostaju i dalje zaključani do kraja transakcije.
  • read uncommited
    Zaključavanje traje samo dok traju i izmene. Ako se dogode izmene u jednoj transakciji, odmah su vidljive i za druge transakcije, iako još uvek nisu prihvaćene. Zbog toga se ovaj izolacioni nivo naziva još i dirty read.

 

Izolacioni nivo se nalazi na nivou servera i može se videti naredbama:

show global variables like 'tx_isolation';

ili

select @@global.tx_isolation;

 

Za izmenu izolacionog nivoa koristimo naredbu set transaction isolation level:

set transaction isolation level serializable;

 

Ovako napisana naredba važiće samo na nivou trenutne konekcije. I to ima identičan rezultat kao da smo je napisali i u obliku:

set session transaction isolation level serializable;

 

Ako bismo želeli da izolacioni nivo važi za sve konekcije na nivou servera, morali bismo da ga deklarišemo kao globalni:

set global transaction isolation level serializable;

 

Timeout i brzina pristupa

InnoDB je u stanju da detektuje pojavu dead lock-a i u tom slučaju izvršiće automatski opoziv transakcija koje se u njemu nalaze. Ali, ponekad (kada u transakciji učestvuju i tabele koje nemaju ovaj mehanizam skladištenja ili kada su tabele zaključane ručno, lock table direktivom) InnoDB, jednostavno, ne detektuje ovakvu situaciju i nije u stanju da prepozna da su transakcije uzajamno zaključane.

Zato je moguće odrediti vremenski period koji predstavlja to koliko će transakcije čekati da se ciljni resurs oslobodi, pre nego što ga napuste i budu opozvane.

Atribut koji sadrži ovu vrednost je innodb_lock_wait_timeout, ali se do njega ne može doći  kroz upite (na primer putem naredbe set), već mora biti inicijalizovan prilikom startovanja (restarta) servera.

 

 

Ista opcija, u MySql Workbench-u se nalazi u odeljku InnoDB pod nazivom innodb_lock_wait_timeout:

 


Konačno, možete kontrolisati i vreme pristupa manipulacionim SQL naredbama. Odnosno, njihov prioritet u odnosu na druge procese servera.

Postoje tri različita nivoa prioriteta:

  1. delayed
    Server stavlja naredbu na „listu čekanja” i aktivira je prvom prilikom.
    insert delayed into mojatabela values (null,'neki tekst');
  2. low priority
    Naredba se aktivira tek kada sve ostale naredbe budu aktivirane.
    insert low_priority into mojatabela values (null,'neki tekst');
  3. high priority
    Server gasi sve ostale procese (i zaključava ih) kako bi se aktuelna naredba aktivirala.
    insert high_priority into mojatabela values (null,'neki tekst');


Vreme pristupa nije moguće kontrolisati u InnoDB mehanizmu skladištenja.

 

Najvažnije iz lekcije:

  1. Transakcije su načini izvršavanja manipulacionih naredbi koje omogućavaju da se izvrši sve ili ništa.
  2. Transakcije funkcionišu na InnoDB mehanizmu skladištenja, ali ne i na MyIsam-u.
  3. Postoje eksplicitne i implicitne transakcije.
  4. Implicitna transakcija nema jasno označen početak.
  5. Eksplicitna transakcija počinje naredbom start transaction.
  6. Transakcije se završavaju naredbama rollback transaction ili commit transaction.
  7. Rollback transaction poništava sve naredbe u transakciji.
  8. Commit transaction potvrđuje transakciju.
  9. Konkurencija između transakcija može se regulisati izolacionim nivoom transakcija.
  10. Moguće je uticati na brzinu kojom će manipulacione SQL naredbe biti aktivirane.

Vežba

Problem:
 
Postoje sledeće dve tabele:

create table orders
(
id int primary key auto_increment,
code varchar(5)
)

create table basket
(
id int primary key auto_increment,
code varchar(5)
)


Potrebno je napraviti proceduru koja će u transakciji preneti određeni red iz tabele basket u tabelu orders.


Rešenje:

create PROCEDURE submitOrder(newcode varchar(5))
BEGIN
start transaction;
      insert into orders (code) select code from basket where code = newcode;
     delete from basket where code = newcode;
commit;
END//


Napomena:

Ova vežba rukuje samo jednom kolonom, code. U produkciji, u ovakvoj situaciji, obično se transportuju i dodatne kolone.

Dodaj komentar Sviđa mi se - (0) Ne sviđa mi se - (0)    

  • Transakcije i konkurencija 1
  • Transakcije i konkurencija 2
  • Transakcije i konkurencija 3