Tranzakció bevezetés

• A tranzakciók alatt utasítást vagy utasítások sorozatát értjük, melyet egy felhasználó vagy alkalmazás hajt végre az adatbázis tartalmának olvasása vagy frissítése érdekében.

  • Úgy is megfogalmazhatjuk, mint az adatbázison végzett munka logikai egysége.

• Például tranzakciónak tekinthető ha két bankszámla között egyenleg átutalás történik.

  • Vegyük példának Karlos és Ray felhasználókat. Karlos 5000 Ft-ot utal Ray-nek.

  • Ez a folyamat számos alacsony szintű folyamatot foglal magába. Egy ilyen tranzakció a következő lépéseket foglalja magába:

    Open_Acc (Karlos)
    OldBal = Karlos.bal
    NewBal = OldBal - 5000
    Karlos.bal = NewBal
    CloseAccount(Karlos)
    
    OpenAccount(Ray)
    Old_Bal = Ray.bal
    NewBal = OldBal + 5000
    Ray.bal = NewBal
    CloseAccount(Ray)
    

• A gyakorlatban több SQL lekérdezést összefogunk egy csoportba és végrehajtjuk őket, mint egy tranzakció részét.

• Fontos, hogy ezeket a tranzakciókat megfelelően kezeljük az adatintegritás biztosítása és az adatbáziskezelői hibák kezelése végett.

• Tranzakciók tulajdonságai (angolul: ACID properties): Olyan tulajdonságok, amelyeket minden tranzakciónak követnie kell (ezeknek a végrehajtásáért különböző al-rendszerek felelősek).

  • Atomiság (atomicity): A “mindent vagy semmit” tulajdonság. Egy tranzakció vagy egészében kerül végrehajtásra, vagy nem kerül végrehajtásra egyáltalán.
  • Konzisztencia (consistency): A tranzakció egy állandó állapotból kell, hogy módosítsa az adatbázist egy másik állandó állapotba. Ez az adatbáziskezelő és az alkalmazás együttes felelőssége.
  • Izoláció (isolation): A tranzakciók egymástól függetlenül kerülnek végrehajtásra. A súrlódó hatásai egy még nem befejezett tranzakciónak nem szabad láthatónak lenniük más, párhuzamosan futó tranzakciók számára.
  • Tartósság (durability): A hatásai egy végrehajtott tranzakcióknak véglegesen rögzítésre kell kerülniük az adatbázisban és nem veszhetnek el, vagy tűnhetnek el egy későbbi hiba miatt.

Tranzakciók izolációs szintjei

• Az izoláció határozza meg, hogy a tranzakció integritás hogyan látható más felhasználók és rendszerek által. Más szavakkal, a tranzakció a rendszerben úgy kell, hogy megjelenjen, hogy az az egyetlen tranzakció, amely hozzáfér az adatbázis rendszer erőforrásaihoz.

• Az izolációs szintek határozzák meg annak a mértékét, hogy mely tranzakciókat kell elszigetelni a más tranzakciók által végzett adatmódosításoktól. Az izolációs szinteket a következő jelenségek határozzák meg:

  • Dirty Read: Az a szituáció, amikor a tranzakció olyan adatot olvas, amely még nem került commit-re.
    • Pl.: Amikor tranzakció 1 frissít egy rekordot és nem commit-eli, miközben tranzakció 2 már a frissített rekordot olvassa. Ha tranzakció 1 visszavonja (rollback) a módosítást, tranzakció 2 olyan adatot olvasott, amelyet úgy veszünk, hogy nem is létezett.
  • Non Repeatable read: Akkor történik, amikor egy tranzakció ugyanazt a rekordot kétszer olvassa és mindkétszer eltérő értéket kap.
    • Pl.: Tranzakció 1 épp adatot olvas. A párhuzamosság miatt tranzakció 2 ugyanazt a rekordot frissíti és commit-eli. Ha tranzakció 1 ugyanazt az adatot újraolvassa, más értéket fog kapni.
  • Phantom Read: Akkor történik, amikor két egyforma lekérdezés kerül végrehajtásra, de a visszakapott rekordok eltérőek.
    • Pl.: Tranzakció 1 lekérdez pár rekordot, ami megfelel egy feltételnek. Tranzakció 2 generál pár új rekordot, ami megfelel tranzakció 1 feltételének. Ha tranzakció 1 újra végrehajtásra kerül, akkor más outputot fog kapni.

• Ezen jelenségek alapján az SQL 4 izolációs szintet határoz meg:

  1. Read Uncommitted: A legalacsonyabb izolációs szint. Ezen a szinten a tranzakció olvashat nem committelt változtatásokat, melyeket más tranzakciók végeztek. Ezáltal lehetővé teszi a dirty reads jelenséget. Ezen a szinten a tranzakciók nincsenek izolálva egymástól.
  2. Read Committed: Ez a szint biztosítja, hogy minden adat olvasott adat committelve van, abban a pillanatban ahogy olvasva van. Ezzel kizárja a dirty read lehetőségét. A tranzakció egy aktuális rekordot zárolja írásra és olvasásra, amely meggátolja, hogy más tranzakció olvassa, frissítse vagy törölje azt.
  3. Repeatable Read: Ez a legszigorúbb izolációs szint. A tranzakció minden referált/kapcsolódó rekordot zárol olvasásra, és írásra (update és delete műveletekre). Mivel más tranzakciók nem tudják olvasni, frissíteni vagy törölni ezeket a rekordokat, kizárja a non-repeatable read jelenségét.
  4. Serializable: Ez a legmagasabb izolációs szint. A tranzakciók végrehajtása egymásutáni.

  

• A választott izolációs szint függ az alkalmazás igényeitől. Magasabb szintek erősebb adatkonzisztenciát biztosítanak, de hosszabb zárolási időket eredményezhet, ami csökkenti a párhuzamosságot és a teljesítményt. Az alacsonyabb szintek több párhuzamosságot tesznek lehetővé az adatkonzisztencia csökkenése mellett.

• Ezek mellett a szintek mellett egyes adatbáziskezelő rendszerek további szinteket is támogathatnak.

• Az izolációs szintek előnyei:

  • Magasabb párhozamosság: Az izolációs szintek lehetővé teszik, hogy több tranzakció is párhuzamosan fusson anélkül, hogy egymás munkáját megzavarnák.
  • Kontroll az adatkonzisztencia felett: Az izolációs szintek kontrollt biztosítanak az adatkonzisztencia felett.
  • Kevesebb adat anomália: Az izolációs szintek segítségével csökkenthetjük az adat anomáliák számát, mint például dirty-reads, non-repeatable reads és phantom reads.
  • Rugalmasság: Az izolációs szintek rugalmasságot biztosítanak az alkalmazások tervezése során, amelyek eltérő adat konzisztenciát igényelnek.

• Az izolációs szintek hátrányai:

  • Megnövekedett erőforrásigény: Az izolációs szintek használata megnöveli az erőforrásigényt, mivel az adatbáziskezelő rendszernek több ellenőrzést és zárolást kell végeznie.
  • Csökkentett párhuzamosság: Néhány izolációs szint (pl.: serializable) csökkentheti a párhuzamosságot azáltal, hogy a tranzakcióktól több zárolást vár el.
  • Korlátozott támogatás: Nem minden adatbáziskezelő rendszer támogatja az összes izolációs szintet, ami korlátozhatja az alkalmazások mozgatását más rendszerek között.
  • Komplexitás: Az eltérő izolációs szintek használata növelheti a komplexitását az adatbáziskezelő alkalmazások tervezésének, ami megnehezíti az implementációjukat és a karbantartásukat.

Tranzakció kezelő utasítások

• A tranzakciókezelő parancsokat csak más adatbáziskezelő parancsokkal együtt használjuk, pl.: INSERT, UPDATE és DELETE.
• Nem használhatóak azonban táblák létrehozásakor és törlésekor, mert ezek a műveletek automatikusan commit-elve lesznek az adatbázisba.
• Az utasítások a következők:

  • COMMIT − A tranzakció által végrehajtott változtatások mentése. Minden tranzakciót ment, amely az adatbázison végrehajtásra került a legutóbbi COMMIT vagy ROLLBACK parancs óta.

    COMMIT;
    

    --Példa
    
    --Tábla létrehozása a példa számára
    CREATE TABLE customers (
       id INT NOT NULL,
       name VARCHAR (20) NOT NULL,
       age INT NOT NULL,
       address CHAR (25),
       salary DECIMAL (18, 2),
       PRIMARY KEY (id)
    );
    
    --Tábla feltöltése adatokkal
    INSERT INTO customers VALUES 
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'Hyderabad', 4500.00),
    (7, 'Muffy', 24, 'Indore', 10000.00);
    
    --A 25 éves vásárlók törlése az adattáblából.
    DELETE FROM customers WHERE age = 25;
    
    --A változások mentése.
    COMMIT;
    

    • MySQL esetén nem szükséges manuálisan használni a COMMIT parancsot, mert az autocommit rendszerváltozó engedélyezve van. Ezért minden adatmódosító lekérdezés a változtatásokat egyből végre is hajtja.
    • Az olyan esetekben, amelyeknél szükséges a COMMIT parancs használata, a MySQL a START TRANSACTION parancsot alkalmazza.

  • ROLLBACK − A még nem mentett (COMMIT-elt) változtatások visszavonása. Csak azokat a változtatásokat tudja visszavonni, amelyek a legutóbbi COMMIT vagy ROLLBACK óta történtek.

    ROLLBACK;
    

    --Példa
    
    --A 25 éves vásárlók törlése az adattáblából.
    DELETE FROM customers WHERE age = 25;
    
    A változtatások visszavonása.
    ROLLBACK;
    

  • SAVEPOINT − Mentési pontokat hoz létre a tranzakción belül a ROLLBACK parancs számára.

    • Alapesetben egy ROLLBACK parancs a legutóbbi COMMIT-ig vonja vissza a változtatásokat. Azonban lehetőségünk van mentési pontok létrehozására, hogy csak részben vonjuk vissza a tranzakció módosításait.

    • Két COMMIT között több mentési pontot is megadhatunk.

    • Mentési pont létrehozása:

      SAVEPOINT savepoint_name;
      

    • Mentési pont visszaállítása:

      ROLLBACK TO savepoint_name;
      

      --Példa
      
      --Törlünk 3 vásárlót, de minden törlés előtt létrehozunk egy mentési pontot.
      SAVEPOINT sp1;
      
      DELETE FROM customers WHERE id=1;
      
      SAVEPOINT sp2;
      
      DELETE FROM customers WHERE id=2;
      
      SAVEPOINT sp3;
      
      DELETE FROM customers WHERE id=3;
      
      --Egy mentési pont visszaállítása
      ROLLBACK TO sp2;
      

    • Mentési pont törlése:

      RELEASE SAVEPOINT savepoint_name;
      

  • SET TRANSACTION − Egy tranzakció inicialzálása. Ezzel a paranccsal tudjuk meghatározni a tranzakció tulajdonságait (például az izolációs szintet vagy a hozzáférés módját).

    • Az alapértelmezett izolációs szint a REPEATABLE READ.
    • A hozzáférés módja lehet olvasás (READ ONLY) vagy írás olvasás (READ WRITE). Alapértelmezetten READ WRITE módot használ.
    • A tranzakciók karakterisztikáját megadhatjuk globálisan (GLOBAL) vagy munkamenet szinten is (SESSION).
      • GLOBAL esetén a megadott tulajdonságok minden munkamenetre érvényesek lesznek.
      • A SESSION kulcsszó esetén a tulajdonságok csak a jelenlegi munkamenetben végrehajtásra kerülő tranzakciókra lesznek érvényesek.

    SET [GLOBAL | SESSION] TRANSACTION
       { ISOLATION LEVEL level | access_mode};
    

    --Példa
    
    --Létrehozunk egy táblát az alábbi utasítással
    CREATE TABLE employee(
       first_name VARCHAR(20),
       last_name VARCHAR(20),
       age INT,
       income INT
    );
    
    --Tranzakció indítása, beállítása, majd adatok betöltése CSV és XML fájlokból
    START TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    LOAD DATA INFILE 'C:/uploads/data.csv' into table employee
       FIELDS TERMINATED BY ','
       LINES TERMINATED BY '\\n';
    SAVEPOINT mysavepoint;
    load xml infile 'C:/uploads/data.xml' into table employee ROWS IDENTIFIED BY '<row>';
    

  • START TRANSACTION - Tranzakció indítása.

    • Ebben az esetben minden adatmódosító utasítás, pl.: INSERT, DELETE vagy UPDATE addig nem fog tényleges változtatást végrehajtani az adatbázison, amíg egy explicit COMMIT vagy ROLLBACK parancs nem kerül kiadásra.
    • Alternatívája a BEGIN parancs.

    START TRANSACTION;
    --Adatmódosító parancsok
    COMMIT;
    

    START TRANSACTION [transaction_property [, transaction_property] ...] | BEGIN [WORK]
    COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
    ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
    SET autocommit = {0 | 1}
    
    /*transaction_property:
        WITH CONSISTENT SNAPSHOT
      | READ WRITE
      | READ ONLY*/
    

    • Az AND CHAIN kulcsszavak egy új tranzakciót indítanak, amint a jelenlegi véget ér ugyanazzal az izolációs szinttel, mint a befejezett tranzakció.
    • A RELEASE kulcsszó hatására a szerver bontja a kapcsolatot a klienssel a tranzakció végrehajtása után.
    • A NO kulcsszó felülírja a CHAIN és RELEASE parancsok végrehajtását. Így biztosítani tudjuk, hogy azok más beállítás hatására se kerüljenek végrehajtásra.
    • A WITH CONSISTENT SNAPSHOT tulajdonság egy folytonos olvasási folyamatot indít el az adatbázis tároló motor felé (pl.: InnoDB). Megegyezik azzal, mintha a tranzakció létrehozását egy SELECT utasítás követné.
    • A SET autocommit beállítás letiltja vagy engedélyezi az alapértelmezett automatikus COMMIT-olást a jelenlegi munkamenetben.

Táblák zárolása

• Lehetőségünk van a táblák zárolására annak érdekében, hogy együttműködjünk más munkameneteket a táblákhoz való hozzáférés kezelésében, vagy, hogy meggátoljunk egy időre más munkameneteket a táblák módosításában (például amikor egy másik munkamenetnek kizárólagos hozzáférésre van szüksége).