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.

2 Comments

  1. pacshu:

    Ez nagyon trükkös!

    De mi van akkor, ha én 10 vagy 15 táblába szeretnék sorszámot szeretnék, akkor 10 vagy 15 “árnyék” táblára lesz szükségem?
    Én nem vagyok egy SQL mester, de olvastam, hogy az SQL servernek van row lock szerű megoldása, nem lehetne akkor arra építkezni (cikkedben a 2. rossz lehetőség feljavításaként) ?

  2. Erik:

    Szia!

    Igen, akkor 10-15 tábla is kell. A #2 megoldással az a baj, hogy agyonveri a konkurrenciát. Működhet egy csomó esetben, ahol kicsi a forgalom, de én szeretem, ha többen is tudják masszírozni az alkalmazást.

    DECLARE @i int;
    BEGIN TRAN
    SELECT @i = MAX(sorszam) FROM dbo.Szamla WITH (XLOCK,HOLDLOCK);
    SET @i = @i+1;
    INSERT INTO dbo.Szamla VALUES(@i, 'stb');
    COMMIT
    

    Mint látható, az egész táblát le kellett lockolnom, mivel a maxot kerestem. Ez mondjuk egy nagyobbacska táblánál már fájhat.

    Erik

Leave a comment