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

Meer zoals dit

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

door Szilard Denes

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

door Zsolt Vajda

3_eloadas_vol2

door Adam Szalay

Előadás_3_Mindmap_MülhauserAttila_AN40E0

door Attila Mülhauser

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.