DML utasítások és tranzakciókezelés
Forrás: Előadás diáik
DML Utasítások
(Data Manipulation Language)
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.
Az UPDATE utasítás és a szintaxisa
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
Korrelált UPDATE utasítás
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.
Az INSERT utasítás és a szintaxisa
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
INSERT SELECT utasítás
Í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
NULL értékek beszúrása
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 DELETE utasítás
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';
Allekérdezés a DELETE-ben
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
Tranzakciók
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.
ACID szabályok
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
Á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
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
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
Konzisztencia (Consistency):A tranzakció befejezése után az adatbázisnak konzisztens állapotba kell kerülnie.
COMMIT és ROLLBACK utasítások
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
Adatok értéke a COMMIT vagy ROLLBACK előtt
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.
Adatok értéke a 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.A zárak (lefoglalások) felszabadulnak.A tranzakció során esetleg elhelyezett mentési pontok (SAVEPOINT) elvesznek.
Adatok értéke a ROLLBACK után
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
Utasítás szintű ROLLBACK
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)
Kétfázisú jóváhagyás
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)
SAVEPOINT utasítás
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
Olvasási konzisztencia
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.