Az OUTPUT clause

Ideje, hogy valami szakmait is írjak, mert kezd uncsi lenni a blog. Szóval van egy szép hosszú listám arról, hogy mi mindent nem mondtam el a Millenárisban tartott beszédemben, és ebből mazsolázgatok. Kezdjük is el!

Az első egy kicsi, de hasznos apróság: az OUTPUT clause vagy ahogy Serény tanár úr írná, a klóz. Mire jó? Tipikus jelenet, hogy kell update-elnem rekordokat egy OLTP adatbázisban, azaz az adat változik alattam, de szeretném tudni, hogy pontosan melyeket update-eltem meg. Erre a gyenge megoldás a

select primary_key into #temptabla from tabla
where status = 'NEW'

update tabla where primary_key in (select primary_key from #temptabla)
set status = 'ACTIVE'

drop table #temptabla

Ez egy egész jó megközelítés, két feltétel teljesülése esetén:

  1. Nulla konkurrencia lehetséges, azaz pl. nem akar egyszerre két alkalmazásszerver dolgozni az adatbáziban, ekkor ugyanis ők lelkesen dolgozzák fel a rekordokat – egyszerre. Ezt nem részletezném, hogy miért gáz.
  2. 2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.

A dolog roppant egyszerű: bele van dugva egy belső trigger gyakorlatilag, ami a következőt tudja: ha beletömjük az OUTPUT-ot az INSERT/UPDATE/DELETE utasításba, akkor lesz inserted és/vagy deleted táblánk, és lehet kiszipkázni az adatokat, akár csak kiechózni, akár betenni táblaváltozókba. Az OUTPUT helye a WHERE feltétel előtt van még, erre figyeljetek. Valahogy így:

UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key
WHERE status = 'NEW'

Valljuk be, hogy jobban néz ki. Ha el akarjuk menteni az outputot későbbi reszeléshez vagy csak nyakonvágni a teljesítményt, akkor pedig a fenti példa így néz ki:

DECLARE @tablacska table (primary_key int);
UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key INTO @tablacska
WHERE status = 'NEW';
SELECT primary_key FROM @tablacska;

Ez egyébként fokozható, például ha már így előjött a konkurrencia, mint probléma, kiválóan megoldható a sok alkalmazás egy táblát túr probléma ezzel meg egy kis locking hinteléssel:

UPDATE tabla WITH (READPAST)
SET status = 'ACTIVE'
OUTPUT inserted.*
WHERE status = 'NEW'

Így ha több szerver dolgoz fel egy queue-t, hogy szépen mondjam, nem kell nagyon foglalkozniuk a másikkal. Egy konkrét szerver odamegy, kivesz annyi rekordot, amennyit akar (mert lehet TOP-ot is mondani az update-ben), ha valaki más is éppen update-el, akkor az általa lockolt rekordokat átugorja, és keres olyat, ami nincs lockolva. Ez a READPAST ajándéka: átugorja a lockolt rekordokat, így nem akadnak fenn egymáson a párhuzamos processzek.

(disclaimer: épp nincs SQL szerverem, ezért a példákat fejből írtam, ha nem működnek, bocsi, kijavítom :)

Error 7105 – konkurrencia érdekesen

A konkurrencia margójára egy érdekes hiba és megoldása:
Error: 7105, Severity: 22, State: 6
Page (1:42521), slot 14 for text, ntext, or image node does not exist.

Ilyet is tud mondani esetenként az SQL Server, és ez elég rosszul néz ki elsőre. Pár perc guglizás után sokkal rosszabbnak tűnik, mert az értelmezés szerint valószínűleg van egy corrupt page (vagy legalábbis referencia) az adatbázisban, de ha futtatunk egy DBCC CHECKDB-t, akkor semmi hiba nem derül ki (ha kiderül, akkor az a B ág, lehet restore-olni :). Aztán pár nap múlva megint előjön, majd semmi hetekig, megint előjön, és az ember kezdi furcsán érezni magát.
A leggyakoribb előidézője ennek az esetnek a csodálatos read uncommitted tranzakciós szint, megoldása pedig a dirty read elkerülése. Mi is történik? A read uncommitted szintről már tudjuk, hogy ő olvas mindent pillanatnyi állapot szerint, függetlenül attól, hogy az a tranzakció, ami előidézte a jelenlegi állapotot, befejeződött-e vagy még nem. A másik fő jellemzőjét a locking hintként elérhető művészneve adja: NOLOCK. Azaz ő nem lockol semmit. Ennek akkor van jelentősége, maikor valaki töröl vagy módosít, mert ilyenkor a “normál” read committed query a shared lockjával nem engedi, hogy kihúzzák alóla a rekordot, de az uncommitted queryvel mindent meg lehet tenni, ezt is. És aztán amikor eljut egy BLOB-hoz a query, ami a rekordban csak egy 16 byte-os pointer képében tárolódik, és valójában egyéb lapokra van “kitéve” az adatbázis fájlban, akkor még az is megeshet, hogy éppen volt valami módosítás a BLOB-ok háza táján, és amit mi a fenti példában a 42521-es lap 14. rekeszében keresünk, az már átugrott valahova máshova, vagy egyszerűen törölve lett. A pointer persze update-elődik a rekordban, de aki uncommitted módon olvas, az pont be tud esni két szék közé ilyenkor.
Az igazi megoldás a snapshot izolációs szint bekapcsolása és használata az alkalmazásból való nolock-os kérdezések helyett.

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). Continue reading ‘Tranzakció izolációs szintek avagy konkurrencia MSSQL módra I.’ »

Szemafor T-SQL-ben

Egy szép napon szembesültem azzal a problémával, hogy alkalmazásszerverek párhuzamos futásuk közben vágják egymás alatt a fát. Az alkalmazások módosítása kicsit fájdalmas lett volna, ezért SQL oldalon kezeltük le a problémát. A kritikus szakaszban egyszerre csak egyvalaki lehet – ezt szokták szemaforral biztosítani. Na, ez történt itt is, csak T-SQL-ben szemaforoztam. Érdekes élmény:

-- SQL 2005 - alapvetően a try-catch-re építünk
WHILE 1 = 1
BEGIN
BEGIN TRY
	CREATE TABLE ##semaphor (a int);
	BREAK;
END TRY
BEGIN CATCH
	WAITFOR DELAY '000:00:03';
	CONTINUE;
END CATCH
END
-- Itt a kritikus szakasz
DROP TABLE ##semaphor

A gondolatmenet egyszerű: van egy globális temptábla (a dupla rácsos linzer jelzi, hogy ezt bárki elérheti, nemcsak a létrehozója), ami a szemafor. Aki létrehozta, az beléphet a kritikus szakaszba, a többiek meg egy helyben ugrálnak, és próbálják létrehozni a táblát. Ennyi.
Felvetődik a kérdés, hogy mi van azzal, aki SQL 2000-t futtat még. Az egyrészt upgrade-elhet :), másrészt pedig használhatja a következő scriptet:

-- SQL 2000-ben az object_id a barátunk
WHILE object_id('tempdb..##semaphor') is not null
BEGIN
WAITFOR DELAY '000:00:03'
END
CREATE TABLE ##semaphor (a int)
-- Itt a kritikus szakasz
DROP TABLE ##semaphor

Mivel a SQL 2000-ben még nincs try-catch, teljesen más logikával kell hozzáállnom: lekérdezem az object_id-ját a temptáblának, ha létezik, akkor várok, ha nem, akkor megcsinálom. Egyetlen gyenge pontja az előzőhöz képest: ha egyszerre ketten látják úgy, hogy nincs ott a temptábla, és megpróbálják létrehozni, akkor az egyik sikerül, a másik pedig elszáll egy szép exception-nel, mert már létezik a tábla. Ezt sajnos nem bírtam lekezelni. Egyébként meglepően gyakran fordul elő ez az eset ahhzo képest, hogy mennyire kicsinek tűnik a valószínűsége.