Tranzakciókezelés

A gyakorlat célja a relációs adatbázisok és a Microsoft SQL Server tranzakciókezelésének megismerése, a serializable izolációs szint gyakorlati használhatóságának korlátai, egyedi adat egymásra hatások szabályozása read committed izolációs szinten.

Előfeltételek

A labor elvégzéséhez szükséges eszközök:

• Microsoft SQL Server (LocalDB vagy Express edition)
• SQL Server Management Studio
• Adatbázis létrehozó script: mssql.sql

Amit érdemes átnézned:

• Tranzakciók tulajdonságai, izolációs szintek
• Microsoft SQL Server használata segédlet

Gyakorlat menete

A gyakorlat vezetett. A szoftverekkel történő megismerkedés után a gyakorlatvezető instrukciói alapján együtt kerülnek elvégzésre a feladatok. A tapasztalt viselkedésekre magyarázatot keresünk, és azt a csoport együtt megbeszéli.

A viselkedések magyarázatát röviden összefoglalja az útmutató is. Előbb azonban gondolkodjunk el magunk a kérdéseken!

Feladat 1: Adatbázis létrehozása MS SQL Serveren

Első lépésként szükségünk lesz egy adatbázisra. Az adatbázis tipikusan egy központi kiszolgálón helyezkedik el, de fejlesztés közben sokszor a saját gépünkön fut. Mi ez utóbbi opciót választjuk.

1. Kapcsolódj a Microsoft SQL Serverhez SQL Server Management Studio Segítségével. Indítsd el az alkalmazást, és az alábbi adatokkal kapcsolódj.

   • Server name: (localdb)\mssqllocaldb
   • Authentication: Windows authentication

2. Hozz létre egy új adatbázist (ha még nem létezik), az adatbázis neve legyen a Neptun kódod: Object Explorer-ben Databases-en jobb kattintás, és Create Database.

3. Hozd létre a minta adatbázist a generáló script lefuttatásával. Nyiss egy új Query ablakot, másold be a script tartalmát, és futtasd le. Ügyelj az eszköztáron levő legördülő menüben a megfelelő adatbázis kiválasztására.

   

4. Ellenőrizd, hogy létrejöttek-e a táblák. Ha a Tables mappa ki volt már nyitva, akkor frissíteni kell.

   .

5. Bár létrejöttek az új táblák, a Management Studio sokszor mégis pirosan aláhúzza amikor gépelünk. Ennek elkerülése érdekében az Edit > IntelliSense > Refresh Local Cache menüpontot kiválasztva vagy a Ctrl+Shift+R billentyűkombinációval az IntelliSense gyorsítótárát frissítsd.

   .

Feladat 2: Párhuzamos tranzakciók

Nyiss két Query ablakot párhuzamos tranzakciók szimulálásához a New Query gomb kétszeri megnyomásával. Érdemes az ablakokat egymás mellé tenni: a Query fül fejlécére jobb egérrel kattintva válaszd a New Vertical Tab Group opciót:

Használd az alábbi ütemezést a parancsok végrehajtására. A T1 tranzakció a 4-es megrendelés státuszát ellenőrzi, míg a T2 tranzakció megváltoztatja a státuszt csomagolváról szállítás alattira.

1. T1 tranzakció

   -- Listázzuk ki a megrendelés és a hozzá tartozó tételek státuszát
   SELECT s1.Name, p.Name, s2.Name
   FROM [Order] o, OrderItem oi, Status s1, Status s2, Product p
   WHERE o.Id = oi.OrderID
     AND o.ID = 4
     AND o.StatusID = s1.ID
     AND oi.StatusID = s2.ID
     AND p.ID = oi.ProductID
   

   [Order]

   Az [Order] tábla nevében azért kell a szögletes zárójel, hogy jelezzük, tábla névről van szó, és nem az order by parancs kezdetéről.

2. T2 tranzakció

   -- Állítsuk át a megrendelés állapotát
   UPDATE [Order]
   SET StatusID = 4
   WHERE ID = 4
   

3. T1 tranzakció: első lépésben kiadott parancs megismételve

4. T2 tranzakció

   -- Állítsuk át a megrendeléshez tartozó tételek állapotát
   UPDATE OrderItem
   SET StatusID = 4
   WHERE OrderID = 4
   

5. T1 tranzakció: első lépésben kiadott parancs megismételve

Mit tapasztaltál? Miért?

Kezdetben minden tétel "Packaged" státuszban van, ami így konzisztens (nem lehet egy darab tétel "in transit", azaz postázás alatt, ha a megrendelés nincs). Viszont, amint változtattunk a megrendelés állapotán, a csomag státusza ellentmondásosnak látszik a tételek státuszával. Az adatbázis nem inkonzisztens, mert a belső szabályai (integritási kritériumai) alapján nincs probléma. De üzleti értelemben ellentmondásos a tartalom.

Az SQL Server alapértelmezésben auto commit üzemmódban fut, azaz egy utasítás az egy tranzakció, amit automatikusan lezár. Tehát a probléma, hogy a módosításaink nem egy tranzakciót képeznek.

A helyes viselkedéshez össze kellene fogni a két adatmódosító utasítást egy tranzakcióba.

Feladat 3: Tranzakció indítása, read committed izolációs szint

Ismételd meg az előző feladatot úgy, hogy a két adatmódosítás egy tranzakciót képezzen:

• A T2 tranzakció kezdjen egy begin tran, és végződjön egy commit utasítással.
• Az adatmódosításnál a státuszt ezúttal 3-asra állítsuk (hogy érdemi változás legyen az adatokban).

Mit tapasztaltál? Miért?

Amint elkezdtük a státusz módosítását T2-ben, a lekérdező T1-es utasítás várni fog. Addig vár, amíg az adatmódosító tranzakció be nem fejeződik. Ez azért van, mert a select utasítás olvasási zárat akar elhelyezni, de másik tranzakció éppen módosítja az adott rekordot, így kizáró zárat helyezett el rajta.

Jegyezzük meg, hogy az alap izolációs szint, a read committed ezen a platformon azt jelenti, hogy módosítás alatt levő adat nem olvasható. Ez egy implementációs kérdés, az SQL szabvány ezt nem rögzíti. Más adatbázis platform viselkedhet máshogy is (pl. az Oracle Server biztosítja, hogy a rekordok commitált képe mindenképpen olvasható marad). Más izolációs szinten az MSSQL szerver is máshogy viselkedik, a snapshot izolációs szint használata esetén a módosítás megkezdése előtti változat olvasható.

Zárak ellenőrzése SQL lekérdezéssel

A zárak állapotát egy harmadik query ablakban is ellenőrizhetjük SQL lekérdezésekkel, amíg a T2 tranzakció fut (azaz még nem került sor commit vagy rollback végrehajtására).

Nyissunk egy harmadik query ablakot (New Query gomb), és futtassuk le az alábbi lekérdezést, miközben a T2 tranzakció aktív (a BEGIN TRAN és az első UPDATE után, de még a COMMIT előtt):

-- Zárak lekérdezése
SELECT 
    dtl.resource_type,
    DB_NAME(dtl.resource_database_id) AS database_name,
    OBJECT_NAME(P.object_id) AS object_name,
    dtl.request_mode,
    dtl.request_type,
    dtl.request_status,
    dtl.request_session_id
FROM sys.dm_tran_locks dtl
LEFT JOIN sys.partitions P ON dtl.resource_associated_entity_id = P.hobt_id
WHERE dtl.resource_database_id = DB_ID()
ORDER BY dtl.request_session_id

Ez a lekérdezés megjeleníti az aktuális adatbázisban elhelyezett zárakat. A resource_type oszlop mutatja, hogy milyen típusú erőforráson van zár (pl. OBJECT, PAGE, KEY), a request_mode oszlop pedig a zár típusát (pl. S - shared/olvasási, X - exclusive/kizárólagos). A request_session_id segítségével azonosíthatjuk, hogy melyik munkamenet (session) tartja a zárat - ezt a Query ablak címsorában is megtaláljuk.

További hasznos információt kaphatunk a blokkolt tranzakciókról is:

-- Blokkolt session-ök lekérdezése
SELECT 
    blocking_session_id AS 'Blokkoló Session ID',
    session_id AS 'Blokkolt Session ID',
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

Ez a lekérdezés megmutatja, ha van olyan session, amelyik vár egy másik session által tartott zárra.

Feladat 4: Tranzakció megszakítása (rollback) read committed izolációs szinten

Kezdjük el lefuttatni az előző parancs sorozatot, a tranzakcióval együtt, de a módosító tranzakciót szakítsuk meg a közepén.

1. T1 tranzakció

   -- Listázzuk ki a megrendelés és a hozzá tartozó tételek státuszát
   SELECT s1.Name, p.Name, s2.Name
   FROM [Order] o, OrderItem oi, Status s1, Status s2, Product p
   WHERE o.Id = oi.OrderID
     AND o.ID = 4
     AND o.StatusID = s1.ID
     AND oi.StatusID = s2.ID
     AND p.ID = oi.ProductID
   

2. T2 tranzakció

   -- Új tranzakciót kezdünk
   BEGIN TRAN
   
   -- Állítsuk át a megrendelés állapotát
   UPDATE [Order]
   SET StatusID = 4
   WHERE ID = 4
   

3. T1 tranzakció: első lépésben kiadott parancs megismételve

4. T2 tranzakció

   -- Szakítsuk meg a tranzakciót
   ROLLBACK
   

Mit tapasztaltál? Miért?

Az előzőekben tapasztaltakhoz hasonlóan a módosítás megkezdte után az olvasó utasítás várakozásra kényszerült. Amikor megszakítottuk a tranzakciót, akkor érkezett meg az eredmény. Mivel read committed izolációs szinten vagyunk, nem látjuk a módosítás alatt levő tartalmat. Amint a módosító tranzakció befejeződik, akár sikeres commit, akár rollback miatt, elérhetővé válnak a rekordok.

Vegyük észre, hogy pont elkerültük a piszkos olvasás problémáját. Ha a módosítás futása közben megjelent volna a félkész eredmény, a rollback miatt az a tranzakció érvénytelen adattal dolgozna tovább.

Feladat 5: Read committed snapshot izolációs szint

A feladat megkezdése előtt először is szakítsuk meg a félbemaradt tranzakciókat. Mindkét ablakban adjunk ki pár rollback utasítást az esetleg ottmaradt tranzakciók leállításához.

Az SQL Server támogat egy speciális read committed snapshot izolációs szintet is, amely a read committed izolációs szint egy változata. Ebben az üzemmódban a módosítás alatt levő rekordok olvasásakor nem várunk, hanem az utolsó commitált verzió kerül visszaadásra. Ezt a viselkedést az adatbázis szintjén kell bekapcsolni.

Először kapcsoljuk be a read committed snapshot üzemmódot az adatbázisunkon. Ehhez egy új query ablakban futtassuk le az alábbi parancsot (a NEPTUN helyére a saját Neptun kódunkat írva):

ALTER DATABASE [NEPTUN]
SET READ_COMMITTED_SNAPSHOT ON

Fontos

Ez a parancs csak akkor fut le sikeresen, ha nincs más aktív kapcsolat az adatbázishoz. Zárd be az összes többi query ablakot, vagy szakítsd meg az esetleges aktív tranzakciókat, mielőtt lefuttatod!

Most ismételjük meg a 4. feladat lépéseit.

Mit tapasztaltál? Miért különbözik a viselkedés a 4. feladatétól?

Most a T1 tranzakció nem várakozik, hanem azonnal megkapja az eredményt, még akkor is, ha a T2 tranzakció közben módosítja az adatokat! Ez azért lehetséges, mert read committed snapshot módban a rendszer az utolsó commitált verziót adja vissza, nem kell megvárni a módosító tranzakció befejezését.

Ez jelentős teljesítménybeli előnyt jelent, mivel az olvasó tranzakciók nem blokkolódnak az író tranzakciók által. Azonban fontos megérteni, hogy az olvasott adatok egy korábbi állapotot tükröznek, amely időközben már megváltozott lehet.

A read committed snapshot üzemmód különösen hasznos olyan alkalmazásokban, ahol sok olvasás történik, és nem elfogadható, hogy az olvasások blokkolódjanak a módosítások miatt. Ez az Oracle adatbázisok alapértelmezett viselkedése is.

A gyakorlat végén kapcsoljuk vissza az eredeti beállítást (opcionális):

ALTER DATABASE [NEPTUN]
SET READ_COMMITTED_SNAPSHOT OFF

Feladat 6: Megrendelés rögzítése serializable izolációs szinten

A feladat megkezdése előtt először is szakítsuk meg a félbemaradt tranzakciókat. Mindkét ablakban adjunk ki pár rollback utasítást az esetleg ottmaradt tranzakciók leállításához.

Legyen két párhuzamos tranzakciónk, melyek megrendelést rögzítenek. Egy termékre nem vehetünk fel több megrendelést, mint ami a raktárkészlet. Azért, hogy a tranzakciók egymásra hatását elkülönítsük, kapcsoljunk át serializable izolációs szintre.

1. T1 tranzakció

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRAN
   
   -- Ellenőrizzük, hogy mennyi van raktáron egy termékből
   SELECT *
   FROM Product
   WHERE ID = 2
   

2. T2 tranzakció

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRAN
   
   SELECT *
   FROM Product
   WHERE ID = 2
   

3. T1 tranzakció

   -- Ellenőrizzük, hogy hány, még fel nem dolgozott megrendelés van erre a termékre
   SELECT SUM(Amount)
   FROM OrderItem
   WHERE ProductID = 2
     AND StatusID = 1
   

4. T2 tranzakció

   SELECT SUM(Amount)
   FROM OrderItem
   WHERE ProductID = 2
     AND StatusID = 1
   

5. T1 tranzakció

   -- Mivel teljesíthető a megrendelés, rögzítsük
   INSERT INTO OrderItem(OrderID, ProductID, Amount, StatusID)
   VALUES(2, 2, 3, 1)
   

6. T2 tranzakció

   INSERT INTO OrderItem(OrderID, ProductID, Amount, StatusID)
   VALUES(3, 2, 3, 1)
   

7. T1 tranzakció

   COMMIT
   

8. T2 tranzakció

   COMMIT
   

Mit tapasztaltál? Miért?

Holtpont fog kialakulni, mivel a serializable izolációs szint miatt mindkét tranzakció megtiltja a megrendeléstétel tábla módosítását. A select sum miatt, és a megismételhető olvasás elvárás miatt a rekordokat olvasó zárral látja el a rendszer. Viszont így nem futhat le a másik tranzakcióban az insert, amely kizárólagos zárat igényelne. Ez mindkét tranzakció esetén azt jelenti, hogy a másik által fogott zárra vár.

A holtpont eredménye, hogy az egyik tranzakciót le fogja állítani a szerver. Ez elvárt és helyes működést eredményez, mivel megakadályozza, hogy a két tranzakció közt adat egymásrahatás legyen (így nem fordulhat elő, hogy több terméket adunk el, mint amennyi van).

Ismételjük meg a fenti műveletsort, csak a megrendelés rögzítésekor más-más termék ID-t használjunk! Ezzel azt szimuláljuk, hogy két megrendelő más-más terméket rendel meg.

• A feladat megkezdése előtt először is szakítsuk meg a félbemaradt tranzakciókat. Mindkét ablakban adjunk ki pár rollback utasítást az esetleg ottmaradt tranzakciók leállításához.
• Ahol utasításban ID vagy ProductID szerepel, ott egyik tranzakcióban a 2-es, másikban a 3-as terméket használjuk.

Mit tapasztaltál? Miért?

Ha különböző termékre történik a rögzítés, akkor is holtpont fog kialakulni. Olyan a zárolási rendszer, hogy a select sum az egész táblát zárolja, mivel nem tudja megkülönböztetni ProductID szerint a rekordokat. Ez természetes is, mivel csak az üzleti logikából adódik, hogy ha két különböző termékre történik a megrendelés rögzítése, akkor azok történhetnének egyszerre is. Az adatbázis ezt nem tudhatja.

Azaz a serializable izolációs szint túl szigorú, üzleti logikát figyelembe véve nagyobb párhuzamosítás engedhető meg. Ezért is ritkán használjuk a gyakorlatban.

Feladat 7: Megrendelés rögzítése read committed izolációs szinten

Gondold végig, az előző feladat esetén mi történne, ha a nem állítjuk át a tranzakciók izolációs szintjét? Lenne holtpont? És helyes lenne a működés?

Mit várunk? Miért?

Ha nem változtatunk izolációs szintet, akkor helytelen működés állhatna elő. Mivel a read committed izolációs szint nem biztosítja számunkra azt, hogy amíg fut a tranzakciónk, addig egy másik tranzakció berögzítsen rekordokat. Tehát lefuthatna az insert, ami miatt végeredményben több árut adnánk el, mint ami a raktárban van. Ez a nem megismételhető olvasás problémája.

Erről az oldalról nézve tehát a serializable izolációs szint nem volt feleslegesen szigorú. Tényleg megvédett minket egy problémától.

Feladat 8: Manuális zárolás

A feladat megkezdése előtt először is szakítsuk meg a félbemaradt tranzakciókat. Mindkét ablakban adjunk ki pár rollback utasítást az esetleg ottmaradt tranzakciók leállításához.

Read committed izolációs szintet használva dolgozz ki megoldást, amely csak az azonos termékekre történő párhuzamos adatrögzítéseket akadályozza meg. Természetesen feltételezheted, hogy mindegyik párhuzamos folyamat ugyanazt a programlogikát követi.

A megoldáshoz kihasználjuk, hogy lehetséges manuálisan zárakat elhelyezni. Ezek a zárak is, úgy, mint a többi zár, a tranzakció végéig élnek.

SELECT *
FROM tablename WITH(XLOCK)
...

Hova kell ezt a zárat elhelyezni? Hogyan néz ki így a megrendelés folyamata?

A megoldás kulcsa, hogy jó helyre tegyük a zárat. A kérdés, hogy mit is kellene zárolni? A válasz, hogy a terméket (Product): azt akarjuk meggátolni, hogy ugyanabból a termékből rögzíthető legyen még egy eladás. Tehát a termékre, konkrétan a termék táblában arra a sorra tesszük a zárat, ami a terméket reprezentálja.

Ennek a megoldásnak a hátránya, hogy nagyon alaposan át kell gondolnunk, hogyan és hol végezzük a zárolást.

A lépéseink tehát a következők.

1. T1 tranzakció

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRAN
   
   SELECT *
   FROM Product WITH (XLOCK)
   WHERE ID = 2
   

2. T2 tranzakció

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRAN
   
   SELECT *
   FROM Product WITH (XLOCK)
   WHERE ID = 3
   

3. T1 tranzakció

   SELECT SUM(Amount)
   FROM OrderItem
   WHERE ProductID = 2
     AND StatusID = 1
   

4. T2 tranzakció

   SELECT SUM(Amount)
   FROM OrderItem
   WHERE ProductID = 3
     AND StatusID = 1
   

5. T1 tranzakció

   INSERT INTO OrderItem(OrderID, ProductID, Amount, StatusID)
   VALUES(2, 2, 3, 1)
   

6. T2 tranzakció

   INSERT INTO OrderItem(OrderID, ProductID, Amount, StatusID)
   VALUES(3, 3, 3, 1)
   

7. T1 tranzakció

   COMMIT
   

8. T2 tranzakció

   COMMIT
   

Tábla szintű zárolás

A sor szintű zárolás mellett lehetőségünk van tábla szinten is zárolni a WITH(TABLOCKX) paranccsal:

SELECT *
FROM tablanev WITH(TABLOCKX)
...

Bár ez egyszerű megoldásnak tűnik, gondold végig, miért nem érdemes ezt használni?

Jelen esetben a tábla szintű zárat a megrendelésekre kellene tennünk, hiszen a konkurens megrendeléseket akarjuk megakadályozni. De ez ugyanazzal az eredménnyel járna, mint a serializable izolációs szint használata. Ugyan holtpont nem lenne, de a párhuzamos végrehajtást lehetetlenné tenné. Tehát a tábla szintű zárolásra ugyanaz igaz: az üzleti logikából nagyobb párhuzamosság adódik.

---

2025-11-06 Szerzők