Introducere în tranzacția în MySQL. După cum sa dovedit, toată lumea știe, dar nu toată lumea înțelege

Tranzacția este un mecanism care vă permite să interpretați mai multe modificări în baza de date ca o singură operație. Fie toate modificările vor fi luate sau toate vor fi respinse. Nici o altă sesiune nu poate accesa tabelul în timp ce există o tranzacție deschisă, în cadrul căreia orice schimbări sunt efectuate în acest tabel. Dacă încercați să faceți o selecție de date imediat după schimbarea, toate modificările efectuate vor fi disponibile.

Un astfel de mecanism de baze de date suport de tranzacție, cum ar fi INNODB sau BDB, începe o tranzacție de tranzacție de start. Tranzacția este finalizată la confirmarea sau anularea modificărilor. Puteți completa tranzacția cu două comenzi. Command Command salvează toate modificările din baza de date. Comanda Rollback anulează toate modificările.

În exemplul, se creează o masă cu suport de tranzacție, datele sunt introduse în acesta, apoi este lansată o tranzacție, în cadrul căreia se va șterge datele și se efectuează reluarea tranzacției (anularea ștergerii):

Creați tabelul de tabel_innodb (ID Int (11) nu NULL Auto_increment, Nume Varchar (150) Implicit , tasta primară (ID)) Motor \u003d Charset implicit INNODB \u003d UTF8; Inserați în valori de eșantion_innodb (1, "Alexander"), (2, "Dmitri"); Începeți tranzacția; Ștergeți de la eșantion_innodb unde id \u003d 1; Ștergeți de la eșantion_innodb unde id \u003d 2; Rolback;

Deoarece a avut loc relansarea tranzacției, datele din tabel nu au fost șterse.

Și dacă în loc de Rolback, am scris o comitere, atunci ambele linii vor fi șterse.

Sunt necesare tranzacții atunci când este necesar ca mai multe cereri să fie aplicate și implementate cu exactitate "simultan", sau nici unul dintre ele nu a fost îndeplinit dacă ceva nu merge bine.

De exemplu, puteți aduce sistemul de plată pe un site. La momentul cumpărării, comanda trebuie marcată ca plătită și, în același timp, trebuie să scrieți banii din bilanțul utilizatorului. Dacă ceva este greșit - va fi fie un utilizator fără un produs achiziționat și fără bani sau un magazin fără un produs și fără bani. Și cu ajutorul tranzacțiilor, putem evita cu ușurință acest lucru.

Pentru o datorie, trebuie să dețin uneori interviuri în poziția "[Senior | Junior] dezvoltator Python / Django", "Timlid". Pentru marea mea surpriză, am constatat că 9 din 10 solicitanți, în rezumatul despre care cuvintele "MySQL / InnoDB / Tranzacții / declanșatoare / Proc stocate etc." sunt absolut nimic nu poate spune despre experiența lor trecută cu ei. Nu este o singură descriere a opțiunii de utilizare, din păcate, nu am primit niciodată.

Apoi, am oferit să încerc să oferim o opțiune de soluție pentru următoarea situație:

Să presupunem că suntem un serviciu online, care, la rândul său, se bucură de unele API externi plătiți (activarea serviciului, conținutul plătit sau că sufletul dvs. este mulțumit), adică serviciul nostru plătește bani pentru utilizarea API-ului. Utilizatorul din sistemul nostru creează o solicitare de activare a serviciului, completează toate câmpurile și pe ultima pagină a butonului "Activare Service". Adică, la momentul trimiterii unei solicitări HTTP, avem o intrare în baza noastră de date (solicitarea de a activa serviciul). Care este algoritmul nostru? - Cere și eu continuu:

Luați soldul utilizatorului din baza de date;
- dacă soldul este suficient, trageți API-ul;
- Dacă totul este bine, atunci scriem suma soldului pentru serviciu, actualizați, comisia, altfel vă răsuciți înapoi;
- Răspundem utilizatorului.

Totul părea să fie trivial, dar când aduc prima problemă și cea mai evidentă sub formă de 10 cereri competitive (pe care toți la început primesc același echilibru și să înceapă să apeleze la API), soluțiile încep să ofere cea mai sofisticată , variind de la interpretarea a 5 selectori (merită o mărturisire, nu am înțeles nimic în acest exemplu de realizare), utilizarea de contoare auto-dulap, haină externă, tabele noi în baza de date, alunecări și încă nu înțeleg ce.

După cum știți (știau toți candidații!), INNODB în MySQL oferă un mecanism tranzacțional și posibilitatea de blocare a liniilor. Pentru a aplica această blocare cea mai scurtă, este suficientă pentru a adăuga o expresie de actualizare la sfârșitul select-A, de exemplu:

Selectați * din cererile unde ID \u003d 5 pentru actualizare

Tranzacția începe și toate celelalte sesiuni la baza de date nu vor putea să efectueze o cerere similară înainte de finalizarea tranzacției noastre, ei vor aștepta pur și simplu. Pentru a citi aceeași înregistrare va fi disponibilă într-o stare care depinde de nivelul de izolare a tranzacțiilor.

De asemenea, merită remarcat faptul că utilizarea actualizării este mai bună de a face cu autocommistul oprit, deoarece, indiferent de ceea ce ați fost blocat, după prima actualizare, locomoția.

Se pare că este un pic, pare evident, dar 9 din 10 ...

Actualizare.
Fosta nume "Tranzacție în MySQL", care nu este dezvăluită în articol a fost înlocuită cu "tranzacții în MySQL și selectată pentru actualizare"

Zy.
Articolul nu spune că API trebuie să tragă în cadrul tranzacției și ce să facă în caz de eșec și cum să proceseze situații excepționale.

Mecanismul tranzacțional este susținut numai de INNODB și BDB. Prin urmare, toate tabelele cu care doriți să lucrați prin tranzacții ar trebui să fie convertite la tipul corespunzător. Poate sa .

În mod implicit, MySQL funcționează în modul AutoCommit. Aceasta înseamnă că rezultatele executării oricărui operator SQL, schimbarea datelor, vor fi imediat salvate.
Modul de autocommit poate fi oprit ca acesta: Setați autocommit \u003d 0;

Dacă doriți să treceți de la modul de autocommit doar pentru a efectua o singură secvență de comenzi, puteți utiliza comanda de tranzacție Începe sau Start pentru a face acest lucru (începând cu versiunea MySQL 4.0.11).

Un exemplu de interogare SQL care stabilește numărul de bunuri selectate prin cumpărarea în tabelul de corecții și face modificări la tabelul de mărfuri:
Începeți tranzacția; Introduceți în corecții SET ID_GOODOS \u003d: ID_GOODLODS, NUMĂR \u003d: Număr, ID_ODERS \u003d: ID_ODaters pe butonul duplicat Număr de actualizare a cheii \u003d Număr +: Număr; Actualizarea bunurilor set rezerve \u003d rezervă +: Număr, disponibil \u003d disponibil -: Număr unde id \u003d: ID_goods; Să se angajeze;

Tranzacția este completată de operatorul comitetului. Modificările sunt salvate. În cazul unei erori într-una dintre solicitări, modificările nu vor fi salvate în nici un tabel.

Dacă aveți nevoie să creați un mecanism de schimbare mai complex, utilizați Savepoint și Rollback la comenzile de salvare

Următorii operatori completează implicit tranzacția (ca și cum ar fi fost emisă înainte de execuție):

Alter Tabel
Drop baza de date.
Încărcați datele principale.
Setați autocommit \u003d 1
Începe.
Indexul de picătură
Blocați tabelele
Începeți tranzacția.
Creați indexul
Drop tabelul
Redenumiți tabelul
Trunchiatul tabelului

PHP PDO oferă mijloacele de lucru cu tranzacțiile. Ele pot fi citite despre ele.

Tranzacții paralele și niveluri de izolare (acces în comun)

Imaginați-vă că, în timpul executării tranzacției de tranzacție 1, un alt utilizator a creat oa doua tranzacție paralelă și a făcut o selecție * de la solicitarea de utilizator după prima solicitare "Introducerea în valori utilizator (ID, NIK) (1," a fost efectuată în noi tranzacția Nikola ').
Ce va vedea utilizatorul celei de-a doua tranzacții?
Va putea vedea înregistrarea introdusă chiar și atunci când rezultatele primei tranzacții nu au fost încă fixate (nu sa întâmplat niciodată)? Sau poate vedea modificările numai după ce rezultatele primei tranzacții vor fi fixate?

Se pare că există ambele opțiuni. Totul depinde de nivelul de izolare al tranzacției.

Există o tranzacție 4 niveluri de izolare:

0 - Citirea datelor neconfirmate (citirea murdară) (citirea neangajată, citită murdară) - cel mai scăzut nivel de izolație. La acest nivel, este posibil să se citească modificări inadecvate în tranzacțiile paralele. În acest caz, al doilea utilizator va vedea intrarea introdusă de la prima tranzacție nefixată. Nu există nicio garanție că tranzacția nefixată va fi respinsă în orice moment, astfel încât această lectură este o sursă potențială de erori.
1 - citiți datele confirmate (citiți comise) - este posibilă citirea datelor înregistrate numai tranzacțiile înregistrate. Dar la acest nivel există două probleme. În acest mod, liniile care participă la eșantion în cadrul tranzacției nu sunt blocate pentru alte tranzacții paralele, problema nr. 1 implică acest lucru:
"Citirea neaderentului" (Citirea non-repetabilă) este o situație în care câteva probe (selectate) apare în cadrul tranzacției și se efectuează o tranzacție paralelă între aceste eșantioane, care modifică datele implicate în aceste eșantioane. Deoarece tranzacția paralelă a modificat datele, rezultatul pentru următorul eșantion pe aceleași criterii în prima tranzacție va fi diferit.

Numărul de problemă 2 - "Citirea Phantom" - Acest caz este discutat mai jos.
2 - citire repetabilă (citire repetată, instantaneu) - La acest nivel de izolare este, de asemenea, posibilă citirea numai a datelor tranzacții fixe. De asemenea, la acest nivel, nu există nicio problemă a "citirii ne-refuzate", adică liniile care participă la eșantion în cadrul tranzacției sunt blocate și nu pot fi modificate de alte tranzacții paralele. Dar mesele nu sunt blocate în întregime. Din acest motiv, problema "citirii fantomei" rămâne. "Citirea Phantom" este atunci când, în timpul executării unei tranzacții, rezultatul acelorași eșantioane se poate schimba datorită faptului că întreaga masă este blocată, dar numai acele linii care participă la eșantion. Aceasta înseamnă că tranzacțiile paralele pot introduce șiruri de caractere în tabelul în care se efectuează eșantionul, astfel încât cele două selectări * din cererile de tabel pot da un rezultat diferit la momente diferite la introducerea tranzacțiilor paralele de date.
3 - tranzacții serializabile (serializabile) - serializabile. Cel mai fiabil nivel de izolare a tranzacțiilor, dar în același timp cel mai lent. La acest nivel, nu există probleme de tranzacții paralele, dar pentru acest lucru va trebui să plătească viteza sistemului, iar viteza în majoritatea cazurilor este extrem de importantă.

În mod prestabilit, nivelul de izolare nr. 2 este instalat în MySQL (citite repetat). Și, după cum cred, dezvoltatorii MySQL nu au făcut în zadar prestabilit exact acest nivel, deoarece este cel mai de succes pentru majoritatea cazurilor. Prima dată când poate părea că cea mai bună opțiune numărul 3 este cea mai fiabilă, dar în practică puteți experimenta o mare inconvenientă din cauza muncii foarte lente a aplicației dvs. Amintiți-vă că depinde în mare măsură de faptul că nu este deloc nivelul de izolare a tranzacțiilor în baza de date și despre modul în care aplicația dvs. este proiectată. Cu programarea competentă, puteți utiliza chiar și cel mai scăzut nivel de izolare a tranzacțiilor - totul depinde de caracteristicile structurii și alfabetizării dezvoltării aplicației dvs. Dar nu este necesar să se străduiască pentru cel mai mic nivel de izolație - nu, doar dacă nu utilizați modul cel mai sigur, trebuie să vă amintiți despre problemele tranzacțiilor paralele, în acest caz că nu sunteți confuzi și faceți totul corect.

SET TRANSACTION - Acest operator stabilește nivelul de izolare a următoarei tranzacții, la nivel global sau numai pentru sesiunea curentă.

Stabilirea nivelului de izolare a tranzacției (citiți necommunit | Citiți comited | repetabilă citire | serializable)

Compușii existenți nu sunt afectați. Pentru a efectua acest operator, trebuie să aveți un super privilegiu. Aplicarea cuvântului cheie de sesiune stabilește nivelul implicit al tuturor tranzacțiilor viitoare numai pentru sesiunea curentă.

De asemenea, puteți seta nivelul inițial de izolare globală pentru serverul MySQLD, rulându-l cu opțiunea -Transaction-Isoling.

Tranzacția este o operațiune constând din una sau mai multe interogări de bază de date. Esența tranzacțiilor - pentru a asigura executarea corectă a tuturor solicitărilor într-o singură tranzacție, precum și a mecanismului de izolare a tranzacției unul de celălalt pentru a rezolva problema accesului comun a datelor.

Orice tranzacție este fie îndeplinită complet sau nu este efectuată deloc.

Există două concepte fundamentale în modelul tranzacțional: comiterea și răsturnarea. Comiterea înseamnă stabilirea tuturor modificărilor tranzacției. Rollback înseamnă anularea (lansarea) schimbărilor care au avut loc în tranzacție.

Când începe tranzacția, toate modificările ulterioare sunt salvate într-o depozitare temporară. În cazul comiterii, toate modificările făcute în cadrul unei singure tranzacții vor rămâne în baza de date fizică. Dacă se execută răsturnarea, toate modificările făcute în cadrul acestei tranzacții se vor întoarce înapoi.

În tranzacțiile MySQL sunt acceptate numai de tabelele INNODB. Myisam Tabelele de tranzacții nu acceptă. InnoDB este autocommistul implicit, ceea ce înseamnă că, în mod implicit, fiecare solicitare este echivalentă cu o singură tranzacție.

Tranzacția începe cu o interogare specială "Tranzacție de pornire" sau "începe". Pentru a finaliza tranzacția, trebuie să remediați modificările (interogarea comitetă), fie să vă întoarceți la acestea (interogarea Rolback).

Exemplu cu comiterea:

Începeți tranzacția; (De asemenea, puteți scrie Începe.;) ... Unele acțiuni cu baza de date (Inserare, Actualizare, Ștergere ...) să se angajeze; // fixarea acțiunilor, înregistrați-le în baza de date fizică

Exemplu cu ROLLBACK:

Setați autocommit \u003d 0; // dezactivați autocommistul Începeți tranzacția; ... o acțiune cu baza de date (inserați, actualizați, ștergeți ...) rolback; // retrage o serie de acțiuni, nu scrieți la baza de date fizică

În MySQL, nu există niciun mecanism de tranzacții imbricate. O conexiune BD este o tranzacție. O nouă tranzacție într-o singură conexiune poate începe numai după terminarea celei anterioare.

Pentru unii operatori, nu este posibil să se rostogolească cu ajutorul relansiei. Acestea sunt declarații de definiție a datelor (limbajul definiției datelor - DDL). Aceasta include crearea, modificarea, scăderea, trunchia, comentați, redenumiți cererile.

Următorii operatori completează implicit tranzacția (ca și cum ar fi fost emisă înainte de execuție):

Alter Tabel
Drop baza de date.
Încărcați datele principale.
Setați autocommit \u003d 1
Începe.
Indexul de picătură
Blocați tabelele
Începeți tranzacția.
Creați indexul
Drop tabelul
Redenumiți tabelul
Trunchiatul tabelului

Rețineți că, în cazul erorilor SQL, tranzacția în sine nu se rotește înapoi. De obicei, erorile sunt procesate de ambalaje SQL în aplicația în sine, cum ar fi PHP DOP, de exemplu. Dacă doriți să reluați modificările în cazul unei erori direct în MySQL, puteți crea o procedură specială și puteți efectua reluarea în el în manipulator:

Creare procedură prc_test () începe să declare ieșire manipulator pentru sqlexception începe reluarea; // aici reluați tranzacția în cazul unei erori finale; Începeți tranzacția; Introduceți în valorile TMP_TABLE ("NULL"); Să se angajeze; Sfârșit; Sunați la prc_test ();

Dar această metodă este mai probabil să se familiarizeze, nu un ghid pentru acțiune. De ce? Chiar nu recomand acest lucru pentru a face acest lucru, deoarece în cea mai mare parte erorile de baze de date sunt procesate utilizând pachete SQL pe partea de aplicare, cum ar fi PHP PDO, de exemplu, pentru a controla complet tranzacțiile de acolo.

Luați în considerare un exemplu practic: există 2 tabele, utilizatori - utilizatori și informații despre utilizator - user_info. Imaginați-vă că avem nevoie fie să executăm 3 cereri de bază de date, fie să le îndeplinesc deloc, deoarece altfel va conduce la eșecurile aplicației.

Începeți tranzacția; Introduceți în valorile utilizatorului (ID, NIK) (1, "Nikola"); Introduceți în utilizator_info (ID, ID_USER, item_name, item_value) Valori (1, 1, "nume", "nikolay"); Introduceți în valori utilizator_info (ID, ID_USER, item_name, itemvalue) (2, 1, "vârstă", "24"); Să se angajeze;

În general, cred că principiul funcționării tranzacției este de înțeles. Dar totul nu este atât de simplu. Există probleme de tranzacții paralele. Luați în considerare un exemplu. Imaginați-vă că, în timpul executării acestei tranzacții, un alt utilizator a creat oa doua tranzacție paralelă și a făcut o selecție * de la solicitarea de utilizator după prima cerere "Introduceți în valorile utilizatorului (ID, NIK) (1," Nikola "a fost efectuată în tranzacția noastră" Ce va vedea utilizatorul celei de-a doua tranzacții? Va putea vedea înregistrarea introdusă chiar și atunci când rezultatele primei tranzacții nu au fost încă fixate (nu sa întâmplat niciodată)? Sau poate vedea modificările numai după ce rezultatele primei tranzacții vor fi fixate? Se pare că există ambele opțiuni. Totul depinde de nivelul de izolare al tranzacției.

Tranzacțiile au 4 nivele de izolație:

0 - Citirea datelor neconfirmate (citirea murdară) (Citiți nerecunoscuți, citiți murdar) - cel mai scăzut nivel de izolație. La acest nivel, este posibil să se citească modificări inadecvate în tranzacțiile paralele. În acest caz, al doilea utilizator va vedea intrarea introdusă de la prima tranzacție nefixată. Nu există nicio garanție că tranzacția nefixată va fi respinsă în orice moment, astfel încât o lectură este o sursă potențială de erori.
1 - citirea datelor confirmate (Citiți angajați) - Este posibil să citiți datele numai ale tranzacțiilor fixe. Dar la acest nivel există două probleme. În acest mod, liniile care participă la selecția din cadrul tranzacției, nu sunt blocate pentru alte tranzacții paralele, problema nr. 1 curge din aceasta: "citirea non-repetată" (citirea nepetabilă) este o situație în care mai multe Probele apar în cadrul tranzacției (selectați) Conform acelorași criterii, se efectuează o tranzacție paralelă între aceste eșantioane, ceea ce schimbă datele implicate în aceste eșantioane. Deoarece tranzacția paralelă a modificat datele, rezultatul pentru următorul eșantion pe aceleași criterii în prima tranzacție va fi diferit. Problema numărul 2 - "Citirea Phantom" - acest caz este considerat mai jos.
2 - Citirea repetată (Citire repetabilă, instantaneu) - La acest nivel de izolație, este posibilă citirea datelor numai a tranzacțiilor fixe. De asemenea, la acest nivel, nu există nicio problemă a "citirii ne-refuzate", adică liniile care participă la eșantion în cadrul tranzacției sunt blocate și nu pot fi modificate de alte tranzacții paralele. Dar mesele nu sunt blocate în întregime. Din acest motiv, problema "citirii fantomei" rămâne. "Citirea Phantom" este atunci când, în timpul executării unei tranzacții, rezultatul acelorași eșantioane se poate schimba datorită faptului că întreaga masă este blocată, dar numai acele linii care participă la eșantion. Aceasta înseamnă că tranzacțiile paralele pot introduce șiruri de caractere în tabelul în care se efectuează eșantionul, astfel încât cele două selectări * din cererile de tabel pot da un rezultat diferit la momente diferite la introducerea tranzacțiilor paralele de date.
3 - Serializabil (Serializable) - tranzacții serializabile. Cel mai fiabil nivel de izolare a tranzacțiilor, dar în același timp cel mai lent. La acest nivel, nu există probleme de tranzacții paralele, dar pentru acest lucru va trebui să plătească viteza sistemului, iar viteza în majoritatea cazurilor este extrem de importantă.

SET TRANSACTION - Acest operator stabilește nivelul de izolare a următoarei tranzacții, la nivel global sau numai pentru sesiunea curentă.

Stabiliți nivelul de izolare a tranzacției
(Citește neangajată | Citiți comited | repetabilă citire | serializable)

De asemenea, puteți seta nivelul inițial de izolare globală pentru serverul MySQLD, rulându-l cu opțiunea -Transaction-Isoling.