Delphi - MS SQL adatbázis kezelés Delphi-ből

Klasszikus példa egy pénzügyi tranzakció programozása. Tegyük fel, hogy egy ügyfél valamekkora összegű pénzt kíván átutalni az egyik számláról (A) a másikra (B). Ekkor a programnak az összeget le kell vonnia az A számláról, hozzá kell adnia a B számlához, továbbá a pénzügyi tranzakciót fel kell tüntetni a forgalmi naplóban és még ki tudja, hány másik táblában. A program mondjuk először leveszi az összeget az A számláról, majd mielőtt hozzáírná a B számlához, meghibásodik, vagy áramszünet lesz. Ha a műveletek nem lennének egy tranzakcióban, az ügyfél számára elveszne a pénze. Így azonban a tranzakcióban szereplő minden művelet érvénytelen, az adatbázis eredeti állapotába áll vissza, vagyis az összeg az ügyfél A számláján marad.

A tranzakciót négy jellemző tulajdonsága minősíti. (ACID: Atomicity, Consistency, Isolation, Durability). A tranzakciónak a munka egy elemi (atomic) szeletét kell definiálnia. Amikor egy tranzakció lezajlik, az adatbázisnak konzisztens állapotba kell kerülnie. A konkurens tranzakcióknak izoláltaknak kell lenniük, vagyis, ha egy A tranzakció módosít egy adatot, akkor egy másik, vele egyidejűleg futó B tranzakció ugyanannak az adatnak a módosítás előtti értékét látja egészen addig, amíg az A tranzakció be nem fejeződik. A módosított értékek csak ez után láthatók. Egy tranzakció lezajlása után a végrehajtott módosítások állandósulnak, még egy esetleges rendszerhiba után is megmaradnak.

Az adatbázis fizikai integritásáért az SQL Server felel. A programozó feladata, hogy az adatok logikai integritását biztosítsa. Ehhez meg kell határoznia, hogy hol kell tranzakciókat alkalmaznia, definiálnia kell a tranzakciók kezdetét és végét, illetve azokat a műveleteket, amelyek egy tranzakcióba kerülnek. Minden tranzakció végén az adatbázisnak konzisztens állapotban kell lennie!

Az adatbázis fizikai integritásának megőrzésére az SQL Serverben a következő automatikus mechanizmusok szolgálnak:

Zárolási képesség a tranzakciók izolálásához. A zárolásról részletesebben lesz szó később.
Naplózó képesség a maradandósági (durability) kritérium kielégítésére. Egy esetleges rendszerleállás után az SQL szerver újraindulásakor a tranzakciós napló segítségével az összes be nem fejezett tranzakciót visszagörgeti.
Tranzakciókezelő képesség. Egy tranzakció elindítása után annak sikeresen be kell fejeződnie. Ellenkező esetben az SQL Server visszaállítja a tranzakció előtti állapotot.

Tranzakció indítására a BEGIN TRANSACTION utasítás szolgál. Lezárása kétféleképpen történhet. A sikeres tranzakciót COMMIT utasítás zárja le. A COMMIT végrehajtása garantálja, hogy a tranzakció során végrehajtott módosítások ezután az adatbázis állandó részét képezik. A tranzakció során keletkezett zárolások is ekkor szabadulnak fel. Ha hiba merül fel a tranzakció során, akkor annak a ROLLBACK utasítás vet véget. Ekkor minden módosítás érvényét veszti, az adatbázis a tranzakció előtti állapotba kerül. ROLLBACK esetén a használt zárolások szintén felszabadulnak.

Nézzünk egy példát. A pubs adatbázisban a szerzői jogdíjakat a roysched nevű tábla tartalmazza. Szeretnénk megemelni minden számítástechnikai könyv szerzői jogdíját 10 százalékkal. Az ilyen könyvek azonosítója (title_id mező) 'Pc'-vel kezdődik.

BEGIN TRANSACTION MyTransaction

USE pubs
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'

COMMIT TRANSACTION MyTransaction

A tranzakciónak lehet neve, de ez nem kötelező. Beágyazott tranzakciók esetében lehet rá szükség, hogy a BEGIN TRANSACTION - COMMIT párokat meg lehessen különböztetni egymástól. Ez a megkülönböztetés csak a programozónak szól, az SQL Server nem veszi figyelembe ezeket a neveket. A BEGIN TRANSACTION kiadása után azok az erőforrások, amik részt vesznek az adatbázis-műveletekben, zárolás alá kerülhetnek. Zárolt állapotuk mindaddig megmarad, amíg a tranzakciót egy COMMIT, ROLLBACK, vagy egy hiba le nem zárja. Sokáig nyitva hagyott tranzakciók tehát megakadályozhatják, hogy más felhasználók is hozzáférjenek az éppen zárolt adatokhoz. Bár a BEGIN TRANSACTION végrehajtásával a tranzakció kezdetét veszi, a tranzakciós naplóba ekkor még nem kerül bejegyzés. Erre csak akkor kerül sor, ha a tranzakció során végrehajtjuk az INSERT, UPDATE vagy DELETE utasítások valamelyikét.

A @@TRANCOUNT nevű globális változó tárolja a rendszer aktív tranzakcióinak számát. Ezt a BEGIN TRANSACTION eggyel növeli, a COMMIT TRANSACTION eggyel csökkenti. Ha a COMMIT után a változó értéke nem nulla, például egy beágyazott tranzakció esetében, akkor a változtatások még nem kerülnek véglegesítésre, nem történik meg az erőforrások felszabadítása a zárolás alól.

A ROLLBACK utasítás visszagörgeti a kiadott utasításokat egészen a tranzakció elejéig. Beágyazott tranzakció esetében nem veszi figyelembe a belső tranzakciókat, az érvénytelenítés egészen a külső tranzakció elejéig történik. A @@TRANCOUNT változó értéke ilyenkor 0 lesz. Egy tranzakció sohasem görgethető már vissza COMMIT után!

Előfordulhat, hogy egy tranzakció során ROLLBACK utasítást kell alkalmaznunk, de nem akarjuk az összes műveletet érvényteleníteni. Ilyenkor a SAVE TRANSACTION utasítással jelölhetjük ki a visszagörgetés határát. Az utasításban a helyet egy névvel jelöljük meg, és a COMMIT kiadásakor erre a névre kell hivatkoznunk. A következő példában ismét a pubs adatbázist használjuk. A 'The Gourmet Microwave' című könyv két szerzője 75%-25% arányban részesülnek a szerzői jogdíjból. Ezt az arányt szeretnénk 65%-35%-ra módosítani. Továbbá kíváncsiak vagyunk arra is, hogy ha 10%-kal emelnénk a könyv árát, az hogyan befolyásolná a szerzők bevételét.

Először tehát a két szerző rekordját kell módosítani. Mivel a két UPDATE között az adatbázis inkonzisztens állapotban van, a műveleteket tranzakcióban fogjuk össze:

BEGIN TRANSACTION royaltychange
    UPDATE titleauthor
        SET royaltyper = 65
            FROM titleauthor, titles
                WHERE royaltyper = 75
                    AND titleauthor.title_id = titles.title_id
                    AND title = 'The Gourmet Microwave'
    UPDATE titleauthor
        SET royaltyper = 35
            FROM titleauthor, titles
                WHERE royaltyper = 25
                    AND titleauthor.title_id = titles.title_id
                    AND title = 'The Gourmet Microwave'

SAVE TRANSACTION percentchanged

Itt megjelöljük a tranzakciót, és most megvizsgáljuk, hogy mi lenne, ha a könyv árát megemelnénk.

UPDATE titles
    SET price = price * 1.1
        WHERE title = 'The Gourmet Microwave'
SELECT (price * royalty * ytd_sales) * royaltyper
    FROM titles, titleauthor
        WHERE title = 'The Gourmet Microwave'
            AND titles.title_id = titleauthor.title_id

Miután a SELECT megmutatta a kért adatokat, a változásokat visszagörgetjük a megjelölt pontig.

ROLLBACK TRANSACTION percentchanged
COMMIT TRANSACTION

Ha a ROLLBACK-kel csak savepoint-ig görgetünk vissza, a @@TRANCOUNT értéke nem változik. Úgyszintén a zárolt erőforrások sem szabadulnak fel a tranzakció végéig.

Autocommit tranzakciók

Minden TSQL utasítás önmagában tranzakcionálisan hajtódik végre, vagyis commit-álódik vagy érvényét veszti befejeztekor. Ha sikeresen végrehajtódott, commit-álódik; ha hiba merül fel, automatikusan ROLLBACK hajtódik végre. Az SQL Servernek ez az alapértelmezett üzemmódja, és egészen egy BEGIN TRAN utasításig így működik. (A TRANSACTION-t TRAN-nak lehet rövidíteni.) Amikor a megkezdett tranzakciónak vége van, akár COMMIT-tal, akár ROLLBACK-kel, ismét autocommit üzemmódba kerül.

Implicit tranzakciók

Ezt az üzemmódot ki és be lehet kapcsolni a SET IMPLICIT_TRANSACTIONS OFF és a SET IMPLICIT_TRANSACTIONS ON parancsokkal. Ebben az üzemmódban az SQL Server bizonyos utasítások után automatikusan tranzakciót indít, nem kell tehát külön beírnunk a BEGIN TRAN utasítást, de a tranzakció lezárásáról nekünk kell gondoskodnunk a ROLLBACK vagy COMMIT utasításokkal.
Ilyen tranzakciót generáló utasítások a következők:

ALTER TABLE
CREATE
DELETE
DROP
FETCH
GRANT
INSERT
OPEN
REVOKE
SELECT
TRUNCATE TABLE
UPDATE

Példa:

USE pubs
CREATE TABLE ImplicitTran (
      Cola int     PRIMARY KEY,
      Colb char(3) NOT NULL)
SET IMPLICIT_TRANSACTIONS ON

Az első tranzakció automatikusan indul a következő INSERT utasítás hatására, amit majd a COMMIT zár.

INSERT INTO ImplicitTran VALUES (1, 'aaa')
INSERT INTO ImplicitTran VALUES (2, 'bbb')
COMMIT TRANSACTION

A második implicit tranzakciót a SELECT indítja.

SELECT COUNT(*) FROM ImplicitTran
INSERT INTO ImplicitTran VALUES (3, 'ccc')
SELECT * FROM ImplicitTran
COMMIT TRANSACTION
SET IMPLICIT_TRANSACTIONS OFF

Elosztott tranzakciók

Ha egy tranzakció egyszerre több adatbázist is érint, melyek különböző erőforrás-szervereken vannak, akkor elosztott tranzakcióról beszélünk. Ezeket a tranzakciókat az SQL Serverben a Microsoft Distributed Transaction Coordinator (MS DTC) menedzseli. Ha a tranzakció csak egyetlen szerveren fut, akkor is érinthet egyszerre két adatbázist. Ez is elosztott tranzakció, de az SQL Server ezt saját maga vezényli le.

Az elosztott tranzakciók érvényesítése két fázisban történik. Az első fázisban a tranzakcióban érintett adatbázisok külön-külön érvényesítik a változtatásokat. Ha ez mindenkinél sikeresen lezajlott, csak akkor válik az egész tranzakció is érvényessé. Ez az ellenőrzés képezi a második fázist. Ha az első fázis során az egyik adatbázis nem tud érvényesíteni, minden adatbázisban érvényteleníteni kell a változtatásokat. Ez a metódus a kétfázisú commit, vagy angolul two-phase commit, 2pc.

A két fázis neve: prepare phase és commit phase. Az első fázisban tehát a tranzakció kezelő megkapja a commit kérelmet, és ekkor egy prepare parancsot küld az összes erőforrás szervernek, akik érintettek ebben a tranzakcióban. Az egyes erőforrás szerverek ekkor mindent megtesznek, hogy a tranzakciót lezárhassák, a vonatkozó naplóbejegyzéseket lemezre mentik, majd jelentik a tranzakció-kezelőnek, hogy ezt a fázist sikeresen végrehajtották vagy sem.

Ha a tranzakció kezelő mindenkitől sikeres visszajelzést kapott, mindenkinek kiküldi a commit parancsot. Minden erőforrás szerver véglegesíti a változtatásokat, és erről is jelentést küld a tranzakció-kezelőnek. Ha mindenki sikert jelent, a tranzakció-kezelő csak ekkor küld üzenetet annak az alkalmazásnak, amelyik meghívta ezt a tranzakciót. Ha bármelyik erőforrás szerver az első menet végén sikertelenségről üzen, a tranzakció-kezelő mindenkinek kiküldi a rollback utasítást és erről értesíti az alkalmazást.

Az elosztott tranzakciókkal kapcsolatban a programozónak nem sok tennivalója akad, az SQL Server automatikusan elrendez mindent. A szokásos módon elindítjuk a tranzakciót, meghívjuk azokat az utasításokat, amiket szeretnénk és a szokásos módon, COMMIT-tal vagy ROLLBACK-kel zárjuk a tranzakciót. Az SQL Server ekkor végrehajtja a műveleteket, majd az MS DTC segítségével elrendezi a COMMIT-ot vagy éppen a ROLLBACK-et.

Néhány jó tanács

A legfontosabb tanács: legyenek a tranzakcióink olyan rövidek, amilyenek csak tudnak lenni. Vagyis érvényesítsük azokat olyan hamar, amilyen hamar csak lehet. Amikor egy tranzakció elindul, az adatbázis-kezelő rendszer lefoglal valamennyi erőforrást, hiszen temérdek dolga van: a korábban már említett ACID jellemzőket ki kell elégíteni. Ha adatot módosítunk, a sort zárolnunk kell, hogy a tranzakció végéig más tranzakciók ne érhessék el. Az izolációs szinttől függően a SELECT utasítások is maguk után vonhatják sorok zárolását. Minél több felhasználó van a rendszerben, annál inkább fontos a tranzakciókat rövid ideig futtatni, hogy a lehető legkevesebbet kelljen várakozni a zárolt adatok miatt.

Ne programozzunk felhasználói bevitelt tranzakció közben. Ezt tegyük meg mindig a tranzakció előtt. Ha tranzakció közben derül ki, hogy szükség van még adatra a felhasználótól., mindig ROLLBACK-eljünk, rendezzük le a bevitelt, és indítsunk egy új tranzakciót. Ellenkező esetben előfordulhat, hogy a felhasználó csak sokára válaszol, és közben zárolva tartjuk a táblákat akár órákig.

Egy tranzakció során mindig a lehető legkevesebb adattal dolgozzunk!

Tranzakciók a Delphiben

Ahogy az előző fejezetben is láthattuk, tranzakciót a Delphiből is indíthatunk. A TDatabase objektum 3 metódust tartalmaz, amit használhatunk: StartTransaction, Commit, Rollback. A StartTransaction metódussal indíthatunk új tranzakciót. A Delphi egyszerre csak egy tranzakciót tud kezelni, az InTransaction boolean property jelzi, hogy éppen fut-e tranzakció vagy sem. Ha tranzakciót indítunk, de az InTransaction már egy korábban indított tranzakció létét jelzi, kivétel keletkezik. A tranzakción belüli összes módosítást a szerver addig tárolja, amíg azokat egy Commit nem érvényesíti vagy egy Rollback nem vonja vissza.
A példában a pubs adatbázis (Test1 alias) titleauthor tábláját módosítjuk egy tranzakción belül. Az esetleg felmerülő hibákat egy try..except utasítás kapja el.

  try
    MyDB.StartTransaction;
    with Query1, SQL do
    begin
      Clear;
      Add('UPDATE titleauthor SET royaltyper = 75 FROM
          titleauthor, titles WHERE royaltyper = 65 AND
          titleauthor.title_id = titles.title_id AND title =
          ''The Gourmet Microwave''');
      ExecSQL;
      Clear;
      Add('UPDATE titleauthor SET royaltyper = 25 FROM
          titleauthor, titles WHERE royaltyper = 35 AND 
          titleauthor.title_id = titles.title_id AND title =
          ''The Gourmet Microwave''');
      ExecSQL;
    end;
    MyDB.Commit;
  except
    On EDatabaseError do
    begin
      MyDB.RollBack;
      ShowMessage('Adatbázishiba');
    end
    else
    begin
      MyDB.RollBack;
      ShowMessage('Egyéb hiba');
    end;
  end;

A program futtatása mellett érdemes az SQL Monitorral figyelni a Delphi és az SQL Server közötti adatforgalmat.

MS SQL adatbázis kezelés Delphi-ből

21. rész