Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely
ABR 2( Adatbázisrendszerek 2) 7. Előadás: Tranzakciók és zárolások a MySQL-ben
1
START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET autocommit = {0 | 1} A START TRANSACTION vagy BEGIN direktívával kezdődik egy új tranzakció. COMMIT elköveti (befejezi) a kurrens tranzakciót, az általa végzett változtatások véglegesek lesznek. A ROLLBACK visszaforgatja az aktuális tranzakciót, érvénytelenítve azon változásokat, melyek általa keletkeztek volna. A SET autocommit utasítás átállítja az autocommit alapértelmezését a kurrens session-ra. Az opcionális WORK kulcsszót a COMMIT és ROLLBACK miatt használjuk, amint a CHAIN és RELEASE záradékokat is. CHAIN és RELEASE használható mint pótlólagos ellenőrzése a tranzakció végrehajtásánk. A completion_type rendszerváltozó értéke meghatározza az alapértelmezett kitöltési viselkedést. 2
Egy tárolt program esetén (tárolt eljárások és függvények, triggerek és események), az elemző úgy tárgyalja BEGIN[WORK]-ot, mint egy BEGIN … END blokkot. Kezdjünk el egy tranzakciót egy ilyen környezetben a START TRANSACTION direktívával inkább. Az AND CHAIN záradék egy új tranzakció kezdetét okozza, amint a kurrens befejeződik és az új tranzakciónak ugyanaz az elkülönítési szintje, mint a kurrensé. A RELEASE záradék azt eredményezi, hogy a szerver lekapcsolódjon a kurrens kliens session-ról, miután befejezi a kurrens tranzakciót. Belevéve a NO kulcsszót, az felülírja a CHAIN vagy RELEASE kitöltést, ami hasznos lehet, ha a completion_type rendszerváltozó a láncolásra vagy a kitöltés kikapcsolásra van állítva alapértelmezetten. Alapérelmezésben, a MSQLben az autocommit mód bekapcsolása van érvényben. Ez azt jelenti, hogy mihelyt egy olyan utasítást hajtunk végre, amelyik felülír (módosít) egy táblát, a MySQL tárolja a módosítást a lemezen, hogy állandóvá tegye. Hogy kikapcsoljuk az autocommit módot, használjuk a következő utasítást: SET autocommit=0; 3
Miután kikapcsoltuk az autocommit módot úgy, hogy az autocommit változót zéróra állítottuk, a tranzakció-biztonságos táblákra (mint az InnoDB vagy a NDBCLUSTER) nem egyből érvényes. El kell végezni a COMMIT utasítást, hogy a változás a lemezen is tárlódjon vagy ROLLBACK utasítást kell kiadni hogy a változásokat figyelmen kívül hagyjuk. Hogy kikapcsoljuk az autocommit módot egy egyszeri utasítás sorra, használjuk a START TRANSACTION záradékot: START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; A START TRANSACTION használatával, autocommit megmarad kikapcsolt állapotban, amíg befejezzük a trazakciót COMMIT vagy ROLLBACK-el. Utána az autocommit mód visszavált az előző állapotba. BEGIN és BEGIN WORK-ot úgy használjuk, mint a START TRANSACTION álnevét, hogy trazakciót indítsunk. START TRANSACTION egy standard SQL szintaxis és az ad-hoc tranzakciók kezdéséhez ajánlják. 4
Több API, amit MySQL kliens alkalmazás írásához használunk (mint a JDBC – java database connectivity) saját eljárást biztosít a tranzakció kezdéshez, amelyiket sokszor a START TRANSACTION utasítás helyett használjuk a kliens oldalról. Kezdhetünk egy tranzakciót emígy is:
START TRANSACTION WITH CONSISTENT SNAPSHOT; A WITH CONSISTENT SNAPSHOT feltétel elindít egy konzisztens olvasást azon tároló motroknál, amelyek képesek erre. Ez csak az InnoDB motorra érvényes. A hatása ugyanaz mintha kiadunk egy START TRANSACTION utasítást amit követ egy SELECT egy InnoDB táblából. A WITH CONSISTENT SNAPSHOT záradék nem változtatja meg a kurrens tranzakció elkülönítési szintjét, ezért csak akkor biztosít egy konzisztens pillanatnyi állapotot ha az elkülönítési szint olyan, amelyik megengedi a konzisztens olvasást (REPEATABLE READ vagy SERIALIZABLE). 5
Egy tranzakció kezdete minden folyamatban lévő tranzakciót finalizál. Egy tranzakció kezdése tábla zárolás feloldását jelenti, amit a LOCK TABLES által kaptunk, mintha egy UNLOCK TABLES utasítást hajtottunk volna végre. Tranzakció kezdése nem szabadít fel egy globális olvasási zárolást, amelyet FLUSH TABLES WITH READ LOCK által kaptunk. Jobb eredmény elérése céljából a tranzakciókat csak egy tranzakcióbiztos tároló motorral érdemes elvégezni. Másképp a következő problémák jelentkezhetnek: •Ha a használt táblák több tranzakció-biztos tároló motor elemei (mint InnoDB vagy Falcon), és a tranzakció elkülönítési szint nem SERIALIZABLE, lehetséges, hogy amikor egyik tranzakció befejeződik, egy másik folyamatban levő tranzakció, amelyik ugyanazokat a táblákat használja csak egyes változtatásokat fog látni az előző tranzakcióból. Ez azt jelenti, hogy a kevert motorok használata nem garantálja az atomosságot, így nem következetes adatok keletkezhetek. 6
Problémák a tranzakcióbiztos motrok használatánál •Ha olyan táblákat használunk, amelyek nem tranzakció-biztosak egy tranzakcióban, a változtatások ezekben a táblákban egyszer tárolódnak, tekintet nélkül az autocommit mód beállítására. •Ha egy ROLLBACK záradékot használunk, miután módosítottunk egy nem tranzakcionális táblát egy tranzakción belül, egy ER_WARNING_NOT_COMPLETE_ROLLBACK figyelmeztetést kapunk. A tranzakció-biztos táblákon elvégzett módosítások visszaforgatásra kerülnek, de semmilyen változtatás nem történik a nem tranzakció-biztos táblákban. •Minden tranzakciót egy bináris naplóban (logban) tartjuk egy tartományban a COMMIT–ig. Azon tranzaciók amelyek visszaforgatásra kerülnek nincsenek bejegyezve. (Kivétel: Nem tranzakcióbiztos táblákon végzett módosításokat nem lehet visszaforgatni. Ha egy tranzakció, amely visszaforgatásra kerül tartalmaz módosításokat nem tranzakcióbiztos táblákra, az egész tranzakció ROLLBACK záradékkal kerül bejegyzésre, hogy biztosítva legyen az, hogy a nem tranzakcióbiztos táblák módosításáról másodpéldány készül.) 7
Utasítások, melyeket nem lehet visszaforgatni Egyes záradékokat nem lehet visszaforgatni. Általában, ezek adatdefiniáló utasítások, mint az adatbázisok készítése és eldobása, azok, amelyek elkészítenek, eldobnak vagy módosítanak táblákat vagy tárolt eljárásokat.
Megtervezhetjük úgy a trankciónkat, hogy ne tartalmazzon ilyen utasításokat. Ha használunk a tranzakció elején olyan utasítást, amelyiket nem lehet visszaforgatni és azután egy másik utasítás nem végződik el, a tranzakció teljes hatását nem lehet visszaforgatni ez esetben, használva a ROLLBACK záradékot. 8
Utasítások, amelyek implicit végrehajtást vonnak maguk után 1.Adatdefiníciós utasítások, melyek adatbázis objektumokat definiálnak vagy módosítanak. ALTER DATABASE, UPGADE DATA DIRECTORY NAME, ALTER PROCEDURE, ALTER TABLE stb… 2.Utasítások, melyek implicit használak vagy módosítanak táblákat a mysql adatbázisban. CREATE USER, DROP USER, RENAME USER, GRANT 3.Tranzakció-kontroll és zárolási utasítások. BEGIN, LOCK TABLES, SET autocommit = 1 (ha az éték nem 1 már), START TRANSACTION, UNLOCK TABLES 4.Adatletöltési záradék LOAD DATA INFILE 5.Adminisztratív utasítások. 9 CAHCE INDEX, ANALYZE TABLE, CHECK TABLE …
SAVEPOINT és ROLLBACK TO SAVEPOINT SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier •Az InnoDB támogatja a következő SQL utasításokat: SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT és az opcionális WORK kulcsszót a ROLLBACK-hez. •A SAVEPOINT utasítás beállít egy megnevezett tranzakciós mentési pontot, amelyi neve identifier. Ha a kurrens tranzakciónak van egy mentési pontja ugyanazzal a névvel, a régi mentési cím letörlődik és egy új állítódik be.
10
•A ROLLBACK TO SAVEPOINT utasítás visszaforgatta a tranzakciót a nevezett mentési ponthoz a tranzakció befejezése előtt. Azon módosítások, amelyeket a kurrens tranzakció végez a mentési pont után visszaforgatódnak, de az InnoDB nem szabadítja fel a sor zárolásokat, amelyek tárolva voltak a memóriában a mentési pont után. (Egy új sorbeszúráshoz, a zárolási információt a tranzakció ID-je hordozza a sorban; a zárolás nincs külön tárolva a memóriában. Ebben az esetben a sor zárolásának feloldása a visszaállítás során történik.) Azon mentési pontok, amelyek egy későbbi időpontban vannak, mint a megnevezett mentési pont, törlésre kerülnek. •A RELEASE SAVEPOINT utasítás eltávolítja a megnevezett mentési pontot a mentési pontok halmazából a kurrens tranzakcióból. Sem érvényesítés, sem visszaforgatás nem történik. Ez egy olyan hiba, mintha a mentési pont nem is létezne. •Minden mentési pont a kurrens tranzakcióból törlésre kerül, ha végrehajtjuk a COMMIT, vagy a ROLLBACK utasítást, amelyek nem neveznek meg mentési pontot. 11
LOCK TABLES és UNLOCK TABLES LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE UNLOCK TABLES A MySQL megengedi a kliens session-nak hogy explicit módon zároljon táblákat azért, hogy együttműködhessen más sessionokkal a táblák elérésére vagy hogy megelőzze azt, hogy más session módosítsa a táblákat azon időszak alatt, amikor a session kizárólagos elérhetőséget kíván. Egy session kéreti vagy felszabadíthatja a zárolást csak ő egymaga. Egy session nem kérhet zárolást egy másik sessionra vagy szabadíthatja fel egy másik session zárolását. 12
Zárolás használható, hogy tranzakciókat versenyeztessen vagy hogy nagyobb gyorsaságra tegyen szert a sorok módosításában. •LOCK TABLES explicit megszerez tábla zárolásokat a kurrens kliens sessionra. Tábla zárolásokat kérhetünk az alaptáblákra vagy nézetekre. Szükséges a LOCK TABLES privilégium és a SELECT privilégium, hogy minden objektumot zárolhassunk. •A nézet zárolásához a LOCK TABLES hozzáad minden alaptáblát, amelyeket a nézet használ a táblahalmazhoz amelyeket zárolni kell, majd automatikusan zárolja őket. Ha egy táblát explicit a LOCK TABLES utasítással zárolunk, minden tábla, melyet a triggerek használnak implicit záróvá lesz. •UNLOCK TABLES explicit felszabadít minden táblazárolást a kurrens sessionban. •Egy másik használata az UNOLOCK TABLESnek az, hogy felszabadítsa a globális olvasási zárolást, amelyet a FLUSH TABLES WITH READ LOCK utasítás okozott, amelyik megengedi hogy zároljunk minden táblát minden adatbázisban. 13
Zárolás-megszerzési szabályok Hogy zárolást szerezünk a folyamatban levő sessionban használjuk a LOCK TABLES utasítást. A kövekező zárolási típusok használhatók: •READ [LOCAL] lock: A session amelyik tartja a zárolást olvashatja a táblát (de nem írhatja). Többszörös session olvasásra zárolhatja ugyanazt a táblát ugyanabban az időben. Más sessions olvashatja a táblát anélkül, hogy explicit READ lock-ot kérne. A LOCAL módosító megengedi a konfliktusmentes INSERT utasításoknak (konkurens beszúrások) más sessions részéről való végrahajtását mikor a zárolás tartja. Habár a READ LOCAL nem használható, ha az adatbázist a szerveren kívüli folyamatok által akarjuk irányítani miközben birtokoljuk a zárolást. Az InnoDB tábláknál a READ LOCAL ugyanazt jelenti, mint a READ. •[LOW_PRIORITY] WRITE lock: A session, amelyik birtokolja a zárolást olvashatja és írhatja a táblát. Csak a session, melyik birtokolja a zárolást tudja elérni a táblát. Semmilyen más session nem érheti el, míg a zárolást nincs feloldva. Egy zárolási kérés egy táblára egy másik session által blokálva van, míg a WRITE zárolást birtokolaja. A LOW_PRIORITY módosító befolyásolja a zárolási ütemezést ha a WRITE zárolási kérésnek várnia kell. 14
•Ha a LOCK TABLES utasításnak várnia kell amíg egy másik session birtokolja a zárolást bármely táblán, leblokálja, míg az összes zárolást meg lehet szerezni. •Egy session amelyik zárolást igényel minden zárolást meg kell szerezzen amire szüksége van egy egyetlen LOCK TABLES utasításban. Amíg a zárolások birtokolva vannak, a session csak a zárolt táblákat használhatja. Például, a következő utasítás szekvenciában, egy hiba fog fellépni a t2 elérése miatt, mivel nem volt zárolva a LOCK TABLES utasításban: mysql> LOCK TABLES t1 READ; mysql> SELECT COUNT(*) FROM t1; EREDMÉNY PL. 3 mysql> SELECT COUNT(*) FROM t2; ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES 15
LOCK TABLES a következőképpen szerzi meg a zárolásokat: 1.Sorbaállítja a zárolásra kerülő táblákat egy belső sorrend szerint. A felhasználó szempontja szerint ez a sorrend nem érhető el. 2.Ha egy tábla olvasásra is és írásra is zárolás kérés érkezik, rakjad az írsra való zárolási kérelmet az olvasási zárolás elé. 3.Zárol egy táblát egyszerre, míg a session megkapja az összes zárolást. Ez az eljárásmód biztosítja, hogy a tábla zárolása beragadás-mentes. Vannak azonban olyan más dolgok, ami miatt figyelni kell erre az eljárásmódra: Ha egy LOW_PRIORITY WRITE zárolást használunk egy táblához azt jelenti, hogy kizárólag a MySQL várakozik erre az egyedi zárolásra, amíg nincs egy session sem, amelyik szeetne egy READ zárolást. Amikor a session megkapja a WRITE zárolást és várakozik hogy megkapja a zárolást a következő tálára a zárolási tábla listában, minden más session várakozik a WRITE zárolás befejezésére. Ha ez egy komoly problémává válik az alkalmazásban, arra kell gondolni, hogy átváltoztassuk egyes tábláinkat tranzakcióbiztos táblákká. 16
Zárolás felszabadítási szabályok Amikor a zárolt táblák, melyeket egy session használ feszabadításra kerülnek, egyszerre szabadulnak fel. Egy session felszabadíthatja explicit a zárolásait, vagy a zárolások implicit szabadulnak fel bizonyos meghatározott feltételek mellett. •Egy session felszabadíthatja az ő zárolásait explicit módon alkalmazva az UNLOCK TABLESt. •Ha egy session azt eredményezi, hogy egy LOCK TABLES utasítás megkapjon egy zárolást miközben ugyancsak birtokol egy zárolást, a létező zárolását implicit felszabadítja a rendszer, mielőtt az új zárolás jóváhagyódik. •Ha egy session elindít egy tranzakciót (például egy START TRANSACTION használatával), egy implicit UNLOCK TABLES kerül végrehajtásra, ami azt eredményezi, hogy a létező zárolások fel lesznek szabadítva. 17
•Ha egy kliens session kapcsolata befejeződik akár normálisan, akár abnormálisan, a szerver implicit feloldja az összes táblazárolást, melyeket a session birtokolt (tranzakcionális vagy nem). Ha a kliens újra kapcsolatot létesít, a zárolások nem hatásosak többé. Ezenkívül, ha a kliensnek egy aktív tranazkciója van, a szerver visszagörgeti a tranzakciót a szétkapcsolásig és ha egy újrakapcsolásra kerül sor, az új session aktiválja az autocommitot. Ebből az okból kifolyólag a kliensek szeretnék használni az auto-reconnect opciót. Ezzel az autoreconnect hatással, a kliens nincs értesítve az újrakapcsolás tényéről de bármely tábla zárolása vagy a kurrens tranzakció elveszik. Az autoreconnect kikapcsolt állapotában, ha egy kapcsolat elvész egy hibajelzés keletkezik az elkövetkező utasításvégzés kérésekor. A kliens észlelheti a hibát és megfelelő cselekedetet végezhet, hogy visszakapja a zárolást vagy újrafuttassa a tranzakciót. •Megjegyzés •Ha az ALTER TABLE utasítást használunk egy zárolt táblán, zárolás nélkülivé válhat. Például, hogyha megpróbálunk egy második ALTER TABLE utasítást, az eredmény lehet egy ilyen hiba: Table 'tbl_name' was not locked with LOCK TABLES. Hogy kezelhessük ezeket, zároljuk a táblákat mégegyszer a második 18 struktúramódosítás előtt.
Interakció a Table Locking és Tranzakciók között LOCK TABLES és UNLOCK TABLES a következő tranzakciók használatakor kerülnek kölcsönhatásba: •LOCK TABLES nem tranzakcióbiztos és implicit befejezésre késztet minden aktív tranzakciót, mielőtt zárolná a táblákat. •UNLOCK TABLES implicit befejezésre késztet bármilyen aktív tranzakciót, de csak ha LOCK TABLES volt használva hogy beszerezzék a táblák zárolását. Például, a következő utasítás sorozatban UNLOCK TABLES elengedi a globális olvasási zárolást, de nem véglegesíti a tranzakciót mivel egy tábla zárolás sincs folyamatban: FLUSH TABLES WITH READ LOCK; START TRANSACTION; SELECT ... ; UNLOCK TABLES; • Tranzakiót kezdve (például a START TRANSACTION használatával) implicit véglegesítést okoz minden kurrens tranzakcióra és felszabadítja a létező zárolásokat. 19
•Más utasítások, melyek implicit tranzakció véglegesítést okoznak nem szabadítják fel a létező zárolásokat. •A helyes használata a LOCK TABLES és az UNLOCK TABLES tranzakciós tábláknál, mint az InnoDB táblák az, hogy kezdjük a tranzakciót a SET autocommit = 0 (nem START TRANSACTION) és azt kövesse a LOCK TABLES, és ne használjuk az UNLOCK TABLESt amíg nem véglegesítjük a tranzakciót explicit módon. Például, ha szükségünk van egy írásra a t1 táblában és egy olvasásra a t2 táblában cselekedhetünk a következő képpen: SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES; Amikor meghívjuk a LOCK TABLESt, az InnoDB belsőleg a saját táblazárolásait valósítja meg. Az InnoDB felszabadítja a belső tábla zárolását a következő commit alkalmával, de hogy a MySQL felszabadítsa a táblazárolásait, meg kell hívnunk az UNLOCK TABLESt. 20
Lock Tables és Triggerek Ha egy táblát zárolunk expicit módon a LOCK TABLES utasítással, bármilyen tábla, melyet a triggerek használnak ugyancsak zároltak lesznek implicit módon: •A zárolások ugyanakkor vannak felvéve, mint azok, amelyek explicit módon vannak megszerezve, mint a LOCK TABLES utasításban. •A zárolás egy táblán, amit egy triggerben használunk függ attól, hogy a tábla csak olvasásra van-e használva. Ha igen, az olvasási zárolás elegendő. Másképp egy írási zárolást használunk. •Ha egy tábla explicit zárolást igényel az olvasásra a LOCK TABLES segítségével, de szükséges zárolni az írásra, mivel módosítható egy triggeren belül, egy írási zárolás lesz felvéve, mintsem egy olvasási zárolás. (Azaz, egy implicit írási zárolás szükséges a táblák megjelenése alkalmából egy triggeren belül, ez egy olvasási zárolási kérést jelent azon táblának, amelyiket konvertálni kell egy írási zárolás kéréshez.) 21
Feltételezzük, hogy két táblát szeretnénk zárolni, t1 és t2: LOCK TABLES t1 WRITE, t2 READ; • Ha t1 vagy t2 tartalmaz triggereket, táblákat melyeket triggereken belül használunk ugyancsak zárolva lesznek. Feltételezzük, hogy a t1 tartalmazza a következő triggert: CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW BEGIN UPDATE t4 SET count = count+1 WHERE id = NEW.id AND EXISTS (SELECT a FROM t3); INSERT INTO t2 VALUES(1, 2);END;
Az eredménye a LOCK TABLES utasításnak az, hogy a t1 és t2 zárolva lesznek mivel megjelennek az utasításban, t3 és t4 zárolva lesznek, mivel a triggeren belül használjuk: •t1 írásra van zárolva a WRITE zárolás kérés révén. •t2 írásra van zárolva, habár a kérés csak egy READ zárolás. Ez azért jelenik meg, mert t2 részt vesz a trigger megvalósításában, vagyis a READ kérés át van alakítva egy WRITE kéréssé. •t3 olvasásra van zárolva, mivel csak olvasásra van használva a triggeren belül. •t4 írásra van zárolva, mivel lehetséges módosításnak van kitéve a triggeren belül. 22
Ha nem-tranzakciós adatbázis motor tábláit használunk, használnunk kell a LOCK TABLESt ha biztosak akarunk lenni, hogy más session nem módosítja a táblákat a SELECT és az UPDATE között. Az itt bemutatott példa igényli a LOcK TABLESt hogy biztosan elvégzésre kerüljön: LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES;
•A LOCK TABLES nélkül lehetséges lenne, hogy egy másik session beszúrjon egy új sort a trans táblába a SELECT és az UPDATE utasítások között. •Használhatjuk a LOCK TABLESt több esetben mikor relatív frissítést használunk (UPDATE customer SET value=value+new_value) vagy a LAST_INSERT_ID() függvény esetében. 23
SET TRANSACTION szintaxisa SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } Ez az utasítás beállítja a tranzakció elkülönítési szintjét globális módon, a kurrens sessionra vagy a következő tranzakcióra: •A GLOBAL kulcsszó utasítás beállítja az alapértelmezett tranzakciós szintet globálisan minden következő sessionra. Létező sessionok nincsenek befolyásolva. •A SESSION kulcsszóval rendelkező utasítás beállítja az alapértelmezett tranzakciós szintet minden következő tranzakcióra, amelyik ezen a sessionon belül hajtódik végre. •A SESSION vagy GLOBAL kulcsszón kívül az utasítás beállítja az elkülönítési szintet az elkövetkező (nem elindított) tranzakciókra, melyek az aktuális sessionban lesznek elvégezve. 24
Egy változtatás a globális elkülönítési szintben SUPER privilégimot igényel. Bármely sessionnak joga van megváltoztatni az elkülönítési szintet (a tranzakció végrehajtása közben is), vagy az elkülönítési szintjét az elkövetkező tranzakcióinak. Az InnoDB támogatja az összes elkülönítési szintet, amelyet itt leírunk, amelyek különböző zárolási stratégiát használnak. Az alapértelmezett szint a REPEATABLE READ. •A következő lista bemutatja hogyan támogatja a MySQL a különböző tranzakciós szinteket: READ UNCOMMITTED •SELECT utasításokat nem zárolással hajtjuk végre, hanem egy lehetséges előző variánsa a sornak lesz használva. Eképpen, használva az elkülönítési szinteket, ilyen olvasások nem konzisztensek. Ezt úgy is hívják, hogy “mocskos olvasás” Másképp, ezen elkülönítési szint úgy működik, mint a READ COMMITTED. 25
•READ COMMITTED Egy Oracle-tipusú elkülönítési szint tekintetbe véve a konzisztens (nemzárolási) olvasást: Minden konzisztens olvasás, még ugyanazon tranzakción belül is beállítja és olvassa a saját friss pillanatképét. Az olvasási zárolásra (SELECT a FOR UPDATE el vagy LOCK IN SHARE MODE), InnoDB zárolás csak az indexeket veszi fel, kihagyja az üres részeket, mely előtte van és ez megengedi a szabad beszúrását egy új sornak a zárolt bejegyzés után. Az UPDATE és DELETE utasításoknál a zárolás függ attól, hogy az utasítás egy egyedi indexet használ-e egyedi keresési feltételek mellett (mint a WHERE id = 100), vagy egy tartomány–tipusú keresési feltételel (mint a WHERE id > 100). Egy egyedi index egy egyedi keresési feltétel mellett, az InnoDB zárolások csak az index rekordokat, nem az előtte levő üres részeket találja meg. A tartomány-tipusú keresések, InnoDB zárolással az index tartományt viszgálja végig, használva az üres zárolási vagy egy következő-kulcs (üres rész plus index-rekord) zárolási vagy blokkolt beszúrásokat más sessionok által az üres helyekről, amit befed a tartomány. Ez szükséges, mivel a “fantom sorokat” blokkolni kell, hogy a MySQL replikáció és visszanyerés működjön. 26
• REPEATABLE READ Ez az alapértelmezett elkülönítési szint az InnoDB-nek. A konzisztens olvasáshoz jelentős különbség van a READ COMMITTED elkülönítési szinttől: Minden konzisztens olvasás ugyanabban a tranzakcióban beolvassa a pillanatfelvételt, amelyet az első olvasás határozott meg. Ez a megállapodás azt jelenti, hogy ha használunk egy pár síma (nem zárolt) SELECT utasítást ugyanazon a tranzakción belül, ezen SELECT utasítások konzisztensek egymás irányában is. •Az olvasási zárolás (SELECT FOR-ral együtt, UPDATE vagy LOCK IN SHARE MODE), UPDATE és DELETE utasítások, a zárolás függ attól, hogy az utasítás egyedi indexet használ-e egy egyedi keresési feltétellel vagy egy tartománytipusú keresési feltételt. Az egyedi index egy egyedi keresési feltétellel az InnoDB zárolások csak az index rekordokat találja, s nem az előttük levő gap-eket. Más keresési feltételek mellett az InnoDB zárolások az index tartományt keresik, használva a gap zárolásokat vagy a következő-kulcs (gap plusz index-rekord) zárolásokat hogy blokkolja a beszúrást más sessionok által a gapbe, amelyeket a tartomány fed. 27
• SERIALIZABLE •Ez a szint olyan, mint a REPEATABLE READ , de InnoDB implicit módon konvertál minden síma SELECT utasítást a következőbe SELECT … LOCK IN SHARE MODE ha az autocommit ki van kapcsolva. Ha az autocommit be van kapcsolva, a SELECT a saját tranzakciója. (Ez azt jelenti hogy a síma SELECT kényszerítése arra, hogy blokkolja ha más tranzakció módosítota a kiválasztott sorokat, ki lehet kapcsolni az autocommitot.) •Megjegyzés A MySQL 5.1 ben, ha a READ COMMITTED elkülönítési szint létezik ezt használatos vagy az innodb_locks_unsafe_for_binlog rendszerváltozó engdélyezve van, nincs InnoDB gap zárolás, kivéve az idegen kulcs megszorítások ellenőrzésére és a többszörös-kulcs ellenőrzésre. Ugyanakkor a sorzárolás a nem összeillő sorokra felszabadítását eredményezik miután a MySQL kiértékeli a WHERE fetételt. •A MySQL 5.1, ha használjuk a READ COMMITTED vagy engedélyezzük az innodb_locks_unsafe_for_binlogt, kötelezően használni kell a soron-alapuló bináris belépést (logging). 28
Bibliográfia • http://dev.mysql.com/doc/refman/5.1/en/sql -syntax-transactions.html • http://dev.mysql.com/doc/refman/5.1/en/loc king-issues.html • http://emte.siculorum.ro/~illyeslaszlo/ABR/ Marosvasarhely-2010/
29
