Oracle Business Intelligence

Az SQL Server és az Oracle adatbáziskezelő tranzakció kezelése különbözik egymástól.

Arról már írtam egy korábbi bejegyzésben, hogy egy tranzakció SQL Server alatt (alapértelmezetten) explicit, azaz begin transaction … commit/rollback transaction utasítások jelölik az elejét illetve a végét, míg Oracle alatt az első végrehajtható SQL utasításkor kezdődik, és commit/rollback (vagy hiba, disconnect) hatására ér véget. Viszont nem ez az egyedüli különbség, a két rendszer a zárakat is eltérően kezeli.

Az SQL Server a SELECT utasítás hatására egy shared zárat helyez a táblára, míg az Oracle semmilyet se. Gondoljuk végig, hogy ez mit jelent: egy SELECT tehát csak akkor tud lefutni SQL Server alatt, ha rá tud tenni egy shared zárat, azaz a táblán maximum shared zár van jelenleg (nincs exclusive lock). Ennek következtében a SELECT és az UPDATE utasítások fizikailag szerializáltan hajtódnak végre.

Ezzel szemben Oracle alatt egy SELECT utasítást nem blokkolja semmi, és egy SELECT utasítás nem blokkol senkit (FOR UPDATE klauzula nélkül).

Képzeljünk el egy olyan T-SQL tárolt eljárást, ami kiolvassa egy számla egyenlegét egy változóba, elszöszmötöl egy darabig, majd – ha van elég pénz az egyenlegen – csökkenti azt. Ennek az eljárásnak a kódja valahogy így nézhet ki:

declare @balance money
begin transaction
 select @balance = balance from tran_balance where userid=20
 -- some very serious calculation..
 if @balance > 1500
     update tran_balance set balance=balance-1500 where userid=20
commit transaction

Mi itt a hiba? Ez a kód bizonyos időzítés mellett helyesen működik SQL Server alatt, Oracle alatt viszont (alapértelmezett izolációs szinten) szinte sehogy se.
Tegyük fel, hogy az alábbi ábrán látható módon indul el két tranzakció.

SQL Server alatt az alábbi lépések fognak történni:

• A SELECT kirak egy shared lock-ot a táblára
• Az UPDATE kirak egy exclusive lock-ot a táblára
• Indulna a 2. tranzakció, de a SELECT nem tud shared lock-ot kirakni, mert a másik tranzakció exclusive lock-ja még érvényben van
• Az első tranzakció lefut, végrehajtódik a COMMIT utasítás
• Elindul a 2. tranzakcióban levő SELECT utasítás is, és az első tranzakció által véglegesített értéket olvassa ki.

Ha az időzítésen variálunk egy picit, pl: feljebb „toljuk” a 2. tranzakció SELECT-jét az első tranzakció SELECT és UPDATE utasítása közé, akkor SQL Server-en is „nem várt” működést tapasztalhatunk (akkor nem várt, ha nem tudjuk, hogy működik :)):

• lefut az első SELECT
• lefut a második tranzakció SELECT-je is, a shared lock miatt (a shared lock-ok egymással kompatibilisek)
• lefut az első UPDATE
• a második UPDATE blokkolódik, egészen addig, amíg az első tranzakció nem fejeződik be.

Mi a helyzet Oracle alatt? A SELECT utasítás mindkét (bármilyen) esetben le fog futni, kiolvasva az utolsó elcommitált értéket. A fenti példakód esetében tehát a konkurens tranzakciók lehet, hogy úgy terhelik be az egyenleget, hogy már nincs is rajta fedezet. Azaz, ha „ész nélkül” (automatikusan) fordítjuk át T-SQL kódjainkat PL/SQL-re, akkor a fenti „kicsit” rossz kódból nagyon rossz kódot kapunk.

Ebben a példában két lehetőség van a javításra, Oracle oldalon. Az egyik a SERIALIZABLE izolációs szint használata: ezen a szinten levő tranzakciók ha egy olyan táblát szeretnének módosítani, amit egy másik – még el nem commitált – tranzakció már módosított, akkor hibát kapunk:


A másik megoldás a LOCK TABLE használata: még mielőtt bármit is olvasnánk a táblából, rárakunk egy zárat. Ezzel elérhetjük az SQL Server-éhez hasonló működést, azzal a különbséggel, hogy nem a SELECT utasítás fog blokkolódni, hanem az előtte lévő LOCK TABLE.

Tranzakciókezelés

Microsoft SQL Server 2000 alatt három féle módon lehet tranzakciót kezdeni:

Explicit tranzakciók esetén a programozó határozza meg a tranzakció elejét és végét is, Transact-SQL scriptek esetén BEGIN TRANSACTION / COMMIT / ROLLBACK utasításokkal, ADO.NET esetén pedig az SqlConnection objektum BeginTransaction() illetve Commit() / Rollback() metódusaival.

A BEGIN TRANSACTION utasítás 1-el növeli a @@TRANCOUNT változó értékét (alapesetben a változó 0), a COMMIT 1-el csökkenti, míg a ROLLBACK 0-ra állítja. Ez azért van így, mert SQL Server alatt úgy lehet tranzakciókat egymásba ágyazni, hogy a beágyazott tranzakciók esetében az adatbázis a COMMIT utasításokat figyelmen kívül hagyja, csak a legkülső COMMIT után lesznek a módosítások véglegesítve. A ROLLBACK utasítás viszont minden aktív tranzakciót visszagörget.

Ezzel szemben Oracle adatbáziskezelő alatt egy tranzakció az első végrehajtható SQL utasítással kezdődik, és vagy akkor ér véget, amikor explicit módon kiadunk egy COMMIT illetve ROLLBACK utasítást, vagy implicit módon, pl.: egy DDL utasítás végrehajtásával, vagy az adatbázisból történő kilépéskor (disconnect).

Léteznek még úgynevezett autonóm tranzakciók (autonomous transactions), amik teljesen függetlenek a "hívó" tranzakciótól. Például, ha egy tárolt eljárás az AUTONOMOUS_TRANSACTION direktívával lett lefordítva, akkor, amikor meghívódik egy külső tranzakcióból, nem látja annak a még nem véglegesített adatait, illetve tőle függetlenül lesz véglegesítve vagy visszagörgetve. Ez például naplózást megvalósító eljárások esetén lehet hasznos.

Az Oracle Migration Workbench a BEGIN TRANSACTION utasításokat a SET TRANSACTION READ WRITE utasítássá fordítja át, ami az aktuális tranzakciót úgy állítja át, hogy utasítás szintű konzisztenciát biztosítson (statement-level read consistency).

Ez annyiban nem szerencsés, hogy míg a BEGIN TRANSACTION tetszőleges helyen szerepelhet egy T-SQL blokkban, addig Oracle alatt a tranzakció tulajdonságait csak az első utasításban lehet beállítani. Azaz például, ha egy INSERT szerepel a BEGIN TRANSACTION előtt (az explicit tranzakción kívül, alapértelmezetten autocommit módban), akkor ezt a Migration Workbench segítségével PL/SQL-re fordítva valami ilyesmit kapunk:

CREATE OR REPLACE PROCEDURE sp_something
AS
BEGIN
INSERT INTO proba
VALUES ( 1, SYSDATE );

SET TRANSACTION READ WRITE;

INSERT INTO proba
VALUES ( 2, SYSDATE );

ROLLBACK;

END;

ami - habár szintaktikailag helyes, azaz le fog fordulni - futáskor hibát fog adni:

ORA-01453: SET TRANSACTION must be first statement of transaction

Gond van még a @@TRANCOUNT változóval is, mert a dokumentáció szerint hiába támogatja a Migration Workbench a globális változók migrációját, nekem összesen annyit sikerült elérnem, hogy a @@TRANCOUNT-ot v_transcount-ra cserélte. A SwisSQL (a Migration Workbench konkurrenciája) ezt a kérdést úgy oldja meg, hogy egy package-ben deklarál egy változót, amit minden egyes tranzakciókezelő utasításkor növel/csökkent, azaz BEGIN TRANSACTION helyett növeli eggyel, COMMIT után csökkenti eggyel, és ROLLBACK után pedig 0-ra állítja.