Relációs adatbázisok és a Microsoft SQL Server tranzakciókezelésének megismerése. 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.
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 és videó
- A használt adatbázis sémája
Felkészülés ellenőrzése:
- A gyakorlatra való felkészüléshez használható ezen kérdőív.
- A gyakorlaton beugróként ugyanezen kérdőívet fogjuk használni, legalább 50% elérése szükséges.
- Gyakorlatvezetői instrukció: a hallgató nyissa meg a kérdőívet és töltse ki. A válaszok megadása után az utolsó oldalon a "View results" gombra kattintva megtekinthető az eredmény és az elért pontszám. A hallgató ezen az oldalon álljon meg és mutassa meg eredményét a gyakorlatvezetőnek.
2021 tavasszal, amennyiben a gyakorlat (még/már/továbbra is) távoktatásban kerül terítékre, az alábbit kérjük.
- Otthoni teljesítés esetén nincs beugró. A fenti linken található kérdőíven ennek ellenére is ellenőrizd a tudásod.
- A feladatokat oldd meg otthon. A feladatok megoldását megtalálod az anyagban, de a feladatod nem ezek kérdés nélküli átmásolása! Kíséreld meg önállóan megoldani a feladatot, és ha nem sikerül, meríthetsz ötletet a minta megoldásból.
- Dokumentáld a gyakorlatot és a dokumentációt ("jegyzőkönyvet") add be. A jegyzőkönyvben azt várjuk, hogy bizonyítod a feladatok elvégzését. Minden feladatnál készíts egy képernyőképet a feladat eredményéről. Ne a kódot fényképezd le, hanem keress módot arra, hogy bemutasd, működik a megoldásod. Például, mutasd meg, hogy a kódodat hogyan hívod meg, és utána mi az eredménye - bekerül egy rekord a táblába, vagy hasonló. Minden feladatról elég egyetlen kép, ha az egyben tudja az eredményt mutatni. A gyakorlat akkor minősül teljesítettnek, ha a feladatok 50%-át elvégezted és dokumentáltad.
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!
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.
-
Kapcsolódjon a Microsoft SQL Serverhez SQL Server Management Studio Segítségével. Indítsa el az alkalmazást, és az alábbi adatokkal kapcsolódjon.
- Server name:
(localdb)\mssqllocaldbvagy.\sqlexpress - Authentication:
Windows authentication
- Server name:
-
Hozzon létre egy új adatbázist (ha még nem létezik)! Az adatbázis neve legyen a Neptun kódja: Object Explorer-ben Databases-en jobb kattintás, és Create Database.
-
Hozza létre a minta adatbázist az generáló script lefuttatásával. Nyisson egy új Query ablakot, másolja be a script tartalmát, és futtassa le. Ügyeljen az eszköztáron levő legördülő menüben a megfelelő adatbázis kiválasztására.
-
Ellenőrizze, hogy létrejöttek-e a táblák. Ha a Tables mappa ki volt már nyitva, akkor frissíteni kell.
.
Nyisson 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álasszuk a New Vertical Tab Group opciót:
Használja 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.
-
T1 tranzakció
-- Listázzuk ki a megrendelés és a hozzá tartozó tételek státuszát select S1.nev, t.nev,s2.nev from megrendeles m, megrendelestetel mt, statusz s1, statusz s2, termek t where m.id=mt.megrendelesid and m.id=4 and m.statuszid=s1.ID and mt.statuszid=s2.ID and t.id=mt.termekid
-
T2 tranzakció
-- Állítsuk át a megrendelés állapotát update megrendeles set statuszid=4 where id=4
-
T1 tranzakció: első lépésben kiadott parancs megismételve
-
T2 tranzakció
-- Állítsuk át a megrendeléshez tartozó tételek állapotát update megrendelestetel set statuszid=4 where megrendelesid=4
-
T1 tranzakció: első lépésben kiadott parancs megismételve
Magyarázat
Kezdetben minden tétel csomagolva státuszban van, ami így konzisztens (nem lehet egy darab tétel postázva, ha a megrendelés nincs postázva). Viszont, amint változatunk 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.
Ismételje 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 egycommitutasí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).
Magyarázat
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
selectutasí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ó.
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.
-
T1 tranzakció
-- Listázzuk ki a megrendelés és a hozzá tartozó tételek státuszát select S1.nev, t.nev,s2.nev from megrendeles m, megrendelestetel mt, statusz s1, statusz s2, termek t where m.id=mt.megrendelesid and m.id=4 and m.statuszid=s1.ID and mt.statuszid=s2.ID and t.id=mt.termekid
-
T2 tranzakció
-- Új tranzakciót kezdünk begin tran -- Állítsuk át a megrendelés állapotát update megrendeles set statuszid=4 where id=4
-
T1 tranzakció: első lépésben kiadott parancs megismételve
-
T2 tranzakció
-- Szakítsuk meg a tranzakciót rollback
Magyarázat
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árrollbackmiatt, 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
rollbackmiatt az a tranzakció érvénytelen adattal dolgozna tovább.
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.
-
T1 tranzakció
set transaction isolation level serializable begin tran -- Ellenőrizzük, hogy mennyi van raktáron egy termékből select * from termek where id = 2
-
T2 tranzakció
set transaction isolation level serializable begin tran select * from termek where id = 2
-
T1 tranzakció
-- Ellenőrizzük, hogy hány, még fel nem dolgozott megrendelés van erre a termékre select sum (mennyiseg) from megrendelestetel where termekid=2 and statuszid=1
-
T2 tranzakció
select sum (mennyiseg) from megrendelestetel where termekid=2 and statuszid=1
-
T1 tranzakció
-- Mivel teljesíthető a megrendelés, rögzítsük insert into megrendelestetel (MegrendelesID,TermekID,Mennyiseg,StatuszID) values(2,2,3,1)
-
T2 tranzakció
insert into megrendelestetel (MegrendelesID,TermekID,Mennyiseg,StatuszID) values(3,2,3,1)
-
T1 tranzakció
commit -
T2 tranzakció
commit
Magyarázat
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 summiatt, é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 azinsert, 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
rollbackutasítást az esetleg ottmaradt tranzakciók leállításához. - Ahol utasításban
idvagytermekidszerepel, ott egyik tranzakcióban a 2-es, másikban a 3-as terméket használjuk.
Magyarázat
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 sumaz egész táblát zárolja, mivel nem tudja megkülönböztetnitermekidszerint 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 tudja.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.
Gondoljuk 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?
Magyarázat
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.
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 dolgozzon ki megoldást, amely csak az azonos termékekre történő párhuzamos adatrögzítéseket akadályozza meg. Természetesen feltételezheti 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 tablanev with(XLOCK)
...Magyarázat
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: 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.
-
T1 tranzakció
set transaction isolation level ***read committed*** begin tran select * from termek ***with (xlock)*** where id = 2
-
T2 tranzakció
set transaction isolation level ***read committed*** begin tran select * from termek ***with (xlock)*** where id = ***3***
-
T1 tranzakció
select sum (mennyiseg) from megrendelestetel where termekid=2 and statuszid=1
-
T2 tranzakció
select sum (mennyiseg) from megrendelestetel where termekid=***3*** and statuszid=1
-
T1 tranzakció
insert into megrendelestetel (MegrendelesID,TermekID,Mennyiseg,StatuszID) values(2,2,3,1)
-
T2 tranzakció
insert into megrendelestetel (MegrendelesID,TermekID,Mennyiseg,StatuszID) values(3,***3***,3,1)
-
T1 tranzakció
commit -
T2 tranzakció
commit
A sor szintű zárolás mellett lehetőségünk van tábla szinten is zárolni:
select *
from tablanev with(TABLOCKX)
...Magyarázat
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.
Az itt található oktatási segédanyagok a BMEVIAUBB04 tárgy hallgatóinak készültek. Az anyagok oly módú felhasználása, amely a tárgy oktatásához nem szorosan kapcsolódik, csak a szerző(k) és a forrás megjelölésével történhet.
Az anyagok a tárgy keretében oktatott kontextusban értelmezhetőek. Az anyagokért egyéb felhasználás esetén a szerző(k) felelősséget nem vállalnak.