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

DML = Data Manipulation LanguageA 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:Új sorokat szúrunk be a táblába - INSERTA táblák sorait módosítjuk - UPDATEA táblák sorait töröljük - DELETEMERGE

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

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.

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 kaveVALUES (8, 3, 'Espresso', 'S', 300, NULL);-> 1 row created.

Írhatunk INSERT utasítást egy allekérdezéssel (ilyenkor nem szerepel a VALUES klauzula):INSERT INTO preferred_kaveSELECT *FROM kaveWHERE 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_nevFROM tipusWHERE tipus_nev in ('Kávéház', 'Cafe & Pub');-> 245 rows created.

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 tableSET column=value [, cloumn=value, ....][WHERE condition];Példa 1 és több soros változtatásokra:UPDATE kaveSET kave_ar=800WHERE kave_id=15;-> 1 row updated.UPDATE alkalmazottSET alkalmazott_vnev=UPPER(alkalmazott_vnev);-> 165 rows updated.

Példa:UPDATE alkalmazottSET (alkalmazott_id, alkalmazott_telefonszam)=(SELECT alkalmazott_id, alkalmazott_telefonszam FROM workersWHERE alkalmazott_telefonszam=06201234567)WHERE alkalmazott_telefonszam=06301234567;-> 1 row 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 aSET comments=(SELECT TO_CHAR(SUM(workhours))FROM egyeb eWHERE e.alkalmazott_id=a.alkalmazott_id);-> 124 rows updated.

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 kaveWHERE 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 newalkalmazottWHERE alkalmazott_id NOT IN (SELECT NVL(alkalmazott_beosztas, -1) FROM alkalmazott);-> 59 row deleted.

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

Az órai tananyagban foglaltakon kívül érdemes megemlíteni a többi DML utasítást is: *1CALLNé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 ilyenszü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. *2EXPLAIN PLANAz 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ÁSPélda:EXPLAIN PLAN FORUPDATE aso_quote_headers_allSET price_frozen_date = sysdateWHERE quote_number = :qnumAND quote_version = :ver; *2LOCK TABLEAz 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 MODEA 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 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.*1A 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

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

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 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.*1A 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.*1Ezzel, a hibával félbeszakadt műveletsort lehet töröltetni.*2Példa:INSERT INTO beosztas (beosztas_id, beosztas_nev)VALUES (8, 'Pincér');DELETE FROM beosztasWHERE 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 beosztasWHERE 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

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

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 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 órán nem említett még két tranzakciós utasítás a SET TRANSACTION és a SET CONSTRAINT.*1Egy 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

Egy igen bonyolult algoritmus, a "two phase commit" nevű algoritmus valósítja meg. Három fázisa van az algoritmusnak:Prepare/Előkészületi fázisCommit/Végrehajtó fázisForget/Felejtő 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.

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.

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

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:Aki csupán olvas (SELECT), az nem vár mások folyamatában lévő tranzakcióiraAki módosít, nem vár azokra, akik csak olvasnakAki módosít, vár azokra, akik szintén módosítják ugyanazokat az adatokatPé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.

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)

Az ISO szabvány a következő lehetséges izolációs szinteket definiálja:Read UncomittedRead ComittedRepeatable ReadSerializableEzek 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/

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:Dirty reads: 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…Non-repeatable reads: 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.Phantom reads: 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/

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/

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/

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/

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

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

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

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

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