Bevezetés a MySQL tranzakcióba. Ahogy kiderült, mindenki tudja, de nem mindenki megérti

A tranzakció olyan mechanizmus, amely lehetővé teszi az adatbázis több változását egyetlen műveletként. Vagy minden változtatásra kerül sor, vagy mindannyian elutasítják őket. Sem más munkamenet sem férhet hozzá az asztalhoz, miközben nyílt tranzakció van, amelyen belül bármilyen változás kerül végrehajtásra ebben a táblázatban. Ha az adatválasztás azonnali módosítása után próbálkozik, az összes módosítás elérhető lesz.

Egy ilyen tranzakciós támogatási adatbázis-mechanizmus, mint például az InnoDB vagy a BDB elindítja a tranzakciós tranzakciót. A tranzakció befejeződik a módosítások megerősítése vagy törlése során. A tranzakciót két paranccsal töltheti ki. A Commit parancs megmenti az adatbázis összes módosítását. A Rollback parancs törli az összes módosítást.

A példában egy tranzakciós támogatással rendelkező táblázatot hoz létre, az adatok beillesztésre kerülnek, majd egy tranzakciót indítanak el, amelyen belül az adatok törlődnek, és a tranzakciós tekercs vissza (törlés törlése) történik:

Táblázat létrehozása Sample_innodb (ID INT (11) NULL AUTO_INCREMENT, NAME VARCHAR (150) Alapértelmezett , elsődleges kulcs (ID)) Engine \u003d InnoDB Default Charset \u003d UTF8; Helyezzen be minta_innodb értékeket (1, "Alexander"), (2, "Dmitry"); Indítsa el a tranzakciót; Törölje a SAMPLE_INNODB-t, ahol ID \u003d 1; Törölje a SAMPE_INNODB-t, ahol ID \u003d 2; visszahúzás;

Mivel a tranzakciós visszaesés történt, a táblázat adatai nem törölték.

És ha a visszacsapás helyett egy elkötelezettséget írtunk, akkor mindkét vonal törölné.

A tranzakciók akkor szükségesek, ha szükséges, hogy több kérést pontosan alkalmazzák és végrehajtják "egyidejűleg", vagy egyikük sem teljesült, ha valami rosszul megy.

Például a fizetési rendszert valamilyen webhelyen hozhatja. A vásárlás idején a megrendelést fizetettnek kell tekinteni, ugyanakkor meg kell írnia a pénzt a felhasználó mérlegéből. Ha valami baj van - a vásárolt termék nélkül és pénz nélkül, vagy pénz nélkül tárolható, vagy pénz nélkül. És a tranzakciók segítségével könnyen elkerülhetjük ezt.

Az adósságért néha interjúkat kell tartanom a "Senior | Junior] Fejlesztő Python / Django" fejlesztője, "Timlid". Nagy meglepetésemre azt találtam, hogy a 10 pályázó 9-ből 9-ből származik, amelynek összefoglalójában a "MySQL / InnoDB / tranzakciók / triggerek / tárolt proc stb." Szavak teljesen nem tudják megmondani a múltbeli tapasztalataikról. Nem a használati opció egyetlen leírása, sajnálatos módon soha nem kaptam meg.

Ezután felajánlottam, hogy megpróbálok megoldani a megoldást a következő helyzetben:

Tegyük fel, hogy online szolgáltatás vagyunk, ami viszont néhány külső fizetett API-t élvez (a szolgáltatás aktiválása, a fizetett tartalom, vagy hogy a lelke elégedett), azaz szolgáltatásunk pénzt fizet az API használatához. A rendszerünk felhasználója létrehoz egy kérést a szolgáltatás aktiválására, kitölti az összes mezőt és az "Aktiválási szolgáltatás" gomb utolsó oldalát. Vagyis a HTTP kérés elküldésének időpontjában bejegyzésünk van adatbázisunkban (a szolgáltatás aktiválásának kérése). Mi az algoritmusa? - kérdezem és folytatom:

Vegye ki a felhasználó egyenlegét az adatbázisból;
- ha az egyensúly elég, akkor húzza meg az API-t;
- Ha minden rendben van, akkor írjuk le a mérleg összegét a szolgáltatás, a frissítés, a patientimim, különben visszafordul;
- Válaszolunk a felhasználóra.

Minden úgy tűnt, hogy triviálisan, de amikor az első és legnyilvánvalóbb problémát 10 versenyképes kérés formájában hozom (hogy mindegyike az elején ugyanazt az egyensúlyt kapja, és elkezdi hívni az API-t), a megoldások elkezdenek kínálni a legkifinomultabbakat Az 5 választó elvégzésétől (érdemes vallomást érdemes, nem értettem semmit ebben a kiviteli alakban), az automatikus szekrénymérők, a külső készpénzek, az új táblázatok felhasználása az adatbázisban, csúszik és még mindig nem érti.

Mint tudod (tudták az összes jelöltet!), Innodb a MySQL-ben tranzakciós mechanizmust és a line-up zárat lehetőségét biztosítja. A legrövidebb zár alkalmazásához elegendő hozzáadni a frissítési kifejezést a kiválasztás végén, például:

Válassza a * menüpontot, ahol az ID \u003d 5 frissítésre

A tranzakció elkezdődik, és az adatbázisra vonatkozó összes többi munkamenet nem lesz képes hasonló kérelmet elvégezni a tranzakció befejezése előtt, egyszerűen várni fognak. Az ugyanazon a rekord olvasásához olyan állapotban lesz elérhető, amely a tranzakciószigetelés szintjétől függ.

Érdemes megjegyezni, hogy a frissítés használata jobb, ha az autokommit ki van kapcsolva, mert függetlenül attól, hogy mit zároltak az első frissítés után, a mozdony.

Úgy tűnik, hogy apróság, nyilvánvalónak tűnik, de 9-ből 10 ...

UPD.
A cikkben nem ismertetett "tranzakció" korábbi nevét a "MySQL tranzakciók váltotta fel, és válassza a Frissítés lehetőséget"

Zy.
A cikk nem jelenti azt, hogy az API-nak meg kell húznia a tranzakción belül, és mit kell tennie a kudarc és a kivételes helyzetek feldolgozásában.

A tranzakciós mechanizmust csak az InnoDB és a BDB támogatja. Ezért az összes olyan táblázatot, amellyel a tranzakciókon keresztül szeretne dolgozni, megfelelő típusra kell átalakítani. Tud .

Alapértelmezés szerint a MySQL autocommit módban működik. Ez azt jelenti, hogy az SQL operátor végrehajtásának eredményei, az adatok megváltoztatása azonnal mentésre kerül.
Az AutoCommit mód kikapcsolható, mint ez: Állítsa be az autocommit \u003d 0;

Ha az AutoCommit módból csak egy parancssori sorozatot szeretne váltani, akkor a kezdet vagy indítsa el a tranzakciós parancsot, hogy ezt tegye (a MySQL 4.0.11 verziójával kezdődően)

Példa egy SQL lekérdezésre, amely javítja a kiválasztott áruk számát a Vásárláskor a korrekciós táblázatban, és módosítja az áruk táblázat:
Indítsa el a tranzakciót; Helyezze be a korrekciókat az ID_Goods \u003d: id_goods, szám \u003d: szám, id_oders \u003d: ID_Odaters a Duplicate Key Update szám \u003d szám +: szám; Frissítse az Áruk SET REARCE \u003d RESARD +: szám, elérhető \u003d Elérhető -: Szám, ahol id \u003d: id_goods; ELKÖVETNI;

A tranzakciót a kötelező üzemeltető végzi. A módosítások mentésre kerülnek. Az egyik kérés egyikében hiba esetén a módosítások semmilyen táblázatban nem kerülnek mentésre.

Ha összetettebb változtatási mechanizmust kell létrehoznia, használja a savepoint és a Savepoint parancsokba

A következő piaci szereplők implicit módon teljesítik a tranzakciót (mint a végrehajtás előtti kötelezettségvállalás):

Alternatív asztal
Drop adatbázis.
Töltsön be mesteradatokat.
Állítsa be az autocommit \u003d 1
Kezdődik.
Cseppindex
Lock asztalok
Indítsa el a tranzakciót.
Index létrehozása
Csepp asztal
Táblázat átnevezése
Csonka asztal

A PHP PDO a tranzakciókkal való együttműködés eszközeit kínálja. Olvashatók róluk.

Párhuzamos tranzakciók és szigetelési szintek (közös hozzáférés)

Képzeld el, hogy az 1. tranzakciós tranzakció végrehajtása során egy másik felhasználó létrehozott egy második párhuzamos tranzakciót, és az első kérés után az első kérés (ID, NIK) értékek (1 -) az első kérés (ID, NIK) értékek (1 -) tranzakció nikola ').
Mit lát a második tranzakció felhasználója?
Látni fogja a beillesztett rekordot akkor is, ha az első tranzakció eredményei még nem voltak rögzítve (nem történt meg nem történt)? Vagy láthatja a változásokat csak az első tranzakció eredményei után?

Kiderül, hogy mindkét lehetőség van. Mindez a tranzakció elszigeteltségétől függ.

Van egy tranzakció 4 Szigetelési szint:

0 - A nem megerősített adatok (piszkos olvasás) olvasása (olvasható olvasás, piszkos olvasás) - a legalacsonyabb szigetelés szintje. Ezen a szinten lehetséges, hogy a párhuzamos tranzakciók nem megfelelő változásait olvashatjuk. Ebben az esetben a második felhasználó meg fogja látni a beillesztett bejegyzést az első kétértelmű tranzakcióból. Nincs garancia arra, hogy a feltüntetett tranzakciót bármikor elutasítják, így az ilyen olvasás potenciális hibák forrása.
1 - Olvassa el a megerősített adatokat (olvassa el az elkötelezett) - csak rögzített tranzakciókat olvashat. De ezen a szinten két probléma van. Ebben az üzemmódban a tranzakción belüli mintában részt vevő vonalak nem blokkolódnak más párhuzamos tranzakciókhoz, az 1. számú probléma ebből szól:
"Nem kívánatos olvasás" (Nem megismételhető READ) olyan helyzet, amikor néhány mintát (SELECT) belül történik a tranzakció, és egy párhuzamos tranzakció között végzik ezeket a mintákat, amely megváltoztatja az érintett adatok ezekben a mintákban. Mivel a párhuzamos tranzakció megváltoztatta az adatokat, a következő minta ugyanazon kritériumai az első tranzakcióban eltérőek lesznek.

2. probléma - "Phantom Reading" - Ezt az ügyet az alábbiakban tárgyaljuk.
2 - Ismétlődő olvasás (ismételhető olvasás, pillanatfelvétel) - ezen az elkülönítési szinten csak rögzített tranzakciókat is olvashat. Ezenkívül ezen a szinten nincs probléma a "nem elutasított olvasás", vagyis a tranzakción belüli mintában részt vevő vonalak blokkolva vannak, és nem módosítható más párhuzamos tranzakciók. De az asztalok nem teljesen blokkolódnak. Ennek köszönhetően a "fantom olvasás" problémája marad. A "Phantom Reading" az, amikor egy tranzakció végrehajtása során ugyanazok a minták eredménye megváltozhat, mivel az egész tábla blokkolva van, de csak azok a sorok, amelyek részt vesznek a mintában. Ez azt jelenti, hogy a párhuzamos tranzakciók szövegek, a táblázatban, ahol a minta végezzük, így a két select * from tábla kéréseket adhat eltérő eredményt különböző időpontokban behelyezésekor adatok párhuzamos tranzakciók.
3 - Serializálható (serializálható) - serializálható tranzakciók. A tranzakciószigetelés legmegbízhatóbb szintje, de ugyanakkor a leglassabban. Ezen a szinten egyáltalán nincsenek problémák a párhuzamos tranzakciókról, de ezért meg kell fizetnie a rendszer sebességét, és a legtöbb esetben a sebesség rendkívül fontos.

Alapértelmezés szerint a 2. szigetelési szint a MySQL-ben (ismételhető olvasás) telepítve van. És ahogy azt hiszem, a MySQL-fejlesztők nem hiába tették az alapértelmezettet pontosan ezt a szintet, mivel a legtöbb esetben a legsikeresebb. Az első alkalom, hogy úgy tűnik, hogy a 3 legjobb megoldás a legmegbízhatóbb, de a gyakorlatban nagy kényelmetlenséget tapasztalhat az alkalmazás nagyon lassú munkájának köszönhetően. Ne feledje, hogy sokan attól függ, hogy mennyire jó az adatbázisban lévő tranzakciók izolálásának szintje, és arról, hogy az alkalmazás megtervezése. Az illetékes programozással a tranzakció legalacsonyabb szintjét is használhatja - mindez az alkalmazás kialakításának szerkezetének és írástudásának jellemzőitől függ. De ez szükségtelen, hogy törekedjen a legalacsonyabb szintű szigetelés - nincs, csak ha használja nem a legbiztonságosabb mód, meg kell emlékezni azokról a problémákról, a párhuzamos műveletek, ebben az esetben Ön nem zavaros és tegyen meg mindent helyesen.

Állítsa be a tranzakciót - Ez az üzemeltető meghatározza a következő tranzakció elszigeteltségét, globálisan vagy csak az aktuális munkamenethez.

Állítsa be a tranzakció elkülönítési szintjét (olvassa el az elolvasott elolvasott elolvasás megismételhető olvasását Serializálható)

A meglévő vegyületek nem érintettek. Az operátor elvégzéséhez szükség van egy szuper kiváltságra. A munkamenet-kulcsszó alkalmazása az összes jövőbeni tranzakció alapértelmezett szintjét csak az aktuális munkamenetre állítja be.

A MySQLD-kiszolgáló kezdeti globális szigetelési szintjét is beállíthatja, ha a -tranaction-Isolation opciót futtathatja.

A tranzakció egy vagy több adatbázis-lekérdezésből áll. A tranzakciók lényege - annak biztosítása érdekében, hogy minden kérelem egyetlen tranzakción belüli, valamint a tranzakciós elkülönítési mechanizmus biztosítja egymást a közös adathozzáférés problémájának megoldásához.

Bármely tranzakció teljes egészében teljesül, vagy egyáltalán nem végzett.

A tranzakciós modell két alapvető koncepciója van: elkövetni és visszautasítani. Kötelezi el a tranzakció összes változásának rögzítését. A visszanyerés azt jelenti, hogy a tranzakcióban bekövetkezett változások törlése (visszavonása).

Amikor a tranzakció elkezdődik, az összes későbbi módosítás ideiglenes tárolásra kerül. Elkötelezettség esetén az egyetlen tranzakció keretében végrehajtott valamennyi változás a fizikai adatbázisban marad. Ha a visszavonás végrehajtja, az ezen tranzakción belüli összes változtatás visszafordul.

A MySQL tranzakciókat csak az InnoDB asztalok támogatják. A myisam tranzakciós táblák nem támogatják. Az InnoDB az alapértelmezett autokommit, ez azt jelenti, hogy alapértelmezés szerint minden kérelem egyenértékű egyetlen tranzakcióval.

A tranzakció különleges lekérdezéssel kezdődik, "Indítsa el a tranzakciót" vagy a "kezdő". A tranzakció befejezéséhez be kell javítania a változtatásokat (lekérdezés elkövetése), vagy vissza kell húznia őket (visszafordulási lekérdezés).

Példa az elkötelezettséggel:

Indítsa el a tranzakciót; (Továbbá írhatsz Kezdődik.;) ... néhány intézkedés az adatbázissal (betét, frissítés, törlés ...) eLKÖVETNI; // A cselekvések rögzítése, rögzítse őket fizikai adatbázisban

Példa visszahúzással:

Állítsa be az autocommit \u003d 0; // kapcsolja ki az autocommitt Indítsa el a tranzakciót; ... néhány művelet adatbázissal (betét, frissítés, törlés ...) visszahúzás; // visszavonja a műveletek sorozatát, ne írjon a fizikai adatbázisba

A MySQL-ben nincsenek beágyazott tranzakciók mechanizmusa. Az egyik BD kapcsolat egy tranzakció. Egy új tranzakció egyetlen kapcsolaton belül csak az előző befejeződött.

Egyes operátorok esetében nem lehet visszavezetni a visszavezetést. Ezek az adatmeghatározás nyilatkozatok (adatmeghatározás nyelv - DDL). Ez magában foglalja a hozzászólást, az átalakításokat, a csonkítást, a megjegyzéseket, átnevezheti a kéréseket.

A következő piaci szereplők implicit módon teljesítik a tranzakciót (mint a végrehajtás előtti kötelezettségvállalás):

Alternatív asztal
Drop adatbázis.
Töltsön be mesteradatokat.
Állítsa be az autocommit \u003d 1
Kezdődik.
Cseppindex
Lock asztalok
Indítsa el a tranzakciót.
Index létrehozása
Csepp asztal
Táblázat átnevezése
Csonka asztal

Kérjük, vegye figyelembe, hogy SQL hibák esetén maga a tranzakció nem gördül vissza. Általában a hibákat az SQL csomagolók feldolgozzák az alkalmazásban, például PHP PDO-t. Ha a MySQL-ben közvetlenül a MySQL-ben bekövetkezett hibákat szeretnénk visszafordítani, létrehozhat egy speciális eljárást, és végezhet benne a kezelőben:

Eljárás létrehozása PRC_TEST () Kezdje el a SQLEXCEPLE KIVÁLASZTÁSÁNAK KIVÁLASZTÁSÁT; // a végső hiba esetén visszafordul a tranzakciót; Indítsa el a tranzakciót; Helyezzen be tmp_table értékekre ("null"); ELKÖVETNI; Vége; Hívja a prc_test ();

De ez a módszer nagyobb valószínűséggel ismeri meg, nem útmutató a cselekvéshez. Miért? Tényleg nem javaslom ezt, hogy ezt tegye, mivel többnyire adatbázis-hibákat dolgoznak fel az alkalmazási oldalon SQL csomagolóanyagokkal, például PHP PDO-t, például a tranzakciókat.

Fontolja meg a gyakorlati példát: 2 asztal, felhasználó - felhasználók és felhasználói információk - User_info. Képzeld el, hogy 3 adatbázis-kérelmet kell végrehajtanunk, vagy egyáltalán nem teljesítik őket, mivel egyébként az alkalmazás hibáihoz vezetnek.

Indítsa el a tranzakciót; Helyezze be a felhasználót (ID, NIK) értékeket (1, "Nikola"); Helyezze be a user_info (ID, ID_USER, ITEM_NAME, ITEM_VALUE) értékeket (1, 1, "Név", "Nikolay"); Helyezze be a user_info (ID, ID_USER, ITEM_NAME, ITEM_VALUE) értékeket (2, 1, "életkor", "24"); ELKÖVETNI;

Általánosságban elmondható, hogy a tranzakció működésének elvét érthető. De minden nem olyan egyszerű. A párhuzamos tranzakciók problémái vannak. Fontolja meg a példát. Képzeld el, hogy a tranzakció végrehajtása során egy másik felhasználó létrehozott egy második párhuzamos tranzakciót, és az első kérés után az első kérés (ID, NIK) értékek (1, "NIKOLA" értéket végeztünk " Mit lát a második tranzakció felhasználója? Látni fogja a beillesztett rekordot akkor is, ha az első tranzakció eredményei még nem voltak rögzítve (nem történt meg nem történt)? Vagy láthatja a változásokat csak az első tranzakció eredményei után? Kiderül, hogy mindkét lehetőség van. Mindez a tranzakció elszigeteltségétől függ.

A tranzakciók 4 szintű szigeteléssel rendelkeznek:

0 - Nem megerősített adatok olvasása (piszkos olvasás) (Olvassa el a nem parancsot, piszkos olvasást) - a legalacsonyabb szintű szigetelés. Ezen a szinten lehetséges, hogy a párhuzamos tranzakciók nem megfelelő változásait olvashatjuk. Ebben az esetben a második felhasználó meg fogja látni a beillesztett bejegyzést az első kétértelmű tranzakcióból. Nincs garancia arra, hogy a feltüntetett tranzakciót bármikor elutasítják, így ez az olvasás potenciális hibák forrása.
1 - A megerősített adatok olvasása (Olvassa el az elkötelezett) - csak rögzített tranzakciók adatait olvashatja. De ezen a szinten két probléma van. Ebben az üzemmódban a tranzakción belüli kiválasztásban részt vevő vonalak nem blokkolódnak más párhuzamos tranzakciókhoz, az 1. számú probléma kialszik ebből: "Nem ismétlődő olvasás" (nem ismételhető olvasás), amikor több A tranzakción belüli minták (válasszon) ugyanolyan kritériumok szerint kerülnek előfordulva, ez a minták között párhuzamos tranzakciót végeznek, amely megváltoztatja a mintákban szereplő adatokat. Mivel a párhuzamos tranzakció megváltoztatta az adatokat, a következő minta ugyanazon kritériumai az első tranzakcióban eltérőek lesznek. Probléma 2 - "Phantom Reading" - Ez az eset az alábbiakban tekintendő.
2 - ismételt olvasás (Ismétlődő olvasás, pillanatfelvétel) - Ezen a szigetelési szinten csak rögzített tranzakciók adatait is olvashatjuk. Ezenkívül ezen a szinten nincs probléma a "nem elutasított olvasás", vagyis a tranzakción belüli mintában részt vevő vonalak blokkolva vannak, és nem módosítható más párhuzamos tranzakciók. De az asztalok nem teljesen blokkolódnak. Ennek köszönhetően a "fantom olvasás" problémája marad. A "Phantom Reading" az, amikor egy tranzakció végrehajtása során ugyanazok a minták eredménye megváltozhat, mivel az egész tábla blokkolva van, de csak azok a sorok, amelyek részt vesznek a mintában. Ez azt jelenti, hogy a párhuzamos tranzakciók beilleszthetnek húrokat a táblázatban, amelyben a mintát elvégzik, így a két kiválasztási * a táblázatkérelmek közül választhat különböző eredményeket különböző időpontokban az adatok párhuzamos tranzakciók beillesztésekor.
3 - Serializálható (Serializálható) - Serializálható tranzakciók. A tranzakciószigetelés legmegbízhatóbb szintje, de ugyanakkor a leglassabban. Ezen a szinten egyáltalán nincsenek problémák a párhuzamos tranzakciókról, de ezért meg kell fizetnie a rendszer sebességét, és a legtöbb esetben a sebesség rendkívül fontos.

Alapértelmezés szerint a 2. szigetelési szint a MySQL-ben (ismételhető olvasás) telepítve van. És ahogy azt hiszem, a MySQL-fejlesztők nem hiába tették az alapértelmezettet pontosan ezt a szintet, mivel a legtöbb esetben a legsikeresebb. Az első alkalom, hogy úgy tűnik, hogy a 3 legjobb megoldás a legmegbízhatóbb, de a gyakorlatban nagy kényelmetlenséget tapasztalhat az alkalmazás nagyon lassú munkájának köszönhetően. Ne feledje, hogy sokan attól függ, hogy mennyire jó az adatbázisban lévő tranzakciók izolálásának szintje, és arról, hogy az alkalmazás megtervezése. Az illetékes programozással a tranzakció legalacsonyabb szintjét is használhatja - mindez az alkalmazás kialakításának szerkezetének és írástudásának jellemzőitől függ. De szükségtelen, hogy törekedjen a legalacsonyabb szigetelés szintjére - nem, csak ha nem használja a legbiztonságosabb módot, akkor emlékeznie kell a párhuzamos tranzakciók problémáiról, ebben az esetben nem zavarja és mindent helyesen csinál.

Állítsa be a tranzakciót - Ez az üzemeltető meghatározza a következő tranzakció elszigeteltségét, globálisan vagy csak az aktuális munkamenethez.

Állítsa be a tranzakció elkülönítési szintjét
(Olvassa el a nem parancsot Olvasás Elkötelezett | Ismétlődő Olvasható |

A MySQLD-kiszolgáló kezdeti globális szigetelési szintjét is beállíthatja, ha a -tranaction-Isolation opciót futtathatja.