Introducere
Erorile de la nivelul aplicatiilor si a echipamentelor de calcul pot determina aparitia datelor corupte, date care dau nastere la rezultate distorsionate. Evitarea acestor situatii este mai stringenta pentru organizatiile in care astfel de evenimente perturba intr-o maniera critica buna desfasurare a activitatii.
Exemple de organizatii in care astfel de situatii trebuie evitate cu orice pret sunt cele care activeaza in sistemul bancar. Aparitia unei erori in timpul unei transfer de bani poate duce la pierderea banilor pentru beneficiar. Clientul X transfera suma de 100 RON catre clientul Y. O varianta simplificata de realizare a operatiei de transfer poate fi implementata la nivelul unui sistem de gestiune a bazelor de date, prin doua operatii de actualizare. Prima operatie scade 100 RON din soldul clientului X, iar cea de a doua adauga cei 100 RON la soldul clientului Y. Daca apare o eroare dupa prima operatie de actualizare, suma de 100 de RON este debitata de la clientul X, dar nu mai este transferata la clientul Y. Pentru a evita astfel de situatii se utilizeaza tranzactii.
O tranzactie reprezinta un set de instructiuni SQL care sunt executate ca o singura unitate. Pentru ca o tranzactie sa fie finalizata, si pentru a salva modificarile aduse, toate instructiunile de la nivelul tranzactiei trebuie sa fie executate cu succes. Daca o tranzactie nu este finalizata cu succes, toate modificarile aduse de acea tranzactie vor fi sterse, aducand tabelele bazei de date la starea initiala.
Controlul tranzactiilor
Implicit, sistemul de gestiune a bazelor de date MySQL functioneaza cu modul autocommit activat. Acest aspect implica faptul ca orice instructiune pentru manipularea datelor (UPDATE, INSERT, DELETE) este considerata o tranzactie finalizata, iar modificarile realizate sunt stocate permanent.
Controlul tranzactiilor este asigurat prin intermediul urmatoarelor instructiuni: START TRANSACTION, BEGIN, COMMIT, ROLLBACK si SET autocommit. Sintaxele pentru aceste instructiuni sunt precizate mai jos.
START TRANSACTION [caracteristica_tranzactie [, caracteristica_tranzactie] ...] caracteristica_tranzactie: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY } BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}
Instructiunile START TRANSACTION si BEGIN sunt folosite pentru pornirea tranzactiilor. BEGIN este un alias al instructiunii START TRANSACTION. Se recomanda utilizarea instructiunii START TRANSACTION pentru pornirea tranzactiilor, deoarece aceasta beneficiaza de modificatori suplimentari. START TRANSACTION si BEGIN dezactiveaza modul de operare autocommit pentru o serie de instructiuni. Modul de operare autocommit ramane dezactivat pana cand tranzactia este finalizata prin una din instructiunile COMMIT sau ROLLBACK. La finalizarea tranzactiei modul autocommit revine la starea precursoare tranzactiei.
Folosind sintaxa START TRANSACTION pot fi specificati modificatori suplimentari care controleaza modul de operare al tranzactiei. Trei astfel de modificatori sunt disponibili pentru instructiunea START TRANSACTION:
• WITH CONSISTENT SNAPSHOT: modificatorul declanseaza o citire consistenta (consistent read); optiunea este valabila doar pentru motorul de stocare InnoDB; daca datele interogate in interiorul tranzactiei au fost modificate de operatii din exteriorul tranzactiei curente, acestea sunt reconstruite la versiunea din timpul declansarii citirii consistente; datele sunt privite ca o copie de la un anumit moment in timp (SNAPSHOT); singurul nivel de izolare al tranzactiilor care permite citire consistenta este REPEATABLE READ; pentru celelalte nivele de izolare clauza este ignorata;
• READ WRITE: specifica un mod de acces al tranzactiei, care permite modificarea sau blocarea tabelelor utilizate in tranzactie; daca nu este specificat nici un mod de acces al tranzactiei, implicit se considera READ WRITE;
• READ ONLY: specifica un mod de acces al tranzactiei, care nu permite ca pe parcursul desfasurarii acesteia sa fie modificate sau blocate tabele vizibile in alte tranzactii; restrictia nu se aplica tabelelor temporare.
Declansarea unei noi tranzactii va determina si urmatoarele:
• operatiile nefinalizate de la nivelul unor tranzactii din sesiunea curenta vor fi salvate permanent (COMMIT);
• tabelele blocate prin instructiunea LOCK TABLES, altele decat cele blocate global, vor fi deblocate ca si cum ar fi utilizata instructiunea UNLOCK TABLES.
Instructiunea COMMIT este utilizata pentru a specifica finalul unei tranzactii. La executia acestei instructiuni sistemul salveaza rezultatele tranzactiei permanent. Modificarile aduse datelor nu mai pot fi anulate prin intermediul instructiunii ROLLBACK.
In exemplul de mai sus avem deschise doua sesiuni concurente la un server MySQL. In prima sesiune este deschisa o tranzactie care include doua instructiuni de manipulare a datelor, o adaugare a unei noi inregistrari, respectiv o actualizarea a valorilor de la nivelul unei alte inregistrari. Actualizarea se desfasoara cu o valoare preluata din inregistrarea nou introdusa in tabelul sakila.actor.
mysql> START TRANSACTION; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.04 sec) mysql> SELECT last_name INTO @last_name -> FROM sakila.actor -> WHERE actor_id = LAST_INSERT_ID(); Query OK, 1 row affected (0.04 sec) mysql> UPDATE sakila.actor -> SET last_name = @last_name -> WHERE actor_id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.05 sec)
Modificarile asigurate prin intermediul primei sesiuni sunt disponibile in cea de-a doua sesiune doar in momentul finalizarii tranzactiei prin instructiunea COMMIT.
mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 1 | PENELOPE | JOHNSON | 2021-04-04 15:58:56 | | 201 | BRUCE | JOHNSON | 2021-04-04 15:58:56 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.00 sec)
O tranzactie este finalizata implicit daca sunt utilizate:
• instructiuni de definire a datelor (DDL) – CREATE, ALTER, DROP (versiunile pentru baze de date, tabele, vizualizari, proceduri stocate, etc.);
• instructiuni care actualizeaza tabelele din baza de date sistem, mysql – ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD;
• instructiuni de control al tranzactiilor – BEGIN, START TRANSACTION, SET autocommit=1 (tranzactiile nu pot fi imbricate);
• instructiuni de blocare a tabelelor; la utilizarea instructiunii UNLOCK TABLES, daca au fost blocate tabele non-tranzactionale cu instructiunea LOCK TABLES;
• instructiuni de administrare – ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, RESET;
• instructiuni de control al replicarii – START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO.
Pentru a anula modificarile aduse bazei de date prin intermediul tranzactiei curente, se utilizeaza instructiunea ROLLBACK. Actualizarile realizate asupra detelor din tabele netranzactionale nu vor putea fi anulate, iar la apelul instructiunii ROLLBACK serverul va genera un avertisment.
Chiar daca la nivelul tranzactiei sunt operate doua modificari asupra datelor din tabelul sakila.actor, datele revin la versiunea precursoare tranzactiei prin finalizarea acesteia fara salvarea rezultatelor. Acest lucru este asigurat prin intermediul instructiunii ROLLBACK.
mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.04 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.06 sec) mysql> SELECT last_name INTO @last_name -> FROM sakila.actor -> WHERE actor_id = LAST_INSERT_ID(); Query OK, 1 row affected (0.04 sec) mysql> UPDATE sakila.actor -> SET last_name = @last_name -> WHERE actor_id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 1 | PENELOPE | JOHNSON | 2021-04-06 05:58:20 | | 201 | BRUCE | JOHNSON | 2021-04-06 05:58:20 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.04 sec)
Cuvantul cheie WORK este optional pentru instructiunile COMMIT si ROLLBACK. Aceste doua instructiuni prezinta si alte doua clauze care permit controlul finalizarii tranzactiei:
• AND CHAIN: la finalizarea tranzactiei curente este declansata o noua tranzactie care are acelasi nivel de izolare si acelasi mod de acces;
• RELEASE: la finalizarea tranzactiei sesiunea este inchisa si toate setarile de sesiune sunt pierdute;
• NO: cuvantul cheie este utilizat pentru a dezactiva unul din modurile de operare CHAIN sau RELEASE, daca acestea au fost activate prin configurarea server-ului.
Prin utilizarea instructiunii SET autocommit se poate activa sau dezactiva modul de operare autocommit pentru sesiunea curenta. Daca se realizeaza dezactivarea modului autocommit, prin precizarea valorii 0 pentru variabila de sesiune autocommit, modificarile aduse inregistrarilor din tabele tranzactionale nu sunt finalizate imediat; in acest caz, salvarea pe disk se realizeaza prin intermediul instructiunii COMMIT, iar ignorarea modificarilor are loc prin intermediul instructiunii ROLLBACK. Variabila autocommit este o variabila de sesiune, prin urmare trebuie setata pentru fiecare sesiune in parte.
mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.05 sec) mysql> SELECT @@SESSION.autocommit; +----------------------+ | @@SESSION.autocommit | +----------------------+ | 1 | +----------------------+ 1 row in set (0.04 sec) mysql> SET autocommit = 0; Query OK, 0 rows affected (0.04 sec) mysql> SELECT @@SESSION.autocommit; +----------------------+ | @@SESSION.autocommit | +----------------------+ | 0 | +----------------------+ 1 row in set (0.04 sec) mysql> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.04 sec) mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | BRUCE | JOHNSON | 2021-04-06 06:11:04 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec) mysql> connect Connection id: 46 Current database: sakila mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.04 sec)
Puncte de salvare
Un punct de salvare este un punct definit in interiorul unei tranzactii. Un astfel de punct de salvare poate fi utilizat pentru a permite anularea partiala a modificarilor efectuate in interiorul unei tranzactii.
Instructiunile care permit controlul punctelor de salvare sunt precizate mai jos:
SAVEPOINT identificator ROLLBACK [WORK] TO [SAVEPOINT] identificator RELEASE SAVEPOINT identificator
Instructiune SAVEPOINT permite definirea unui punct de salvare intermediar. Referirea punctului de salvare se realizeaza prin intermediul unui identificator stabilit in momentul definirii lui. Daca identificatorul este deja utilizat, vechiul punct de salvare este sters si inlocuit cu cel nou.
Pentru a declansa procedura de anulare (ROLLBACK) a modificarilor de la un punct de salvare pana la ultima instructiune rulata, se utilizeaza ROLLBACK TO SAVEPOINT. Cuvintele cheie WORK si SAVEPOINT sunt optionale. Apeland instructiunea ROLLBACK TO SAVEPOINT tranzactia nu este finalizata, vor fi ignorate doar modificarile precursoare punctului de salvare specificat in apel.
RELEASE SAVEPOINT sterge punctul de salvare specificat din lista punctelor de salvare disponibile in tranzactie, fara sa declanseze finalizarea tranzactiei.
Toate punctele de salvare definite in cadrul unei tranzactii vor fi sterse la finalizarea acesteia prin COMMIT sau ROLLBACK.
mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.06 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.05 sec) mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | BRUCE | JOHNSON | 2021-04-06 06:14:34 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec) mysql> SAVEPOINT beforeUpdatePenelopeGuiness; Query OK, 0 rows affected (0.04 sec) mysql> UPDATE sakila.actor -> SET last_name = "JOHNSON" -> WHERE actor_id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 1 | PENELOPE | JOHNSON | 2021-04-06 06:14:34 | | 201 | BRUCE | JOHNSON | 2021-04-06 06:14:34 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec) mysql> ROLLBACK TO SAVEPOINT beforeUpdatePenelopeGuiness; Query OK, 0 rows affected (0.04 sec) mysql> COMMIT; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | BRUCE | JOHNSON | 2021-04-06 06:14:34 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec)
Blocarea obiectelor
Procesul de blocarea se realizeaza asupra unor obiecte SQL si este utilizat pentru a opri modificarea datelor din alte sesiuni concurente. Obiectele pot fi blocate sau deblocate doar pentru sesiunea curenta. Din sesiunea curenta, in care se ruleaza instructiunile de blocare / deblocare, nu se pot bloca obiecte pentru o alta sesiune sau debloca obiectele blocate in alte sesiuni.
O posibila problema care poate aparea prin blocarea obiectelor o reprezinta situatia in care doua tranzactii incearca sa aiba acces la aceleasi date, si fiecare tranzactie are blocate date de care cealalta tranzactie are nevoie pentru a continua. De exemplu: tranzactia 1 are blocat tabelul 1 randurile 10 pana la 30, tranzactia 2 are blocat tabelul 2 randurile 10 pana la 20, tranzactia 1 are nevoie de acces la tabelul 2 randul 15 pentru a continua, tranzactia 2 are nevoie de acces la tabelul 1 randul 20 pentru a continua. Nici una din cele doua tranzactii nu poate continua si nu poate elibera inregistrarile blocate. Tranzactiile au ajuns intr-un impas (deadlock). Fara o interventie de la utilizator sau serverul de gestiune aceste tranzactii vor fi blocate. Motorul de stocare InnoDB asteapta un anumit timp pentru deblocarea resurselor, iar in caz contrar, va finaliza o tranzactie fara stocarea modificarilor (ROLLBACK).
Blocarea la nivel de tabel
Blocarea si deblocarea tabelelor poate fi realizata prin intermediul a doua instructiuni: LOCK TABLES, respectiv UNLOCK TABLES.
LOCK TABLES nume_tabel [[AS] alias] tip_blocare [, nume_tabel [[AS] alias] tip_blocare] ... tip_blocare: { READ [LOCAL] | [LOW_PRIORITY] WRITE } UNLOCK TABLES
Instructiunea LOCK TABLES realizeaza blocarea tabelelor pentru sesiunea curenta. Pentru a putea rula instructiunea, utilizatorul trebui sa aibe privilegiul LOCK TABLES si SELECT pentru fiecare obiect / tabel blocat. Se pot bloca tabele de baza si vizualizari. La blocarea vizualizarilor, vor fi blocate si tabelele utilizate in constructia vizualizarii. In aceiasi nota, in cazul tabelelor care prezinra declansatori (triggers) vor fi blocate si tabelele utilizate la nivelul acestora.
Tabelele se pot bloca folosind unul din cele doua tipuri de nivele de acces:
• READ: sesiunea care detine blocarea poate citi inregistrarile din tabel dar nu poate scrie in tabel; alte sesiuni pot citi inregistrarile din tabel dar nu le pot modifica; asupra unui tabel pot fi stabilite mai multe procese de blocare de tip READ, din mai multe sesiuni; din acest motiv o blocare de tip READ mai poarta denumirea de blocare partajata (shared lock); prin utilizarea cuvintului cheie LOCAL se permite altor sesiuni inserarea de inregistrari care nu cauzeaza conflicte in tabelul blocat; pentru motorul de stocare InnoDB cuvantul cheie LOCAL nu are nici un efect;
session 1> LOCK TABLES sakila.actor READ; Query OK, 0 rows affected (0.05 sec) session 1> ... session 1> UNLOCK TABLES; Query OK, 0 rows affected (0.05 sec) session 1> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | ALVIN | DAVIS | 2021-04-04 12:32:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.05 sec)
session 2> -- returneaza rezultatul interogarii session 2> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set (0.00 sec) session 2> -- asteapta deblocarea tabelului sakila.actor session 2> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("ALVIN", "DAVIS"); session 2> -- se insereaza inregistrarea dupa deblocarea tabelului sakila.actor session 2> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("ALVIN", "DAVIS"); Query OK, 1 row affected (9.48 sec) session 2> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | ALVIN | DAVIS | 2021-04-04 12:32:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.05 sec)
• WRITE: sesiunea care detine blocarea poate citii si scrie in tabel; nici o alta sesiune nu are acces la date pana la deblocarea tabelului; o blocare de tip WRITE mai poarta denumirea de blocare exclusiva, deoarece doar o singura sesiune o poate detine in acelasi timp.
session 1> LOCK TABLES sakila.actor WRITE; Query OK, 0 rows affected (0.05 sec) session 1> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.05 sec) session 1> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | BRUCE | JOHNSON | 2021-04-04 12:41:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.05 sec) session 1> UNLOCK TABLES; Query OK, 0 rows affected (0.05 sec)
session 2> -- asteapta deblocarea tabelului sakila.actor session 2> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; session 2> -- returneaza rezultatul interogarii dupa deblocarea tabelului sakila.actor session 2> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | BRUCE | JOHNSON | 2021-04-04 12:41:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (24.79 sec)
Pentru a elibera obiectele blocate din sesiunea curenta se utilizeaza instructiunea UNLOCK TABLES. În cazul in care o sesiune ruleaza o a doua cerere de blocare de tabele folosind LOCK TABLES toate tabelele blocate la apelul precedent vor fi deblocate. Din acest motiv toate tabelele care trebuie blocate intr-o sesiune trebuie specificate printr-o singura instructiune LOCK TABLES.
Deblocarea tabelelor este de asemenea declansata si de apelul instructiunii START TRANSACTION pentru inceperea unei tranzactii.
Finalizarea unei sesiuni determina si deblocarea tabelelor blocate in sesiunea respectiva.
Un avantaj al blocarii la nivel de tabel este imposibilitatea aparitiei unei situatii de tip deadlock. Prin faptul ca toate tabele necesare trebuie blocate intr-o singura instructiune, se asigura faptul ca tranzactia nu trebuie sa astepte deblocarea unor resurse. Principalul dezavantaj al blocarii la nivel de tabel apare in cazul in care se doreste modificarea unei inregistrari dintr-un tabel cu milioane de inregistrari; in acest fel se blocheaza tot tabelul si nici o alta sesiune nu mai are acces al el, asigurandu-se un nivel de concurenta scazut.
Blocarea tabelelor interactioneaza cu tranzactiile in urmatoarele moduri:
• instructiunea LOCK TABLES nu poate fi utilizata in interiorul unei tranzactii deoarece implicit va finaliza orice tranzactie deschisa inainte de a bloca tabelele;
• UNLOCK TABLES va finaliza tranzactia deschisa, dar doar daca sunt tabele blocate prin instructiunea LOCK TABLES;
• deschiderea unei tranzactii folosind instructiunea START TRANSACTION implicit va deblocate orice tabel blocat;
• utilizarea corecta a instructiunilor LOCK TABLES si UNLOCK TABLES in cadrul unei tranzactii presupune pornirea tranzactia prin setarea parametrului autocommit pe 0, blocarea tabelelor necesare, finalizarea tranzactiei folosind instructiunea COMMIT, deblocarea tabelelor; chiar si in acest caz pot aparea blocaje in deblocarea tabelelor datorita faptului ca blocarea la nivel de tabel este gestionata de sistemul de gestiune si nu de motorul de stocare InnoDB.
mysql> SET autocommit=0; Query OK, 0 rows affected (0.04 sec) mysql> LOCK TABLES sakila.actor WRITE; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("BRUCE", "JOHNSON"); Query OK, 1 row affected (0.04 sec) mysql> UPDATE sakila.actor -> SET last_name = "JOHNSON" -> WHERE actor_id = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.05 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.05 sec) mysql> SET autocommit=1; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 1 | PENELOPE | JOHNSON | 2021-04-04 12:15:59 | | 201 | BRUCE | JOHNSON | 2021-04-04 12:15:58 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.04 sec)
Blocarea la nivel de inregistrare
Utilizand acest mod de blocare se permite concurenta maxima. Vor fi blocate doar inregistrarile necesare, restul datelor fiind disponibile pentru celelalte sesiuni. Motorul de stocare InnoDB implementeaza blocarea la nivel de inregistrare utilizand instructiunea SELECT si doua clauze speciale.
Utilizand instructiunea SELECT cu clauza LOCK IN SHARE MODE, se vor bloca randurile citite in modul partajat. Alte sesiuni pot citi randurile blocate, dar nu le pot modifica decat dupa finalizarea tranzactiei curente.
session 1> START TRANSACTION; Query OK, 0 rows affected (0.04 sec) session 1> SELECT last_name INTO @last_name -> FROM sakila.actor -> WHERE actor_id = 1 LOCK IN SHARE MODE; Query OK, 1 row affected (0.04 sec) session 1> INSERT INTO sakila.actor (first_name, last_name) -> VALUES ("REYA", @last_name); Query OK, 1 row affected (0.04 sec) session 1> COMMIT; Query OK, 0 rows affected (0.04 sec)s
session 2> -- asteapta deblocarea inregistrarii din tabelul sakila.actor session 2> UPDATE sakila.actor -> SET last_name = "JOHNSON" -> WHERE actor_id = 1; session 2> -- se finalizeaza actualizarea precedenta din cadrul sesiunii curente session 2> -- dupa finalizarea tranzactiei din sesiunea 1 session 2> UPDATE sakila.actor -> SET last_name = "JOHNSON" -> WHERE actor_id = 1; Query OK, 1 row affected (10.56 sec) Rows matched: 1 Changed: 1 Warnings: 0 session 2> SELECT * -> FROM sakila.actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 1 | PENELOPE | JOHNSON | 2021-04-04 15:28:56 | | 201 | REYA | GUINESS | 2021-04-04 15:28:36 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.01 sec)
A doua clauza utilizata cu instructiunea SELECT pentru a bloca inregistrari este FOR UPDATE. Blocheaza randurile ca si cum ar fi rulata o instructiune UPDATE pe acestea. Alte tranzactii nu pot modifica aceste randuri, nu le pot bloca la citire folosind clauza LOCK IN SHARE MODE, si nu pot sa citeasca inregistrarile respective in anumite nivele de izolare.