Kategóriák: Minden - adatbázis

a Anna Szalay 7 éve

346

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.

4. előadás gondolattérképe

DML utasítások és tranzakciókezelés

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

ACID tulajdonság

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

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

Isolation

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

Consistency

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

Atomicity

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

Izolációs szintek

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/

Serializable

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/

Repeateble Read

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/


Read Comitted

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/

Read Uncomitted

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 Coffee séma

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)

Tranzakció

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

Olvasási konzisztencia

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.

Kétfázisú jóváhagyás

Egy igen bonyolult algoritmus, a "two phase commit" nevű algoritmus valósítja meg. Három fázisa van az algoritmusnak:

Forget fázis

Ebben a fázisban a központi koordinátor "elfelejti" a tranzakciót.

Commit fázis

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.

Prepare fázis

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.

Egyéb tranzakciós utasítások

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

SAVEPOINT

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

Tranzakciók vezérlése
ROLLBACK

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

Utasítás-szintű ROLLBACK

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.

Példa a ROLLBACK utasításra
Adatok értéke ROLLBACK után

A tranzakció által végzett minden módosítás elveszik, a régi értékek visszaállnak, és a zárak felszabadulnak.

COMMIT

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

Adatok értéke COMMIT után

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.

Adatok értéke COMMIT vagy ROLLBACK előtt

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 utasítások

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:

Egyéb DML utasítások

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

MERGE

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

DELETE

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

Allekérdezés DELETE-ben

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.

UPDATE

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.

Korrelált UPDATE

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.

UPDATE allekérdezéssel

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.

INSERT

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.

INSERT SELECT

Í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.

NULL értékek beszúrása

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.

(SELECT)

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