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.

Idén először vettem részt a Magyarországi Oracle Felhasználók Konferenciáján, melyet április 6-9. között a siófoki Hotel Azúrban tartottak.

Én szerda reggel érkeztem meg, kiváncsian vártam Kardkovács Zsolt és Éberhardt Péter előadását a Közel valós idejű, adaptív műsorajánló digitális televíziózáshoz címmel, amikor is a terem előtt hallottam, hogy sajnos elmarad. Helyettük Sárecz Lajos (akinek a jóvoltából vehettem részt a konferencián) tartott előadást Real Application Testing témában.

A szerdai nap további részét főleg az Üzleti intelligencia és az Adatbiztonság szekciók előadásain töltöttem, illetve befurakodtam Czinkóczki László - szokásosan - teltház előtt tartott SQL érdekességek, újdonságok előadására.

Az előadásokat egy számomra rendkívül érdekes panelbeszélgetés zárta szintén adatbiztonság témában, ahol a meghívott vendégek Kirner Attila (PSZÁF), Jakab Péter (MKB Zrt.), Dr. Suba Ferenc (Magyar Kormány Informatikai Biztonsági Incidenskezelő Központ), Antal Lajos (PwC Kft.), Bártfai Attila (kancellár.hu Zrt.), Keleti Arthur (KFKI Zrt.) voltak.

A beszélgetésről talán mindent elmond az, hogy az első kérdés megválaszolása, kivesézése 50 percig tartott.

Az esti vacsorát látványos zenés programok, sör, beszélgetések színesítették, majd egy csocsó-"bajnokság" :)

A csütörtöki nap számomra továbbra is BI + SQL/PLSQL témában telt, az adatbiztonság helyét Tóth Balázs kiváló Change Management a 11g Oracle adatbázisban előadása vette át.

Én hasznosnak éreztem a konferenciát, csak néha tényleg a bőség zavarával kűzködtem: túl sok jó előadás volt :)

Ez a két hibaüzenet kissé elkeserített, ugyanakkor megmagyarázza számomra, hogy az SQL Developer Migration Workbench miért több kurzor változó segítségével oldja meg az eredményhalmazok visszaadását a tárolt eljárásokból, és miért nem egy kurzor változókat tartalmazó tömb segítségével.

A válasz egyszerű: mert nem lehet kurzor változókat tartalmazó tömböt készíteni:

PLS-00990: Index Tables of Cursor Variables are disallowed
PLS-00989: Cursor Variable in record, object, or collection is not supported by this release

A "this release" pedig az Oracle Database 11g Enterprise Edition Release 11.1.0.6.0. Pedig milyen jó ötletnek tűnt.. :)