Razumevanje MySQL transakcija iz korena | MySQL tehnički forum
Pojam transakcije
MySQL transakcija je jedna ili više operacija nad bazom koje se ili sve uspešno izvrše, ili sve neuspešno rolbekuju.
Transakcije se ostvaruju putem logova transakcija, a logovi transakcija obuhvataju: redo log i undo log.
Stanja transakcije
Aktivna (active)
Kada se operacije nad bazom koje pripadaju transakciji još uvek izvršavaju, kažemo da se transakcija nalazi u aktivnom stanju.
Delimično potvrđena (partially committed)
Kada se poslednja operacija u transakciji završi, ali pošto su sve operacije izvršene u memoriji, efekti još uvek nisu ispisani na disk, kažemo da se transakcija nalazi u stanju delimične potvrde.
Neuspešna (failed)
Kada transakcija, dok je u aktivnom ili delimično potvrđenom stanju, naiđe na neku grešku (grešku same baze, grešku operativnog sistema, nagomilani prekid napajanja itd.) i ne može da nastavi sa izvršavanjem, ili kada ručno zaustavimo izvršavanje transakcije, kažemo da se transakcija nalazi u neuspešnom stanju.
Prekinuta (aborted)
Ako transakcija, nakon što se delimično izvršila, pređe u neuspešno stanje, poništavamo efekte koje je neuspešna transakcija ostavila na trenutnu bazu; taj proces poništavanja nazivamo rolbek (rollback).
Kada se rolbek završi, odnosno kada se baza vrati u stanje pre izvršavanja transakcije, kažemo da se transakcija nalazi u prekinutom stanju.
Potvrđena (committed)
Kada transakcija u stanju delimične potvrde sinhronizuje sve izmenjene podatke na disk, možemo reći da je transakcija prešla u potvrđeno stanje.
Iz slike se takođe vidi da samo kada transakcija pređe u potvrđeno ili prekinuto stanje, njen životni ciklus se smatra završenim. Za potvrđene transakcije, izmene koje su napravljene u bazi trajno stupaju na snagu; za transakcije u prekinutom stanju, sve izmene napravljene u bazi se rolbekuju do stanja pre izvršavanja transakcije.
Namena transakcije
Transakcije prvenstveno služe da obezbede konzistentnost podataka pri složenim operacijama nad bazom, posebno pri konkurentnom pristupu podacima.
MySQL transakcije se uglavnom koriste za obrudu velikih količina podataka visoke složenosti.
Karakteristike transakcije
Atomarnost (Atomicity, poznata i kao nedeljivost)
Operacije podataka u transakciji se ili sve uspešno izvrše, ili sve neuspešno rolbekuju do stanja pre izvršavanja, kao da se ta transakcija nikada nije ni desila.
Izolacionost (Isolation, poznata i kao nezavisnost)
Više transakcija je međusobno izolovano i ne utiče jedna na drugu. Baza dozvoljava više konkurentnih transakcija da istovremeno čitaju i pišu i menjaju njene podatke; izolacionost sprečava nekonzistentnost podataka koja bi nastala zbog preplitanja kad bi se više transakcija izvršavalo konkurentno.
Četiri nivoa izolacije:
1. Čitanje nepotvrđenog (Read uncommitted)
2. Čitanje potvrđenog (Read committed)
3. Ponovljivo čitanje (Repeatable read)
4. Serijalizacija (Serializable)Konzistentnost (Consistency)
Pre i posle operacija u transakciji, podaci ostaju u istom (konzistentnom) stanju, integritet baze nije narušen.
Atomarnost i izolacionost imaju ključni uticaj na konzistentnost.
Trajnost (Durability)
Kada se operacije transakcije završe, podaci se ispisuju na disk radi trajnog čuvanja; ne gube se čak ni u slučaju kvara sistema.
Sintaksa transakcija
Podaci
Kreiranje tabele:
create table account(
-> id int(10) auto_increment,
-> name varchar(30),
-> balance int(10),
-> primary key (id));
Ubacivanje podataka:
insert into account(name,balance) values('Lao Wangova supruga',100),('Lao Wang',10);mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wangova supruga | 100 |
| 2 | Lao Wang | 10 |
+----+--------------+---------+Lao Wangova supruga drži 100 juana na svom WeChat računu, namenjeno za džeparac koji Lao Wangu isplaćuje svakog meseca — kad se posebno potrudi, dobije više. Lao Wang ima i svoj mali trezor i već je uspeo da uštedi 10 juana džeparca, hehe.
begin
Način pokretanja transakcije 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SQL operacije transakcije......start transaction [modifikatori]
Modifikatori:
1. read only //samo čitanje
2. read write //čitanje i pisanje, podrazumevano
3. WITH CONSISTENT SNAPSHOT //konzistentno čitanjeNačin pokretanja transakcije 2
mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> SQL operacije transakcije......Ako se podesi read only, izmena podataka prijavljuje grešku:
mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set balance=banlance+30 where id = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.commit
Izvršava potvrdu transakcije; po uspešnoj potvrdi podaci se ispisuju na disk.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)rollback
Izvršava rolbek transakcije, vraćajući je u stanje pre operacija transakcije.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)Ovde treba istaći da se izraz ROLLBACK koristi samo kada programer ručno rolbekuje transakciju; ako transakcija tokom izvršavanja naiđe na greške zbog kojih ne može da nastavi, sama transakcija će se automatski rolbekovati.
Kompletan primer potvrde
U januaru se Lao Wang posebno istakao, pa mu je supruga nagradila sa 20 juana džeparca.
Koraci izvršavanja:
1. Pročitati podatke sa računa Lao Wangove supruge
2. Skinuti 20 juana sa računa Lao Wangove supruge
3. Pročitati podatke sa računa Lao Wanga
4. Dodati 20 juana na račun Lao Wanga
5. Izvršiti uspešnu potvrdu
6. Sada Lao Wangova supruga na računu ima samo 80 juana, a Lao Wang ima 30 juana — Lao Wang je oduševljenmysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set balance=balance+20 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)Stanje na računima:
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wangova supruga | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+Kompletan primer rolbeka
U februaru se Lao Wang ispočetka pokazao odlično, uporno je radio kućne poslove i šetao psa, pa mu je supruga htela da da 25 juana džeparca. Ali Lao Wang ne podnosi pohvalu — baš dok mu je supruga slala džeparac, na telefonu koji je ležao na stolu iznenada je primetila poruku devojčice: „Dragi brate Wange...". Supruga je pukla od besa, u jednom mah opozvala transfer i otkazala mu džeparac za taj mesec.
Koraci izvršavanja:
1. Pročitati podatke sa računa Lao Wangove supruge
2. Skinuti 25 juana sa računa Lao Wangove supruge
3. Pročitati podatke sa računa Lao Wanga
4. Dodati 25 juana na račun Lao Wanga
5. Sada Lao Wangova supruga opoziva prethodne operacije
6. Sada stanje na računima Lao Wanga i njegove supruge ostaje isto kao pre operacijamysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set balance=balance-25 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set balance=balance+25 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)Stanje na računima:
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wangova supruga | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+Skladišni enginei koji podržavaju transakcije
1. InnoDB
2. NDBSkladišni enginei koji ne podržavaju transakcije — na primer, ako izvodimo transakcije nad MyISAM-om, transakcija neće stupiti na snagu, SQL naredbe se automatski potvrđuju, pa je rolbek za skladišne enginee koji ne podržavaju transakcije nevažeći.
create table tb1(
-> id int(10) auto_increment,
-> name varchar(30),
-> primary key (id)
-> )engine=myisam charset=utf8mb4;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1(name) values('Tom');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)
mysql> rollback;//rolbek je nevažeći
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)Podešavanje i provera transakcija
Provera stanja automatske potvrde transakcija:
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+Podrazumevano je uključena automatska potvrda transakcija — svaka izvršena SQL naredba se odmah potvrđuje.
Da bismo u ovom stanju radili s transakcijama, moramo eksplicitno pokrenuti (begin ili start transaction) i potvrditi (commit) ili rolbekovati (rollback).
Ako se autocommit podesi na OFF, transakcija se stvarno izvršava tek kada izvršimo potvrdu (commit) ili rolbek (rollback).
Načini isključivanja automatske potvrde
Prvi
Eksplicitno pokrenuti transakciju izrazima START TRANSACTION ili BEGIN.
Drugi
Postaviti sistemsku promenljivu autocommit na OFF.
SET autocommit = OFF;Implicitna potvrda
Kada pomoću izraza START TRANSACTION ili BEGIN pokrenemo transakciju, ili kada sistemsku promenljivu autocommit podesimo na OFF, transakcija se neće automatski potvrđivati. Ali ako nakon toga unesemo izvesne naredbe, one će „tiho“ potvrditi transakciju, baš kao da smo uneli izraz COMMIT. Ova situacija u kojoj pojedine specifične naredbe izazivaju potvrdu transakcije naziva se implicitna potvrda.
Jezik za definiciju podataka koji definiše ili menja objekte baze (Data definition language, skraćeno: DDL)
Objekti baze podataka su stvari poput baze, tabele, prikaza (view), uskladištene procedure i slično. Kada naredbama CREATE, ALTER, DROP menjamo te takozvane objekte baze, implicitno se potvrđuje transakcija kojoj pripadaju prethodne naredbe.
BEGIN;
SELECT ... # jedna naredba u transakciji
UPDATE ... # jedna naredba u transakciji
... # ostale naredbe u transakciji
CREATE TABLE ... # ova naredba implicitno potvrđuje transakciju kojoj pripadaju prethodne naredbeImplicitno korišćenje ili izmena tabela u bazi mysql
Implicitno korišćenje ili izmena tabela u bazi mysql.
Kada koristimo naredbe poput ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD, takođe se implicitno potvrđuje transakcija kojoj pripadaju prethodne naredbe.
Naredbe za kontrolu transakcija ili zaključavanje
Naredbe za kontrolu transakcija ili zaključavanje.
Kada, pre nego što je jedna transakcija potvrđena ili rolbekovana, ponovo pokrenemo drugu transakciju naredbom START TRANSACTION ili BEGIN, prethodna transakcija se implicitno potvrđuje.
BEGIN;
SELECT ... # jedna naredba u transakciji
UPDATE ... # jedna naredba u transakciji
... # ostale naredbe u transakciji
BEGIN; # ova naredba implicitno potvrđuje transakciju kojoj pripadaju prethodne naredbeIli, kada je trenutna vrednost sistemske promenljive autocommit OFF, a mi je ručno prebacimo na ON, takođe se implicitno potvrđuje transakcija kojoj pripadaju prethodne naredbe.
Ili, korišćenje naredbi za zaključavanje poput LOCK TABLES, UNLOCK TABLES takođe implicitno potvrđuje transakciju kojoj pripadaju prethodne naredbe.
Naredbe za učitavanje podataka
Na primer, kada pomoću naredbe LOAD DATA masovno uvozimo podatke u bazu, takođe se implicitno potvrđuje transakcija kojoj pripadaju prethodne naredbe.
Neke naredbe vezane za MySQL replikaciju
Korišćenje naredbi START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO takođe implicitno potvrđuje transakciju kojoj pripadaju prethodne naredbe.
Ostale naredbe
Korišćenje naredbi ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, RESET takođe implicitno potvrđuje transakciju kojoj pripadaju prethodne naredbe.
Sačuvane tačke transakcije
Pojam
U naredbama baze koje odgovaraju transakciji postavimo nekoliko tačaka; prilikom poziva ROLLBACK možemo navesti do koje tačke želimo da se rolbekujemo, umesto da se vraćamo na sam početak.
Sa sačuvanim tačkama transakcije, prilikom složenih operacija transakcije ne moramo da brinemo da ćemo pri prvoj grešci odmah rolbekovati do početnog stanja — kao da preko noći sve krene ispočetka.
Sintaksa korišćenja
1. SAVEPOINT ime_tačke;//označava sačuvanu tačku
2. ROLLBACK TO [SAVEPOINT] ime_tačke;//rolbekuje do određene sačuvane tačke
3. RELEASE SAVEPOINT ime_tačke;//brišemysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint action1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wangova supruga | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+
mysql> update account set balance=balance+30 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback to action1;//rolbek do sačuvane tačke action1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+----+--------------+---------+
| id | name | balance |
+----+--------------+---------+
| 1 | Lao Wangova supruga | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+Referenca: Juejin brošura „MySQL — kako radi: razumevanje MySQL-a iz korena"
Knjiga „MySQL visoke performanse"
Referenca: https://learnku.com/articles/39938, priredio: Chenmo Wang Er