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

A DML utasítások a meglévő táblák (általánosabban "egyes séma-objektumok") adatait módosítják.DML utasítás lehet, ha:új sorokat szúrunk be egy táblábaa táblák meglévő sorait módosítjuka táblák meglévő sorait töröljükegyes adatbázis kezelőkben a MERGE is ilyen utasításA DML utasítások sorozatát tranzakciónak nevezzük, amely az adatmódosításaink egy logikai egysége. Ezeket a módosításokat egységenként vagy érvényesítjük a végén, vagy mindenestül visszavonjuk.

A meglévő sorok értékeinek a módosítása.Az UPDATE utasítás a tábla soraiban, vagy a nézet alaptáblája, esetleg egy materializált nézet soraiban lévő attribútumok értékeit módosítja:Példa:UPDATE tableSET column = value [, column = value, ...][WHERE condition];Példa:UPDATE workersSET   division_id = 70WHERE worker_id = 100; 1 row updatedPélda:UPDATE workersSET  last_name = UPPER(last_name); 53 rows updatedEgyszerre tetszőleges számú sor is módosítható allekérdezéssel:Példa:UPDATE  workersSET (position_id, salary) = (SELECT position_id, salary FROM   workers  WHERE  worker_id = 205) WHERE   worker_id = 206;1 rows updated

A korrelált allekérdezés egy olyan SELECT utasítás, amely be van ágyazva a másik utasításba (itt pl. az UPDATE-be), és amely hivatkozik az őt körülvevő SQL utasítás oszlopára vagy oszlopaira. Amikor elképzeljük a működését, akkor úgy képzeljük, hogy a külső UPDATE utasítás által érintett minden sorra külön lefut a belső SELECT.Példa:UPDATE customers cSET comments = (SELECT TO_CHAR(SUM(total_amount))FROM orders oWHERE o.customer_id = c.customer_id); 135 rows updatedA példában a CUSTOMERS tábla COMMENTS mezőjébe írtuk be azt, hogy az illető ügyfél összesen mekkora értékben rendelt terméket.

Sorok beszúrása a táblába. Az INSERT utasítással sorokat adunk hozzá a táblához, a nézet alaptáblájához, vagy esetleg egyéb nem szabványos elemhez.Példa:INSERT INTO table [(column [, column...])]VALUES (value [, value...]);Ezzel a szintaxissal egy utasítással csupán egy sort szúrhatunk be.Megadjuk az oszlopokat, amelyek értéket kapnak:Példa:INSERT INTO divisions (division_id,division_name,manager_id,city,country_id,parent_id)VALUES (2,'Headquarters',100,'San Francisco','US',NULL);1 rows inserted

Írhatunk INSERT utasítást egy allekérdezéssel:Példa:INSERT INTO preferred_customersSELECT * FROM  customersWHERE credit_rating in ('GOOD','EXCELLENT');105 row createdIlyenkor nem szerepel a VALUES klauzula.Az INSERT klauzula oszlopainak a száma megegyezik az allekérdezés oszlopainak a számával:Példa:INSERT INTO preferred_customers(customer_id ,customer_name,city)SELECT customer_id ,customer_name,city FROM  customersWHERE credit_rating in ('GOOD','EXCELLENT');105 row created

Implicit módszer: azokat az oszlopokat kihagyjuk az oszloplistából, amelyekbe NULL kell, hogy kerüljön:Példa:INSERT INTO divisions(division_id,division_name,manager_id,city) VALUES (2,'Headquarters',100,'San Francisco');1 row createdExplicit módszer: NULL kulcsszót használunk a VALUES klauzulában:Példa:INSERT INTO   divisionsVALUES (100, 'Finance', NULL, NULL, NULL, NULL);1 row created

A táblák meglévő sorainak törlése.Példa:DELETE [FROM] table[WHERE condition];Példa:DELETE FROM divisionsWHERE division_name = 'Contracting';1 row deletedPélda:DELETE FROM divisionsWHERE division_name = 'Recruiting';

Egy DELETE segítségével törölhetők ki olyan sorok is, amelyek megtalálhatóak egy másik táblában, vagy akár fordítva, azok, amelyek nem találhatóak a másikban sem.Példa:DELETE FROM newworkerWHERE worker_id NOT IN(SELECT NVL(manager_id,-1) FROM  workers); 39 row deleted

A tranzakció SQL műveletek (DML műveletek) egy olyan sorozata, amely vagy teljes egészében érvényre jut, vagy annak minden módosítása visszavonásra kerül.A tranzakciókkal logikai csoportokba rendezzük a módosításainkat.A fejlesztő (programozó) felelőssége, hogy pontosan a megfelelő egységekbe (tranzakciókba) csoportosítsa a DML műveleteket.

Az adatbáziskezelők azon funkcióit, melyek a többfelhasználós környezetben biztosítják minden felhasználó számára a konzisztens adatbázis képet az ACID rövidítéssel jellemzik (Atomicity, Cosistency, Isolation, Durability - Atomikusság, Konzisztencia, Elszigeteltség, Állandóság).

Állandóság (Durability):A lezárt tranzakciók eredménye nem veszhet el. Hatékonysági okokból az adatbáziskezelők nem írják rögtön diszkre a módosított adatokat, hanem memóriában tárolják. Például egy hardver hiba a memória tartalma és így a tranzakció eredményének elvesztésével járhatna. Az ilyen adatvesztéseket az adatbáziskezelők tranzakció log használatával oldják meg.

Atomikusság (Atomicity):A tranzakcióba bevont DML utasításokat egy egységként kell kezelnie az adatbáziskezelőnek, például, ha a tranzakció végrehajtása valamilyen hardver, szoftver hiba miatt megszakad, akkor az adatbáziskezelőnek automatikusan vissza kell vonnia az addig végrahatott műveleteket, hogy a tranzakció kezdése előtti konzisztens állapot álljon elő.

Elszigeteltség (Isolation):A párhuzamosan futó tranzakcióknak egymástól függetlenül kell működniük. Minden felhasználónak úgy kell tünnie mintha csak ő használná az adatbázist. Hatékonysági okokból nem lehet a függetlenséget úgy biztosítani, hogy egyidőben csak egy tranzakció futhat az adatbázison. Helyette sor illetve tábla zárolásokat alkalmaznak az adatbáziskezelők.

Konzisztencia (Consistency):A tranzakció befejezése után az adatbázisnak konzisztens állapotba kell kerülnie.

Ha egy tranzakciót véglegesíteni akarunk, azt a COMMIT utasítással tesszük.Ha viszont menet közben meggondoltuk magunkat, és vissza akarjuk vonni az éppen folyamatban lévő tranzakciónkat, azt a ROLLBACK utasítással tesszük.Példa:SELECT COUNT(*) FROM newworker;COUNT(*)----------53 INSERT INTO newworker SELECT * FROM newworker;53 rows insertedSELECT COUNT(*) FROM newworker;COUNT(*)----------106 DELETE FROM newworker WHERE position_id LIKE 'SALES%';28 rows deletedSELECT COUNT(*) FROM newworker;COUNT(*)---------- 78 ROLLBACK; rollback completeSELECT COUNT(*) FROM newworker;COUNT(*)----------53

Az adatok tranzakció előtti állapotát még mindig vissza lehet állítani (ROLLBACK).Az a munkamenet, amelyben folyamatban van a tranzakció, az SELECT utasításaiban látja a tranzakció során addig végrehajtott összes változtatást (DML).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 (foglalva). Más tranzakciók nem 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.A zárak (lefoglalások) felszabadulnak.A tranzakció során esetleg elhelyezett mentési pontok (SAVEPOINT) elvesznek.

A tranzakció által elvégzett minden módosítát elveszikA régi értékek visszaállnakA zárak (lefoglalások) felszabadulnakPélda:DELETE FROM copy_emp;22 rows deletedROLLBACK ;Rollback complete

Ha egyetlen DML művelet a végrehajtása során hibára fut, akkor annak az 1 utasításnak az összes változtatása mindenestől visszavonásra kerül A tranzakció során előzőleg végrehajtott módosjtások megmaradnakHa mégis a hiba miatt be kéne fejezni a tejes tranzakciót, azt a felhasználónak magának kell kezdeményeznie (COMMIT vagy ROLLBACK utasítások)

Two phase commitElosztott adatbázisokon igen nehéz biztosítani ezt az atomicitástEgy igen bonyolult algoritmus, a „two phase commit” algoritmus valósítja ezt meg:Prepare fázisCommit fázisUtána elfelejthetők a részletek (forget fázis)

Egyes adatbáziskezelőknél a tranzakció során elhelyezhetőek olyan pontok, amelyekre opcionálisan vissza lehet térni, ez a potenciális visszatérési pont a SAVEPOINT.Ezekre a visszatérési pontokra a ROLLBACK TO SAVEPOINT utasítással térhetünk vissza.Példa:UPDATE...SAVEPOINT update_done;Savepoint createdINSERT...ROLLBACK TO update_done; Rollback complete

Az adatbáziskezelő működési módja ez, amely azt biztosítja, hogy aki olvassa az adatokat, ne láthassa mások befejezetlen tranzakcióitKü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 folyamatban lévő tranzakcióira Aki 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 adatokatPl. az Oracle a tranzakciókezelést és az olvasási konzisztenciát lockokkal és úgynevezett UNDO adatok vezetésével valósítja meg, de a konkrét megvalósítás – lévén rendkívül bonyolult – túlmutat az előadás keretein. A későbbiekben még megemlítjük majd röviden.