af Anna Szalay 7 år siden
330
4. előadás gondolattérképe
A tranzakciók adatbázis-műveletek sorozatát jelentik, amelyek egy logikai egységet alkotnak. Ezeket a változtatásokat vagy teljes egészében érvényesítjük, vagy teljesen visszavonjuk.
af Anna Szalay 7 år siden
330
Mere som dette
A gondolattérkép elkészítéséhez a 4. előadás diáinak anyagát, és az órán készített jegyzeteimet használtam alapvető forrásként.
(Minden más forrásból származó tudást az adott helyen jelöltem meg)
Készítette:
Szalay Anna Dorina
Az ACID ("savasság") egy adatbázistól elvárt képességek halmazát határozza meg.
Az ACID mozaikszó az alábbi szavakból tevődik össze:
Atomicity, Consistency, Isolation, Durability
Tartósság vagy állandóság = Durability.
Egy véglegesített tranzakció által az adatbázison véghezvitt módosításoknak meg kell őrződniük az adatbázisban. Ezeknek a módosításoknak semmilyen hiba miatt nem szabad elveszniük.*
Tehát például: ha egy tranzakciót már jóváhagytak, akkor az "végleges", tehát egy áramszünet nem okozhatja, hogy elveszik a hatása.
* http://shrek.unideb.hu/~nogre/AdatB/Elmelet/13.pdf
Elkülönítés (isolation)
Egyidőben több tranzakció is végrehajtódhat.
Viszont egy tranzakciónak látszólag más tranzakcióktól elkülönítve kell végrehajtódnia. Ez azt jelenti, hogy a tranzakció végrehajtása nem állhat kölcsönhatásban semelyik másik konkurensen végrehajtott tranzakcióval sem.*
* http://shrek.unideb.hu/~nogre/AdatB/Elmelet/13.pdf
Konzisztenciamegőrzés (consistency preservation).
Egy tranzakció konzisztenciamegőrző, ha teljes végrehajtása az adatbázist konzisztens (érvényes) állapotból konzisztens állapotba viszi át.*
* http://shrek.unideb.hu/~nogre/AdatB/Elmelet/13.pdf
Az atomosság a tranzakció a feldolgozás atomi egysége. Az atomicitás pedig azt jelenti, hogy vagy teljes egészében végrehajtódik a tranzakció, vagy egyáltalán nem.*
* http://shrek.unideb.hu/~nogre/AdatB/Elmelet/13.pdf
Az ISO szabvány a következő lehetséges izolációs szinteket definiálja:
Ezek a szintek föntről lefelé egyre költségesebbek lesznek az adatbázisnak, így a tranzakciók végrehajtási sebessége lassul. Az izolációs szinteket a Connection objektumon a setTransactionIsolation() metódussal állíthatjuk be a tranzakció megkezdése előtt.
Például:
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);*
* https://barakonyi.wordpress.com/2010/03/17/java-tranzakcio-kezeles-es-tranzakcio-izolacios-szintek/
Annyival több a REPEATABLE_READ-től, hogy más tranzakció nem írhatja felül a mi tranzakciónk által olvasott értékeket, azaz addig várakoztatja azokat míg be nem fejeződik a tranzakciónk. Így nem fognak tranzakció közben fantom sorok keletkezni a táblában. Itt elég problémás lehet, ha több résztvevő folyamatosan olvas egy táblát, amíg az update-elő szál várakozik, mert a tábla lock-olva van és nem tud bele írni…*
* https://barakonyi.wordpress.com/2010/03/17/java-tranzakcio-kezeles-es-tranzakcio-izolacios-szintek/
Ez annyival jobb a READ_COMMITTED-nél, hogy már a non-repeatable read hibát is képes kiszűrni a tranzakcióból. Egyszerűbben: csak a recordok commit-olt értékeit használja és a recordok tranzakció közbeni törlése nem befolyásolja a select-eket. Ebben az esetben csak egy probléma marad, a phantom read.*
* https://barakonyi.wordpress.com/2010/03/17/java-tranzakcio-kezeles-es-tranzakcio-izolacios-szintek/
Olvasáskor mindig az adott rekord véglegesített értéket kapjuk. Ez az esetek 99%-ra használható, a tranzakcióink mindig csak olyan rekordokat olvasnak, amik komittálva vannak, azaz nincs nyitott tranzakció, ami dolgozna rajtuk. A baj ezzel az, hogy ha sokan írják és olvassák az adott rekordot vagy táblát akkor könnyen kialakulhat az a helyzet, hogy az olvasó tranzakciók arra várnak hogy az írás (pl egy nagy tábla update-je) befejeződjön.*
* https://barakonyi.wordpress.com/2010/03/17/java-tranzakcio-kezeles-es-tranzakcio-izolacios-szintek/
Olvasáskor mindig az aktuális (módosított) értéket kapjuk, még akkor is, ha az adott insert/update tranzakciót a kezdeményező nem commit-olta. A következő problémák léphetnek fel:
A kiolvasott rekord változhat más tranzakciók által a mi tranzakciónk ideje alatt. Tehát előfordulhat, hogy a tranzakciónk elején és végén az adott rekordban más értékek szerepelnek, holott mi nem is változtattuk, csak olvastuk…
A tábla egy adott sora törlődhet a mi tranzakciónk közben. Így amikor mi a tranzakciónk elején és végén futtatunk egy-egy select-et, akkor a második esetben hiányozhatnak sorok az eredményből.
Hasonló az előzőhöz, csak insert-tel. A táblába új sor (fantom sor) kerülhet egy másik tranzakció által miközben a mi tranzakciónk még fut. Így a tranzakciónk végén több sor lesz az adott táblábam, mint amivel számolhattunk az elején…
* https://barakonyi.wordpress.com/2010/03/17/java-tranzakcio-kezeles-es-tranzakcio-izolacios-szintek/
A példa feladatok ezen séma alapján készültek.
(A sémát én készítettem, nem másoltam külső forrásból)
A tranzakció olyan DML utasítások sorozata, amely az adatmódosításaink egy logikai egysége. Mindezeket a változtatásokat egységként vagy érvényesítjük a végén, vagy mindenestől visszavonjuk. A tranzakciókkal logikai csoportokba rendezzük a módosításainkat. A fejlesztő felelőssége, hogy pontosan a megfelelő egységekbe (tranzakciókba) csoportosítsa a DML műveleteket.
A tranzakciót alkotó adatbázis-műveletek vagy egy alkalmazói programba vannak beágyazva, vagy adhatók interaktívan meg egy magas szintű lekérdező nyelv (például SQL) segítségével. A tranzakció határait megadhatjuk az explicit begin transaction és end transaction utasításokkal egy alkalmazói programban, ebben az esetben a két utasítás között elhelyezkedő összes adatbázis-hozzáférési művelet egy tranzakciót alkot. Egy alkalmazói program egynél több tranzakciót is tartalmazhat, ha több tranzakció-elhatároló utasítás szerepel benne. Ha a tranzakciót alkotó adatbázis-műveletek nem módosítják az adatbázist, csak lekérdezik azt, akkor a tranzakciót read-only tranzakciónak nevezzük.*1
A vezérlő utasításokhoz szokás sorolni a művelet végrehajtást szabályozó, úgynevezett tranzakció kezelő utasításokat is. Az SQL szabványban két, a tranzakció végét jelző utasítást szokás definiálni. Az egyik utasítás a tranzakció sikeres befejezését jelenti, míg a másik a korábban végrehajtott tevékenységek visszagörgetését, azaz megsemmisítését írja elő. Ezek a COMMIT és a ROLLBACK.*2
*1 http://shrek.unideb.hu/~nogre/AdatB/Elmelet/13.pdf
*2 http://www.kobakbt.hu/jegyzet/AdatbazisElmelet/ora5.html
Az adatbáziskezelő működési módja ez, amely biztosítja, hogy aki olvassa az adatokat, ne láthassa mások befejezetlen tranzakcióit.
Különbözőképpen implementálható, de mindenképpen rendkívül nehezen megvalósítható.
Például az Oracle adatbázisok esetén:
Például az Oracle a tranzakciókezelést és az olvasási konzisztenciát zárakkal és úgynevezett UNDO adatok vezetésével valósítja meg.
Egy igen bonyolult algoritmus, a "two phase commit" nevű algoritmus valósítja meg. Három fázisa van az algoritmusnak:
Ebben a fázisban a központi koordinátor "elfelejti" a tranzakciót.
Ha minden résztvevő pozitív visszajelzést küld a koordinátornak, hogy felkészült, a koordinátor kiadja a végrehajtási parancsot az összes résztvevőnek, és véghez viszik a tranzakciót.
A kezdő központi "csomópont", amit global coordinator-nak, azaz központi koordinátornak hívnak, előkészíti az adott tranzakcióban résztvevő csomópontokat a commit fázisra. Ha már egy csomópont nem ad pozitív visszajelzést, az összes csomópont ROLLBACK utasítással visszatér az utasítás elejére, és a commit fázisra való felkészülés előröl kezdődik.
Az órán nem említett még két tranzakciós utasítás a SET TRANSACTION és a SET CONSTRAINT.*1
Egy tranzakció tulajdonságait a SET TRANSACTION utasítással állíthatjuk be. Ennek az utasításnak mindig a tranzakció első utasításának kell lennie.*2
*1 http://docs.oracle.com/database/121/SQLRF/statements_1001.htm#SQLRF30043
*2 https://gyires.inf.unideb.hu/KMITT/a03/ch05s06.html
A SAVEPOINT utasítással egy tranzakcióban mentési pontokat helyezhetünk el. Ezek a tranzakció részleges visszagörgetését szolgálják.*
Ezekre a visszatérési pontokra a ROLLBACK TO SAVEPOINT utasítással térhetünk vissza.
* https://gyires.inf.unideb.hu/KMITT/a03/ch05s06.html
Az egyszerű ROLLBACK utasítás érvényteleníti a teljes tranzakció hatását (az adatbázis változatlan marad), oldja a zárakat és törli a mentési pontokat. A tranzakció befejeződik.*1
A TO utasításrésszel rendelkező ROLLBACK a megadott mentési pontig görgeti vissza a tranzakciót, a megadott mentési pont érvényben marad, az azt követők törlődnek, a mentési pont után elhelyezett zárak feloldásra kerülnek és a tranzakció a megadott mentési ponttól folytatódik.*1
Ezzel, a hibával félbeszakadt műveletsort lehet töröltetni.*2
Példa:
INSERT INTO beosztas (beosztas_id, beosztas_nev)
VALUES (8, 'Pincér');
DELETE FROM beosztas
WHERE beosztas_nev='Pénztáros';
ROLLBACK;
-> Ilyenkor olyan mintha nem történt volna semmi. Visszaáll az INSERT INTO előtti állapotra.
2. példa:
INSERT INTO beosztas (beosztas_id, beosztas_nev)
VALUES (8, 'Pincér');
COMMIT;
DELETE FROM beosztas
WHERE beosztas_nev='Pénztáros';
ROLLBACK;
-> Ilyenkor visszaáll a COMMIT előtti állapotra.
*1 https://gyires.inf.unideb.hu/KMITT/a03/ch05s06.html
*2 http://www.kobakbt.hu/jegyzet/AdatbazisElmelet/ora5.html
Ha egyetlen DML művelet a végrehajtása során hibára fut, akkor annak az egy utasításnak az összes változtatása mindenestől visszavonásra kerül. Viszont a tranzakció során előzőleg végrehajtott módosítások megmaradnak.
Ha mégis a hiba miatt be kellene fejezni a teljes tranzakciót, azt a felhasználónak magának kell kezdeményeznie.
A tranzakció által végzett minden módosítás elveszik, a régi értékek visszaállnak, és a zárak felszabadulnak.
A COMMIT a tranzakció által okozott módosításokat átvezeti az adatbázisba és láthatóvá teszi azokat más munkamenetek számára, felold minden – a tranzakció működése közben elhelyezett – zárat és törli a mentési pontokat.*
* https://gyires.inf.unideb.hu/KMITT/a03/ch05s06.html
Az adatok módosítása véglegessé válik az adatbázisban. A tranzakciós módosítások előtti értékek már nem visszaállíthatóak. Minden munkamenet látja az új értékeket. Ezen kívül a zárak felszabadulnak, és a tranzakció során esetleg elhelyezett mentési pontok elvesznek.
Az a munkamenet, amelyben folyamaban van a tranzakció, az a SELECT utasításaiban látja a tranzakció során addig végrehajtott összes változtatást.
A többi munkamenet ekkor még nem látja a folyamatban lévő tranzakció által végzett módosításokat.
A folyamatban lévő tranzakció által módosított sorok le vannak zárva. Más tranzakciók módosíthatják ezeket a sorokat mindaddig, amíg a folyamatban lévő tranzakció be nem fejeződik.
DML = Data Manipulation Language
A DML utasítások a már meglévő táblák (általában "egyes séma-objektumok") adatait módosítják.
Azok számítanak DML utasításoknak, amikkel:
Az órai tananyagban foglaltakon kívül érdemes megemlíteni a többi DML utasítást is: *1
Néhány esetben ahhoz, hogy egy SQL utasítást végre tudjunk hajtani, az Oracle-nek további SQL utasításokat kell végrehajtania. Az ilyen
szükséges plusz utasításokat rekurzív hívásoknak nevezzük. Amikor egy lekérdezést akarunk futtatni, akkor a különböző ellenőrzések szintén ilyen rekurzív hívások formájában jelennek meg, ha a szükséges adatok nincsenek benne a data dictionary cache-ben. *2
Az EXPLAIN PLAN utasítást végrehajtási terv generálására használhatjuk. Ekkor az optimalizáló végrehajtja ugyanazokat a lépéseket, mintha futtatni szeretnénk egy SQL utasítást, csak éppen megáll a végrehajtási terv generálása végén, nem kezdi el végrehajtani azt. Az eredményt pedig egy adott táblába helyezi el - alapértelmezés szerint ez a tábla a PLAN_TABLE nevű tábla.
Az EXPLAIN PLAN utasítás legegyszerűbb szerkezete:
EXPLAIN PLAN FOR SQL_UTASÍTÁS
Példa:
EXPLAIN PLAN FOR
UPDATE aso_quote_headers_all
SET price_frozen_date = sysdate
WHERE quote_number = :qnum
AND quote_version = :ver; *2
Az oracle automatikusan sor szintű zárat helyez el az érintett sorokra a következő utasítások esetén: INSERT, DELETE, UPDATE, SELECT ... FOR UPDATE. Ezek a zárak a tranzakció végéig élnek. A sor szintű zárakon kívül az oracle automatikusan tábla szintű zárat is elhelyez az érintett táblákra a fenti utasítások esetén.
Tábla szintű zárat a LOCK TABLE utasítás hatására helyez el az oracle. Összesen ötféle tábla szintű zárat támogat a rendszer, amelyeket a LOCK TABLE utasítás megfelelő paraméterezésével manuálisan lehet kiváltani. A zárójelben megadtuk azt a számértéket is, ahogyan a rendszerkatalógusban a zárolási mód tárolva van.
A korábbi verziókkal való kompatibilitás miatt van még egy SHARE UPDATE mód is, ami a ROW SHARE szinonimája.
(RS -> 2) LOCK TABLE <tábla> IN ROW SHARE MODE
(RX -> 3) LOCK TABLE <tábla> IN ROW EXCLUSIVE MODE
(S -> 4) LOCK TABLE <tábla> IN SHARE MODE
(SRX -> 5) LOCK TABLE <tábla> IN SHARE ROW EXCLUSIVE MODE
(X ->6) LOCK TABLE <tábla> IN EXCLUSIVE MODE
A zárolási módok jelentése nagyjából a következő:
RS: Későbbi módosítási szándék jelzése, vagyis ne sajátítsa ki senki a táblát.
RX: Néhány sort módosítok éppen.
S: A táblát most egy darabig ne módosítsa senki, de olvashatja más is.
SRX: Ne módosítson senki, én viszont épp azt teszem.
X: Mindenki felejtse el a tábla módosítását, legfeljebb olvashatjátok azt.
*3
*1 http://docs.oracle.com/database/121/SQLRF/statements_1001.htm#SQLRF30043
*2 https://dea.lib.unideb.hu/dea/bitstream/handle/2437/85465/Szakdolgozat_Thurzo_Akos.pdf;jsessionid=04F0670494405A4FAD7E56D8DD09BE12?sequence=1
*3 http://people.inf.elte.hu/sila/eduAB/zarolasok.txt
A MERGE utasítás a többszörös INSERT és DELETE utasítások elkerülésére való.
Általános alakja:
MERGE INTO tábla [másodlagos_név]
USING {tábla|nézet|alkérdés} [másodlagos_név] ON (feltétel)
WHEN MATCHED THEN UPDATE SET oszlop={kifejezés|DEFAULT} [, oszlop={kifejezés|DEFAULT}]...
WHEN NOT MATCHED THEN INSERT (oszlop[, oszlop]...)
VALUES ({DEFAULT|kifejezés [, kifejezés]...});
Az INTO határozza meg a céltáblát, amelyet bővíteni vagy módosítani akarunk.
A USING adja meg az adatok forrását, amely tábla, nézet vagy egy alkérdés lehet.
Az ON utasításrészben megadott feltétel szolgál a beszúrás és módosítás vezérlésére. Minden olyan céltáblasor, amelyre igaz a feltétel, a forrásadatoknak megfelelően módosul. Ha valamelyik sorra a feltétel nem teljesül, az Oracle beszúrást végez a forrásadatok alapján.
A WHEN MATCHED utasításrész a céltábla új oszlopértékét határozza meg. Ez a rész akkor hajtódik végre, ha a feltétel igaz. A WHEN NOT MATCHED utasításrész megadja a beszúrandó sor oszlopértékét, ha a feltétel hamis.
* https://gyires.inf.unideb.hu/KMITT/a03/ch05s06.html
A DELETE utasítás a táblák meglévő sorainak törlésére szolgál.
Általánosan:
DELETE [FROM] table
[WHERE condition];
Példa:
DELETE FROM kave
WHERE kave_nev='Jegeskávé';
-> 1 row deleted.
FONTOS, ha véletlenül elhagyjuk a WHERE-t, az összes sor törlésre fog kerülni.
FONTOS, a DELETE utasítás végrehajtása után a törölt sorokat nem tudjuk egyetlen paranccsal visszaállítani (ez problémát okozhat, ha sok sort töröltünk egyszerre és nem tudjuk az adatokat). Ennek elkerülése érdekében érdemes elsőként SELECT utasítást írni a DELETE helyére, és megnézni mely sorok lesznek a kiválasztottak. Ha ezeket szerettük volna törölni, akkor átírhatjuk DELETE-re. *
* http://www.1keydata.com/sql/sqldelete.html
Egyetlen DELETE segítségével törölhetők olyan sorok, amelyek megtalálhatóak egy másik táblában, vagy akár azok is, amelyek nem találhatóak a másikban sem.
Példa:
DELETE FROM newalkalmazott
WHERE alkalmazott_id NOT IN (SELECT NVL(alkalmazott_beosztas, -1) FROM alkalmazott);
-> 59 row deleted.
Az UPDATE a meglévő tábla soraiban lévő attribútumok értékeinek módosítására szolgál.
Általános alak:
UPDATE table
SET column=value [, cloumn=value, ....]
[WHERE condition];
Példa 1 és több soros változtatásokra:
UPDATE kave
SET kave_ar=800
WHERE kave_id=15;
-> 1 row updated.
UPDATE alkalmazott
SET alkalmazott_vnev=UPPER(alkalmazott_vnev);
-> 165 rows updated.
A korrelált allekérdezés egy olyan SELECT utasítás, amely be van ágyazva a másik utasításba, és amely hivatkozik az őt körülvevő SQL utasítás oszlopára vagy oszlopaira.
Úgy lehetne elképzelni a működését, hogy a külső UPDATE utasítás által érintett minden sorra külön lefut a belső SELECT utasítás.
Példa: Az Alkalmazott tábla Comments mezőjébe szövegesen írjuk be, hogy az alkalmazott összesen hány órát dolgozott.
UPDATE alkalmazott a
SET comments=
(SELECT TO_CHAR(SUM(workhours))
FROM egyeb e
WHERE e.alkalmazott_id=a.alkalmazott_id);
-> 124 rows updated.
Példa:
UPDATE alkalmazott
SET (alkalmazott_id, alkalmazott_telefonszam)=(SELECT alkalmazott_id, alkalmazott_telefonszam FROM workers
WHERE alkalmazott_telefonszam=06201234567)
WHERE alkalmazott_telefonszam=06301234567;
-> 1 row updated.
Az INSERT INTO utasítással sort/sorokat tudunk beszúrni a már meglévő táblákba.
Ezzel a szintaxissal egy utasítással csupán egyetlen sort szúrhatunk be:
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
Megadhatjuk az oszlopokat és az értékeket amiket kapnak:
INSERT INTO kave (kave_id, kave_nev, kave_meret, kave_ar, kave_leiras)
VALUES (5, 'Latte macchiato', M, 750);
-> 1 rows inserted.
Írhatunk INSERT utasítást egy allekérdezéssel (ilyenkor nem szerepel a VALUES klauzula):
INSERT INTO preferred_kave
SELECT *
FROM kave
WHERE kave_meret in ('S', 'M');
-> 89 rows created.
FONTOS, hogy az INSERT klauzula oszlopainak a száma megegyezik az allekérdezés oszlopainak a számával:
INSERT INTO preferred_tipus(tipus_id, tipus_nev)
SELECT tipus_id, tipus_nev
FROM tipus
WHERE tipus_nev in ('Kávéház', 'Cafe & Pub');
-> 245 rows created.
Implicit módszer: azokat az oszlopokat kihagyjuk az oszloplistából, amelyekbe NULL kell, hogy kerüljön:
INSERT INTO kave (kave_id, kavezo_id, kave_nev, kave_meret, kave_ar)
VALUES (8, 3, 'Espresso', 'S', 300);
-> 1 row created.
Explicit módszer: NULL kulcsszót használunk a VALUES klauzulában:
INSERT INTO kave
VALUES (8, 3, 'Espresso', 'S', 300, NULL);
-> 1 row created.
Van aki a SELECT utasítást is a DML utasítások közé sorolja.
A SELECT utasítást egy korlátozott DML utasításnak tekintik, mivel az adatokhoz való hozzáférést teszi lehetővé, viszont magát az adatbázisban eltárolt adatokat nem tudja megváltoztatni.*
* http://docs.oracle.com/database/121/SQLRF/statements_1001.htm#SQLRF30042
