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:
- 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.
- 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 :)
Antal István:
“2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.”
Szerintem a “többet” lenne a helyes kifejezés ide. :-)
7 October 2010, 1:27 pmErik:
Arra gondoltam, hogy a 2008-as év *előtt* ez még elfogadható kód volt. Majd legközelebb jobban fogalmazok. Vagy legalább igyekszem…
7 October 2010, 6:47 pm