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

Mehr dazu

Adatintegritási szabályok (adatbázis kényszerek)

von Szilard Denes

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

von Zsófia Szombati

Adatbázis tervezése (Database Design II.)

von Seres Bianka

Tranzakcionálisan konzisztens működés, zárak és működés naplózása

von Kinga Tajti

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

Forrás: Előadás diáik

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.

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ó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 folyamatban lévő tranzakcióira
• Aki módosít, nem vár azokra, akik csak olvasnak
• Aki módosít, vár azokra, akik szintén módosítják ugyanazokat az adatokat
• Pl. 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.

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 created

INSERT...

ROLLBACK TO update_done;

Rollback complete

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 inserted

SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

106

DELETE FROM newworker WHERE position_id LIKE 'SALES%';

28 rows deleted

SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

78

ROLLBACK;

rollback complete

SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

53

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

Two phase commit

• Elosztott adatbázisokon igen nehéz biztosítani ezt az atomicitást
• Egy igen bonyolult algoritmus, a „two phase commit” algoritmus valósítja ezt meg:
• Prepare fázis
• Commit fázis
• Utána elfelejthetők a részletek (forget fázis)

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 megmaradnak
• Ha 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)

Adatok értéke a ROLLBACK után

• A tranzakció által elvégzett minden módosítát elveszik
• A régi értékek visszaállnak
• A zárak (lefoglalások) felszabadulnak

Példa:

DELETE FROM copy_emp;

22 rows deleted

ROLLBACK ;

Rollback complete

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

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

Konzisztencia

Konzisztencia (Consistency):

A tranzakció befejezése után az adatbázisnak konzisztens állapotba kell kerülnie.

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.

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

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

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ába
• a táblák meglévő sorait módosítjuk
• a táblák meglévő sorait töröljük
• egyes adatbázis kezelőkben a MERGE is ilyen utasítás

A 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 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 divisions

WHERE division_name = 'Contracting';

1 row deleted

Példa:

DELETE FROM divisions

WHERE 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 newworker

WHERE worker_id NOT IN

(SELECT NVL(manager_id,-1)

FROM  workers);

39 row deleted

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

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 created

Explicit módszer: NULL kulcsszót használunk a VALUES klauzulában:

Példa:

INSERT INTO   divisions

VALUES (100, 'Finance', NULL, NULL, NULL, NULL);

1 row created

INSERT SELECT utasítás

Írhatunk INSERT utasítást egy allekérdezéssel:

Példa:

INSERT INTO preferred_customers

SELECT * FROM  customers

WHERE credit_rating in ('GOOD','EXCELLENT');

105 row created

Ilyenkor 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  customers

WHERE credit_rating in ('GOOD','EXCELLENT');

105 row created

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 table

SET column = value [, column = value, ...]

[WHERE condition];

Példa:

UPDATE workers

SET   division_id = 70

WHERE worker_id = 100;

1 row updated

Példa:

UPDATE workers

SET  last_name = UPPER(last_name);

53 rows updated

Egyszerre tetszőleges számú sor is módosítható allekérdezéssel:

Példa:

UPDATE  workers

SET (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 c

SET comments = (SELECT TO_CHAR(SUM(total_amount))

FROM orders o

WHERE o.customer_id = c.customer_id);

135 rows updated

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