MySQL i Transakcije

Kao što sam u prvom ovogodišnjem zapisu i najavio, od danas krećem sa serijom tutorijala o retko korišćenim mogućnostima MySQL-a kao što su transakcije, referencijalni integritet, fulltext search itd. Developeri ove mogućnosti MySQL-a retko koriste. Mnogi od njih čak i ne znaju da one postoje, pa se uobičajeno, bez puno argumenata, na raznim advocacy raspravama MySQL naziva nekompletnom bazom jer “ne podržava” ništa od gore navedenih mogućnosti. Malo ljudi zna da MySQL recimo podržava transakcije još od verzije 3.23.15 (izašla još maja sada već davne 2000-te godine). Slična je priča i sa ostalim mogućnostima.

Ovaj tekst kao i nastavci koji slede ima za cilj da razbije neke predrasude o MySQL-u kao i da podstakne developere da počnu sa korišćenjem naprednijih mogućnosti koje im njihova baza pruža.

Danas krećemo sa opisom transakcija.

Transakcija SQL rečnikom označava skup upita koji se izvršavaju kao celina. Ako su svi upiti uspešno izvršeni onda je transakcija prošla, u suprotnom vraćamo se u pređašnje stanje (rollback). Obzirom da većina ljudi sam pojam transakcija vezuje za novac, iskoristiću upravo jedan takav primer kako bi cela stvar bila još jasnija.

Zamislite transakciju gde Pera svom klijentu Mikici treba da isplati 1000 dinara za neku učinjenu uslugu. Evo kako bi tipičnim SQL rečnikom to mogli da predstavimo:

update bank_accounts set amount = amount - 1000 
where user = 'pera';
update bank_accounts set amount = amount + 1000 
where user = 'mikica';

Šta ako se jedan od gornja dva upita ne izvrši korektno ? Imali bi situaciju da Pera ostane kratak za 1000 dinara bez da je uplatio novac Mikici, ili da Mikica dobije svoj novac od same banke u slučaju da prvi upit nije izvršen. Korišćenjem transakcija u slučaju da dođe do greške sistem se vraća na stanje pre startovanja transakcije, tako da će u ovom konkretnom slučaju biti izvršena oba upita ili nijedan od njih.

Transakcione baze tipično moraju obezbediti 4 osobine koje se skraćeno nazivaju ACID:
Atomicity
Iskaz se sastoji od nekoliko logičnih celina – skupa upita. Ili su svi upiti izvršeni uspešno ili nijedan od njih.
Consistency
Baza je u koezistentnom stanju pre i posle transakcije.
Isolation
Transakcija nema efekat na druge procese. To znači da efekti pojedinačnih upita u transakciji nisu vidljivi drugim klijentima sve dok se transakcija uspešno ne izvrši (commit iskazom).
Durability
Kada je transakcija uspešno obavljena njeni efekti su permanentni.

A sada dosta teorije, vreme je da pređemo i na konkretnu upotrebu. Sintaksa koja sledi prilagođena je MySQL-u 4.1.x. U zavisnosti od verzije vašeg MySQL servera biće potrebne izvesne izmene. Da bi ste saznali koju verziju servera koristitite u mysql klijent programu ukucajte select version();.
Korišćenje transakcija moguće je samo sa InnoDB i BDB tabelama. Ako niste sigurni da li ih vaš server podržava koristite show engines query koji će izlistati podržane tipove tabela. Ako je potrebno omogućiti neki od tipova tabela, konsultujte MySQL manual kako da to učinite (ili kontaktirajte support ako je u pitanju MySQL server na vašem hostingu). U našem primeru koristićemo se InnoDB tabelama, potpuno isto važi i za BDB tabele.

Za početak kreirajmo jednu test tabelu:

create table test(
ime char(20) not null unique)
engine = innodb;

A sada da upotrebimo jednu transakciju:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ime) values('Pera');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(ime) values('Mikica');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| ime    |
+--------+
| Mikica |
| Pera   |
+--------+
2 rows in set (0.02 sec)

Kao što vidite, start transaction koristimo za početak starta transakcije. Nakon uspešno izvršena 2 upita koristili smo commit za kraj transakcije, koja je u ovom slučaju uspešno obavljena. Važna stvar je da nijedna od promena tokom transakcije (recimo unošenje novog imena Pera) nije vidljiva drugim klijentima sve dok se ne izvrši commit. To je osobina Izolacije (I u ACID) koju smo pominjali ranije. A sada da prikažemo jednu neuspelu transakciju:

mysql> select * from test;
+--------+
| ime    |
+--------+
| Mikica |
| Pera   |
+--------+
2 rows in set (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ime) values('Laza');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(ime) values('Mikica');
ERROR 1062 (23000): Duplicate entry 'Mikica' for key 1
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| ime    |
+--------+
| Mikica |
| Pera   |
+--------+
2 rows in set (0.00 sec)

U slučaju gore tokom transakcije došlo je do greške, jer smo pokušali da unesemo već postojeću vrednost (Mikica) u polje definisano kao jedinstveno (unique). Kao rezultat došlo je do greške, pa smo izvršili rollback iskaz. Kao što vidite tabela sadrži iste slogove kao i pre početka transakcije, tj. ime Laza koje je prvo uneto na početku transakcije nije ostalo upisano jer transakcija u celini nije uspela.

Počev od verzije MySQL 4.1.1 moguće je snimiti tačku u transakciji (savepoint) na koju se možemo vratiti rollback k-dom. Evo primera:

mysql> select * from test;
+--------+
| ime    |
+--------+
| Mikica |
| Pera   |
+--------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ime) values('Laza');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint tacka1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ime) values('Mirko');
Query OK, 1 row affected (0.00 sec)

mysql> rollback to savepoint tacka1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ime) values('Dragan');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| ime    |
+--------+
| Dragan |
| Laza   |
| Mikica |
| Pera   |
+--------+
4 rows in set (0.00 sec)

Kao što vidite, nakon unešenog prvog imena (Laza) snimili smo poziciju u transakciji kao tacka1. Unos imena Mirko je poništen vraćanjem na tačku1, nakon koje smo uneli uspešno ime Dragan i odradili commit. Na kraju transakcije, vidi se da ime Mirko nije uneto jer smo se vratili na tačku u transakciji gde to ime nije postojalo.

Eto, toliko o transakcijama u ovom kratkom tutorijalu, nadam da će biti dovoljno da vas zainteresuje za njihovo korišćenje. Za više informacija o transakcijama pogledajte MySQL Manual ili konsultujte odgovarajuću literaturu. Moja preporuka, MySQL 3rd Edition by Paul DuBois.