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 :)

Folyamatos sorszámosztás, avagy szekvencia MSSQL módra

Itzik Ben-Gan egyike a nagy SQL koponyáknak napjainkban. Sajnos blogot nem ír, szerencsére könyvet igen. Alapvetően T-SQL programozással foglalkozna, de a jó T-SQL ugye hatékony is, tehát jön a Query Optimizer komponense az SQL Servernek, aztán a Query Processor, meg persze egy kicsit a tárolást sem árt ismerni… szóval ezermester. Szereti a fejtörőket, és szerintem ezzel összefüggésben áll az, hogy zseniális scripteket ír.

Van egy elég általános probléma alkalmazásfejlesztésben: folyamatos sorszámozás az adatbázisban – a klasszikus példa a számlaszám, amiben nem lehetnek lyukak, mert attól a könyvelő is meg az APEH is ideges. Erre van többféle megoldás, némelyik jó, némelyik nem. Nézzük előbb a rosszakat:

  1. Használjunk identity oszlopot! Ez minden sikertelen beszúrásnál is növelni fogja a sorszámot, és lukak maradnak utána.
  2. Kérdezzük le az utolsó kiadott sorszámot, és tároljuk el az eggyel nagyobbat. Ez csábítóan egyszerű, és speciális esetekben akár jó is lehet, a probléma a konkurrencia: ha ketten egyszerre kérdezik le a legutolsó sorszámot, ugyanazt fogják kiosztani következőnek.

És nézzünk egy jót: Tároljuk el a számlarekordot, egyelőre számlaszám nélkül, egy másik egyedi azonosítóval (ez lehet akár egy identity oszlop is), és tartsunk fenn egy táblát, aminek egyetlen oszlopa identity. Minden számlarekord eltárolás után húzzunk egy sorszámot (azaz szúrjunk be egy rekordot), és azt tegyük el számlaszámként. Mivel itt nem lehet sikertelen beszúrás, a sorszámozás folyamatos lesz. De mit csinálunk a nagyra nőtt sorszámgeneráló táblával? a TRUNCATE TABLE nem jó, mert az ugyan gyorsan üríti a táblát, de reseteli az identity értéket is, tehát megint az elejéről kezdjük osztani a számlákat. A DELETE technikailag jó, de sok rekord törlésénél esetleg lock eszkalációba futunk, aminek az lesz a vége, hogy senki nem fog új számlát készíteni egy darabig.

A problémára gyógyír Itzik egy kis mintascriptje:

USE tempdb;
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
CREATE PROC dbo.usp_AsyncSeq
 @val AS INT OUTPUT
AS
BEGIN TRAN
 SAVE TRAN S1;
 INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
 SET @val = SCOPE_IDENTITY()
 ROLLBACK TRAN S1;
COMMIT TRAN
GO

Ez a dbo.usp_AsyncSeq tárolt eljárás tulajdonképpen az Oracle szekvencia implementációja. Fog egy táblát, amibe mindig beszúr, de nem menti el a beszúrást igazából, csak lekéri az identity értéket. Így a tábla mindig üres, és a karbantartása meg van oldva gyárilag. Használata kb. a következő:


USE tempdb;
-- készítünk egy számla táblát
CREATE TABLE dbo.Szamla (
a int identity,
b varchar(10) PRIMARY KEY, 
--csak azért kell ez a primary key, mert így a legkönnyebb sikertelen beszúrásokat szimulálni később
szamlaszam int)
GO

-- meg egy tárolt eljárást
CREATE PROCEDURE dbo.usp_UjSzamla
@b varchar(10)
AS
-- ha nem sikerül a számlarekord elmentése, ez a beállítás megakadályozza, hogy továbbfusson a script, és feleslegesen húzzon sorszámot
SET XACT_ABORT ON
declare @a int, @szla int
INSERT INTO dbo.Szamla(b) VALUES(@b)
SELECT @a = SCOPE_IDENTITY()
-- itt hívjuk meg a sorszámosztót, ha sikeresen elmentettük a számlát
EXEC dbo.usp_AsyncSeq @val = @szla OUTPUT;
UPDATE dbo.Szamla SET szamlaszam = @szla WHERE a = @a;
-- kikapcsoljuk a szigorúságot
SET XACT_ABORT OFF
GO

--és kipróbáljuk:
EXEC dbo.usp_UjSzamla 'PROBA'
EXEC dbo.usp_UjSzamla 'PROBA'
EXEC dbo.usp_UjSzamla 'PROBA2'

select * from dbo.Szamla
select * from dbo.AsyncSeq

Az eredeti tárolt eljárásban megüthette az ember szemét a SAVE TRAN(SACTION) utasítás. Ez pont olyan, mint a SAVE GAME a játékokban: vissza lehet állni a mentett állapotra. Igen, egy tranzakción belül, tulajdonképpen részleges rollback, ami ugye nincs. De mégis van. Erről a témáról hamarosan lesz egy cikk a Technet portálon, egyelőre érjétek be ennyivel.

Tranzakciós log ürítése és nem ürítése

Mostanság SQL DBA-t keresek, és szakmai tesztekkel és interjúkkal is töltöm rengeteg szabadidőmet. Alapvetően megerősítve látom azon véleményemet, hogy az informatika vallásközeli tudomány, és sokan transzcendens vonásokat találnak a szerverek működésében, és babonás eszközökkel közelítik meg őket. Az egyik ilyen misztikus terület a tranzakciós log szerepe és használata.
Mindenekelőtt szeretném leszögezni a következőt: a tranzakció teljesülését a diszkre fogja kiírni rögtön az SQL szerver, nem a memóriában írja a tranzakciós logot, mert annak ugye nem volna sok értelme, ha a commit tényét egy illékony tárban rögzítené. De ez részletkérdés pillanatnyi motivációm szempontjából, nézzük inkább meg, hogy mikor szabadítja fel az SQL a tranzakciós logot. Három feltételnek kell tejesülnie:

  1. Az adatbázis recovery modellje simple vagy (full és bulk-logged esetén) az adott naplórész le lett backupolva.
  2. A tranzakciós log szóban forgó része nem tartalmaz aktív tranzakciót, azaz minden olyan tranzakció, ami a log írásakor aktív volt, már véget ért (ha rollback lett ,akkor a rollback is véget ért).
  3. Az adatbázis nincs replikálva vagy ha igen, akkor a Log Reader Agent már elolvasta az adott részt

Ezeknek a feltételeknek egyszerre teljesülniük kell. Azaz ha leállítom a Log Reader Agentet, akkor elkezd nőni a tranlog az égig, hogy ne rontsa el a replikációt. Hasonlóan elkezd nőni a log, ha sikerült egy tranzakicónak nyitva maradnia az adatbázisban, és lassan az egész tranlog aktívvá válik miatta.
Tehát ha megnő a log, érdemes a fenti pontokat ellenőrizni, körülbelül ebben a sorrendben (recovery model/utolsó log backup; legrégebbi nyitott tranzakció; replikáció esetén LRA).
Ja, még egy utolsó: a TRUNCATE LOG parancsok margójára: ha valaki rendszeresen kiadja a truncate log parancsot, akkor gondolkozzon el azon, hogy miért van full recovery model beállítva, és gyorsan állítsa át simple-re. Köszi.

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.’ »