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.

Hogyan rejtsük el az adatbázisokat

Pár hete megkeresett egy szak- és sorstársunk, az aktív-aktív clusterről zengett ódáim kapcsán, és megkérezte, hogy hogyan tudná smucig ügyfelét boldoggá tenni. A felállás a következő: van egy cluster, és nem aktív-aktív clustert szeretnének, hanem több SQL instance-t az adott resource groupba, hogy spóroljanak. A cél az, hogy különböző felhasználóknak az az élményük legyen, hogy senki más nem használja az adott SQL szervert. Az alábbiakban leírom, hogy miért NEM lehet ezt szépen megcsinálni.

Mi is az SQL cluster alapgondolata? Van egy resource group, benne pár diszk, egy IP cím meg egy hálózati név, és az egészre ráteszek egy SQL szervert. Mi lenne, ha hasonlóan a standalone szervereken használt named instance-hoz, még egy SQL szervert telepítenénk? A gondolat jó, de sajnos ez nem működik by design. A BOL ezt írja róla konkrétan: ” Each resource group can contain at most one instance of SQL Server.” Mi ennek az oka? Hát, nekem az jutott eszembe, hogy ha clustert építek, akkor nagy rendelkezésreállást akarok. Ha két instance-t telepítek ugyanabba a resource groupba, akkor azok együtt kell, h billenjenek, tehát ha A instance megdöglik, és elbillen a cluster, akkor viszi B instance-t is, kis szünetet okozva. Ha valamelyik diszk hal meg, az meg pláne kinyír mindent. Szóval így erős függés alakulhat ki a két instance között, és pont ezt akarja elkerülni a technológia.

Tehát zsákutca. Akkor nézzünk más megoldást. Közismert, hogy az SQL 2005-ben a security alrendszert fantasztikusan kibővítették, hihetetlen dolgokat lehet megtenni benne. Úgyhogy nézzünk is gyorsan körül, és találjuk meg a következő jogot: VIEW ANY DATABASE. Ha ezt revoke-oljuk, akkor mi történik? Hősünk csak az általa használható adatbázisokat látja a sys.databases táblában, ezzel együtt a Management Studio adatbázis-választó dropdown listája is beszűkül a megfelelő adatbázisokra. Ez nagyon jól hangzik, de a szigorítás sajnos túl jól sikerül. Az Object Explorerben baloldalt ugyanis eltűnik az összes adatbázis, csak a master és a tempdb látszik. Így emberünknek nincs meg az az illúziója, hogy ő egy dedikált szerveren dolgozik, az viszont megvan, hogy széjjelpolicyzta valaki. Ez a kis tüske benne van 2005-ben és 2008-ban is (R2-ben is), a Microsoft azt mondja, hogy majd egy következő major release-ben foglalkoznak vele (2011 vagy 2014 vagy efféle). Tökéletesen működő megoldás tehát nincs, ám teljesen meglepő módon a legjobb eredményt SQL 2000 Query Analyzerrel lehet elérni. Annak az Object Explorere megmutatja az adatbázisokat is megfelelően, rejtély, hogy hova tűnt ez 2005 táján. Az alábbi képen jól látható, hogy a foo userrel belépve a dropdown list jó, az Object Explorerben viszont nem látszik a foo adatbázis.

Hab a tortán: nem bírtam magammal, és elindítottam egy Profilert, hogy megnézzem, mit kérdez a kliens, amitől eltűnnek az adatbázisok az OE-ből. Semmit. Az Object Explorer refreshkor futó T-SQL lekérdezésben ott van benne rendesen a kis privát adatbázisom, de valamiért a gonosz kliens szoftver ezt megeszi. A Microsoft Connecten van már egy item arról, hogyan kéne működnie. Én részemről inkább bugnak tekintem, mint feature requestnek, de ez az én konzervativizmusom. Mindenkinek, aki szeretne ezzel játszani, egy kiváló minta script, Aaron Bertrandtól, aki szintén morcos volt egy kicsit.

USE [master];
GO
CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
CREATE LOGIN [foo] WITH
    PASSWORD = N'foo',
    DEFAULT_DATABASE = [foo],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO
CREATE LOGIN [bar] WITH
    PASSWORD = N'bar',
    DEFAULT_DATABASE = [bar],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO
USE [foo];
GO
CREATE USER [foo]
    FOR LOGIN [foo]
    WITH DEFAULT_SCHEMA = [dbo];
GO
USE [bar];
GO
CREATE USER [bar]
    FOR LOGIN [bar]
    WITH DEFAULT_SCHEMA = [dbo];
GO
USE [master];
GO
DENY VIEW ANY DATABASE TO [foo];
DENY VIEW ANY DATABASE TO [bar];
GO

Ha már más tollával ékeskedem, meg kell jegyeznem, hogy ha a fent említett foo vagy bar usereket dbo-vá (nem db_ownerré) tesszük, akkor elvárás szerint működik a dolog:

USE foo;
GO
DROP USER foo;
GO
USE bar;
GO
DROP USER bar;
GO
USE [master];
GO
ALTER AUTHORIZATION ON DATABASE::foo TO [foo];
ALTER AUTHORIZATION ON DATABASE::bar TO [bar];
GO

Apró probléma: egy adatbázisnak csak egy dbo-ja lehet. De legalább kicsit workaroundoltuk.

SQL újdonságok a piacon

Egyelőre még mindig nem jutottam el oda, hogy egy normális szakmai cikket írjak, mert a *** automatizált szerver menedzsmentet csinálom (kárpótlásul a végén majd néhányat felteszek a scriptekből :), és úgy látszik, hogy nem tudok beszélni/írni róla meg csinálni is.

Viszont van két csodás hír, amivel nem is olyan régen találkoztam: az egyik az, hogy van új Books Online az SQL 2008-hoz (és csak ismételni tudom magam, használjátok mindig a legfrissebb BOL-t), ha minden igaz, az SP1 CU2 verzióhoz van update-elve, letölthető innen.

Az SQL 2008 SP1 CU2 pedig az a verzió, amiről már korábban írtam, hogy nagyon várom, mondjuk én főleg SQL 2005-höz. Ugyanis itt válik lehetővé – egy külön megadott trace flaggel – az SQL szerver memóriájának a fizikai memóriában tartása, a fantasztikus lock pages in memory. Az SQL Server Support Team blogjában olvasható egy szép cikk erről. Az SQL 2005 SP3 CU4 elméletileg egy hónap múlva itt lesz, tekintve, hogy eddig tartották a határidőt, én nagyon optimista vagyok.

Oracle is MySQL TimesTen

Apró színes: az Oracle felvásárolja a Sunt, aminek eredményeképpen az Oracle a klasszikus adatbáziskezelője, a TimesTen memória-alapú adatbázis, a Siebel CRM alkalmazás és a WebLogic alkalmazásszerver mellett a MySQL-t, a Glassfish Java alkalmazásszervert, magát a Javát, a Solaris operációs rendszert és a Sun hardvereit is birtokolja majd. Kíváncsi vagyok, mi lesz ebből, főleg ami a Glassfisht illeti. A MySQL kiváló kistestvér lehet az Oracle RDBMS mellé, bár a bejelentésben nem igazán esett róla szó. Technikai és üzleti szakértők szerint egyaránt kiváló eszköz lehet Larry Ellis számára, hogy a Microsoft SQL Servert szorongassa, immár két irányból (akárcsak a J2EE a .Net ellen – tényleg nem szereti a figura a Microsoftot, nem én találtam ki). Hát majd meglátjuk, mi lesz belőle. Még jó, hogy konyítok MySQL-hez is :P

Blogok

Mostanában nem nagyon jutottam el oda, hogy írjak, mert dolgozom elég sokat (az őrsvezetői munkától néha alig tudok DBA-zni is), meg kiolvastam a Narnia krónikáit az elmúlt két hétben.

A munkahely mókás, éppen egy Halálcsillagot építek: önjáró SQL szerver, ahol az adatbázisokat két kattintással meg lehet csinálni a guideline-nak megfelelőre (adatfájlok, fájlméret, security, monitorozás, maintenance plan, stb.), automatikus index-tuning és security fix van, és kb. mindent megcsinál, ami a DBA dolga lenne. Mellette meg aktív-aktív clustert is építek, mert az költséghatékony.
De a lényeg az, hogy ezek miatt nem írok. Helyette olvasok sokat. Aki tud angolul, annak ajánlok két blogot: az egyik a SQL Customer Advisory Team blogja, ami tele van mindenféle hasznos szösszenettel, a másik az ISV-knek készülő blog, ahova átlag havonta egyszer psotolnak, de azok elég alaposak cserébe, sok kóddal. Sajnos Paul Randal egyre kevesebb nekem tetszőt (300+ mélységű tanulságok az SQL engine-ről) blogol, a linkeket a whitepaperekre meg nem húzza le az RSS readerem, úgyhogy bánatos vagyok.
Persze tudom, aki tud angolul, az majd hülye lesz pont az én blogomat olvasni, mikor rengeteg egyéb lehetősége is van. Viszont van egy-két olyan jó post ezeken, hogy lehet, hogy magyarítom őket. Más tolla is állhat jól… :)