Tranzakció izolációs szintek avagy konkurrencia MSSQL módra I.

Az egyik téma, amiröl nem tudtam beszélni a termékbejelentési konferencián, a “logikai” tuning, aminek egy kiváló útja a konkurrencia kezelésének megfelelö megválasztása. Ez a Microsoft SQL Server családban a transaction isolation level (tranzakció izolációs szint, hogy gyalázzuk a nyelvet) beállításával lehetséges. Ezzel a módszerrel azt határozzuk meg, hogy hogyan kezelje a szerver az egyidejű hozzáférési kérelmeket ugyanahhoz az objektumhoz. Az objektum itt lehet tábla, extent, key range, lap, vagy akár egyetlen rekord is. Például mi történjen akkor, ha egy processz olvasni/módosítani/beszúrni akar egy olyan táblába(n), amit egy másik processz éppen ír/olvas/módosít. Helyes megválasztása esetén az alkalmazásunk egyszerűen jól fog működni, egy rossz döntéssel pedig feláldozhatjuk a teljesítményt vagy a logikai konzisztenciát. A transaction isolation level az SQL 2000-ben gyakorlatilag a lockok kezeléséről szól, aki szereti a perverziót, az ne használja őket, hanem írjon mindenhova locking hinteket (na jó, nem mindenhol lehet, de többnyire igen).
Miből lehet választani? Hát, az alapértelmezett érték szokás szerint a kellemes középszint, az esetek 99%-ára, ezt úgy nevezik, hogy READ COMMITTED. A read committed azt jelenti, hogy a tranzakcióink mindig csak olyan rekordokat olvasnak, amik ööö… kommittálva vannak (említettem már, hogy nem kéne erőltetni a magyarításokat az SQL-nél?), azaz nincs nyitott tranzakció, ami dolgozna rajtuk. Ez egy jó alapgondolat, a probléma ott kezdődik, amikor sokan akarnak olvasni meg írni valahova. És előáll az a hálás helyzet, hogy az olvasók várnak az írókra, hogy végre befejezzék az írást, legyen commit, és olvashassanak végre. Ennél még fájdalmasabb tud lenni az, amikor az írók írnának, de nem tudnak, mert az olvasók egyfolytában olvasnak, és shared lockokat helyeznek el az olvasott adatokon a konzisztencia jegyében, az írás pedig ugye exkluzív lockot igényelne… Azaz a konkurrencia növekedésével a teljesítmény erősen hanyatlik. Ez az a tulajdonság, amiért sok informatikus szerette volna az MSSQL fejlesztőit tartós hőkezelésnek alávetni, és ráadásul igazuk is volt. Ebből a zsákutcából ugyanis egyetlen kiút volt: a READ UNCOMMITTED transaction level, ami viszont hordoz némi kockázatot magában. Ebben az esetben ugyanis a tranzakció olvas mindent, amit lát, nem lockol semmit, nem érdekli az, hogy a rekord még épp módosítás alatt van egy tranzakcióban, csak olvas. Emiatt a teljesítménye kiváló, konkurrenciában nála jobb nincs, viszont ő képes arra, hogy a következőket elkövesse:

  1. Egy rekord többszöri kiolvasása – ha egy rekordot módosítanak, és hátrébb kerül az indexfában, uncommitted szinten simán kétszer kiolvasható.
  2. Egy rekord kihagyása – pont mint az előző, csak fordítva: ha előre kerül a rekord, és átugorja az olvasás aktuális pozícióját, sosem látjuk az eredményhalmazban.
  3. Sosem létezett rekord olvasása – ha egy tranzakció beszúr egy rekordot, majd később rollback lesz a sorsa, akkor az a rekord ugyan sosem került be az adatbázisba, mi mégis kiolvashattuk, ha épp arra jártunk.
  4. Létező rekord kihagyása – az előző fordítottja: ha egy tranzakció töröl egy rekordot, majd később rollback lesz a sorsa, akkor az a rekord ugyan sosem került ki az adatbázisból, mi mégsem láttuk az eredményhalmazban.

Ezek után felmerül a kérdés, hogy miért volna valaki olyan állat, hogy használja? Hát, például mert őt ezek az apróságok nem zavarják. Például (úgy) tudja, hogy olyan információkat kérdez így le, amiket nem befolyásolnak a fenti esetek. Egyszerű példa: azt akarom tudni, hogy hány előfizetőm van éppen, és mindegy, hogy 15643 vagy 15641 lesz a végeredmény. És amiért az üzemeltető él-hal a read uncommitted szintért: amikor fut egy masszív update/insert, ami mondjuk tízmillió rekordot darál végig, akkor a read uncommitted tökéletesen alkalmas arra, hogy megnézzük, hol is tart a processzünk, hacsak nem lőttünk annyira a teljesítményre, hogy belockoltuk az egész táblát, mert az ellen nem véd. Én például gyakran használom erre, sikerrel.
Aki hatékonyságra vágyott, az a read committed és uncommitted párokkal játszhatott, már SQL 2000 óta, és azért gondolkodhatott el a fejlesztők nyílt lángnak való kitételén, mert esetleg kikacsintott a világba, és látta, hogy az Oracle hogyan kezeli ezt az érdekes kérdést. Ott row versioning (sorverziózás) volt (van), és nem kellett választani, hogy kétes adatokat olvasson az alkalmazás, vagy nem-sokkal-többfelhasználós legyen. Egyszerűen annyit tett az adatbázis motor, hogy a módosuló soroknak az eredeti verzióját eltárolta, és aki ki akarta olvasni őket, azt a tárolt másolathoz irányította. Ez az evolúciós előny azért nem maradt az Oracle sajátja, az SQL 2005 enterprise babérokra töréséhez elengedhetetlen volt egy ilyen vagy hasonló megoldás megjelenése. Végül az ilyen mellett döntöttek. Mondhatni, hogy koppintották az ötletet, én sokkal inkább a migráció megkönnyítésére szavazok :)
Szóval az SQL Server 2005-ben megjelent a SNAPSHOT isolation level, amit egyrészt külön be kell kapcsolni az adatbázisban, másrészt kétféleképpen használható: használhatjuk teljesen új izolációs szintként, akár a korábban emlegetett kettőt, illetve lecserélhetjük a read committed izolációs szintet adatbázis szinten snapshot működésre (miután a snapshotot magát engedélyeztük), azaz ha valaki azt gondolja, hogy a default read committed szinten van, akkor téved, mert valójában snapshoton lesz. Ez tuljadonképpen azt jelenti, hogy akkor a default read committed szint helyett igazából default snapshot (sorverziózós) izolációs szintünk lesz, mint az… Oracle-ben… (mondtam már, hogy a migráció…? :) Persze megrögzött mániákus read committed rajongók számára van kiút (vagy visszaút) innen is.
Ennyi blabla után némi kód, először egy szálon, csak hogy ízlelgessük az utasításokat:

USE AdventureWorks
-- (1) Ha nem mondunk semmi extrát...
SELECT * FROM Sales.Currency
-- (2) ...akkor az olyan, mintha azt mondtuk volna, hogy
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM Sales.Currency
-- (Már ha az adatbázisunkon nem állítottuk be a READ_COMMITTED_SNAPSHOT értéket, amit nézzünk is meg, az ALLOW_SNAPSHOT_ISOLATION opcióval együtt.)
SELECT * FROM sys.databases
WHERE name = 'AdventureWorks'
-- Tekintsük meg a snapshot_isolation_state, snapshot_isolation_state_desc és is_read_committed_snapshot_on oszlopok tartalmát.
-- Alaphelyzetben minden ki van kapcsolva.

Persze ez az egész akkor lesz érdekes, amikor több tranzakciónk fut egyszerre. Úgyhogy nyissunk három query ablakot (A és B konkurrálgatnak, C-ben pedig megfigyelünk), és teszteljünk (a kommentben lévő sorszámok jelzik a futtatási sorrendet, a betűk pedig a processzeket):

-- (1) A
USE AdventureWorks
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Sales.Currency WITH (HOLDLOCK)
WHERE CurrencyCode = 'HUF'
-- (2) B
USE AdventureWorks
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Sales.Currency WITH (HOLDLOCK)
WHERE CurrencyCode = 'HUF'

Ezek eddig boldogan lefutottak. Az explicit read committed deklaráció amúgy felesleges, a with (holdlock) locking hint és a tranzakció pedig azért vannak ott, hogy meg tudjuk nézni az allokált lockokat, különben azok azonnal eltűnnének ahogy lefutott a lekérdezés.

-- (3) C
select request_session_id, resource_type, resource_database_id, resource_description, request_mode, request_type, request_status from sys.dm_tran_locks
where request_session_id in (61, 62)
order by request_session_id

Látszik, hogy mindenen két lock van, és a rekordunk KEY alapján van lockolva, mégpedig shared (S) formában, azaz többen is olvashatják egyszerre (mint mi most). (61 és 62 az A és B ablakok SPID-jei, igény szerint módosítandóak)
Próbáljunk meg írni:

-- (4) B
ROLLBACK
BEGIN TRAN
UPDATE Sales.Currency
SET [Name] = 'Magyar Forint'
WHERE CurrencyCode = 'HUF'

A B szál nem fut le, futva marad, mivel szeretné exkluzív (X) lockolni a módosításhoz a rekordot, de azon – mivel éppen olvassák – S lock van, úgyhogy várni kell. Hogy néz ez ki a lockok nyelvén?

-- (5)
select request_session_id, resource_type, resource_database_id, resource_description, request_mode, request_type, request_status from sys.dm_tran_locks
where request_session_id in (61, 62)
order by request_session_id

A B szál tett egy update (U) lockot a S lock mellé a rekordra, és szeretné megkapni az X lockját, de az CONVERT státuszban maradt, mert az S mellé nem fér oda az X (általában: semmi mellé nem fér oda az X). Dobjuk el az A szál által nyitott tranzakciót, hadd fusson ez tovább, és rögtön kérdezzünk is egyet:

-- (6) A
ROLLBACK
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

Megfordultak a szerepek, most A vár B-re, aki a ROLLBACK kiadásakor hirtelen lefutott, és betette az X lockját végre. Nézzük is meg:

-- (7) C
select request_session_id, resource_type, resource_database_id, resource_description, request_mode, request_type, request_status from sys.dm_tran_locks
where request_session_id in (61, 62)
order by request_session_id

Látjuk, hogy az A szálnak WAIT-ben maradt a S lockja. Rollbackeljük B tranzakcióját is! (Grétsy tanár úr sem mondta volna szebben)

-- (8) B
ROLLBACK

(A 9-es lépés eltűnt, mivel kivettem a tranzakció nyitását a 6-osból, így nem kell rollback.)
Most nézzük meg az uncommitted szintet is! Nyissunk egy update tranzakciót:

-- (10) B
BEGIN TRAN
UPDATE Sales.Currency
SET [Name] = 'Magyar Forint'
WHERE CurrencyCode = 'HUF'

A (6)-nál már láttuk, hogy ilyenkor kiakadunk read committed szinten. Nézzük mi történik uncommitted esetben:

-- (11) A
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

Simán lefutott, és visszaadta azt az értéket, amit a B tranzakción belül adtunk meg, ami még nyitott, és nincs kommittálva. Megállapíthatjuk, hogy találó nevet kapott a read uncommitted. Rollbackeljük a tranzakciót…

-- (12) B
ROLLBACK

… és kérdezzük le megint ugyanezt:

-- (13) A
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

Most a megfelelő, konzisztens értéket kapjuk vissza.
Lépjünk tovább a SQL 2005 nagyszerű újítása, a nem lockokkal operáló snapshot szint felé:

-- (14) B
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

Szép piros outputot kapunk, melyben elmeséli a SSMS, hogy szerinte nem jó vicc olyan adatbázisban snapshot izolációs szintet használni, ahol nincs engedélyezve. Nézzük meg ezt
T-SQL-ből is (15a), majd engedélyezzük (15b) egy egyszerű alter database paranccsal a snapshotot:

-- (15a) C
SELECT name, database_id, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases
WHERE name = 'AdventureWorks'
-- (15b) C
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON

SELECT name, database_id, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases
WHERE name = 'AdventureWorks'

Most még egyszer ugorjunk neki az előzőnek:

-- (16) B
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

A transaction isolation level már be lett állítva az előbb, hiszen az a session tulajdonsága, nem az adatbázisé, nem is tudom miért írtam ki még egyszer… Na, kezdjük újra az író-olvasó találkozót, és írjunk egyet az A szálon:

-- (17) A
BEGIN TRAN
UPDATE Sales.Currency
SET [Name] = 'Magyar Forint'
WHERE CurrencyCode = 'HUF'

Nézzük meg, melyik szinten mit látunk B-ben. (18a)-t és (18b)-t külön futtassuk:

-- (18a) B
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

-- (18b) B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

USE tempdb

Az első esetben a snapshot szinten az utolsó kommitolt értéket olvashattuk ki, várakozás nélkül. Ez a sorverziózás áldása, nem kell várakozni vagy szemetet olvasni. A második esetben az uncommitted szinten kiolvastunk valamit, ami még nincs is hivatalosan az addatbázisban. A harmadik esetben pedig lógva maradtunk, hiszen az X lock megakasztott minket. Rollbackeljük a tranzakciónkat. (a USE tempdb-ről később)

-- (19) A
ROLLBACK
USE tempdb

És most, hogy mindkét processzünket kiraktuk az AdventureWorksből, megtehetjük azt, ami nekem nagy kedvencem ebben az egész játékban:

-- (20) C
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON

Ehhez a művelethez nem lehet más az adatbázisban, csak az az egy processz, amelyik kiadja a parancsot, ezért mentek át a többiek a tempdb-be. És mostantól kezdve amikor az mondjuk, hogy read committed, akkor az SQL Server 2005 az AdventureWorks adatbázisban sorverziózást fog használni. Játsszuk újra az előző ki mit lát játékot:

-- (21) A
USE AdventureWorks
BEGIN TRAN
UPDATE Sales.Currency
SET [Name] = 'Magyar Forint'
WHERE CurrencyCode = 'HUF'

És kérdezzük végig a három szintet:

-- (22) B
USE AdventureWorks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM Sales.Currency
WHERE CurrencyCode = 'HUF'
-- hoppa :)

Jé, most lefut a read committed is, pedig neki várnia kellene az X lock miatt… de nem teszi… mert ő egy álruhás snapshot igazából. Ezt a kis trükköt egyedül a sys.databases-ből lehet kideríteni (lásd (15a)), mert ha a sys.dm_exec_sessions nézetből kérdezzük, ott is read committed a szint.

-- (23) C
SELECT name, database_id, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases
WHERE name = 'AdventureWorks'

Tüntessük el játszadozásunk nyomait:

-- (24) A
ROLLBACK
kill 61
kill 62
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF

Ennyit elsőre, később majd az innen kimaradt, konkurrenciát csökkentő szintekről is esik némi szó, de ezt is két hónapja kezdtem el írni :)

A demo script letölthetőitt.

2 Comments

  1. caverkid:

    Szia!

    Érdeklődnék, hogy lesz e ennek a bejegyzésnek folytatása? :-)

  2. Erik:

    Öööö…. persze… valamikor… Nem tudom, feltűnt-e, de tele vagyok első részekkel :) Amikor ezt írtam, még fiatal voltam, és optimista. Egyébként szerintem egy éven belül meglesz mindennek a következő része, de nem tudok közelebbit mondani.

Leave a comment