A nested tárolt eljárás és a @@trancount. Ismerjük? Használjuk?

Amikor több tárolt eljárást kell egymásba ágyazni, amiket egyébként külön-külön is hívhat az alkalmazás, akkor szoktam elővenni a @@trancount-ot.
A nézőpontom az,  hogy 1 begin transaction legyen, és ne legyenek egymásba ágyazott tranzakciók. Az egymásba ágyazással az a baj, hogy ha egy közbenső tranzakció rollback-elve van, akkor az egész tranzakciós lánc rollback-elődik, viszont a kód fut tovább és a rollback utáni utasítások végre fognak hajtódni! Az ezután kiadott commit vagy rollback pedig már hibára fog futni.
Ezeket elkerülendően használnom a @@trancount változót arra, hogy biztosan csak 1 tranzakció nyiljon és csak 1 tranzakció záró utasítás fusson le.

 

Demozzunk egyet.
Készítünk egy t1 táblát, amibe 2 db SP ír. Első (usp_nested1) beszúr egy sort, a második (usp_nested2) frissíti ezt a sort. Bizonyos esetben az első sp tudja hívni a másodikat is, itt lesz az egymásba ágyazás.

 

Hozzuk létre a táblát.

 

USE [tempdb]
GO


IF OBJECT_ID('dbo.t1') IS NOT NULL
    DROP TABLE dbo.t1
GO

IF OBJECT_ID('dbo.usp_nested1') IS NOT NULL
DROP PROCEDURE dbo.usp_nested1
GO

IF OBJECT_ID('dbo.usp_nested2') IS NOT NULL
    DROP PROCEDURE dbo.usp_nested2
GO

CREATE TABLE dbo.t1 (
    id INT NOT NULL PRIMARY KEY,
    amount INT NOT NULL,
    created_at DATETIME NOT NULL
)
GO

Készítsük el a rosszul megírt tárolt eljárást:

CREATE PROCEDURE dbo.usp_nested1 
	@id INT,
	@amount INT,
	@created_at DATETIME,
	@additional_amount INT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @result INT = 0


	BEGIN TRAN

	INSERT INTO [dbo].[t1] ([id], [amount], [created_at])
		VALUES (@id, @amount, @created_at);

	IF @additional_amount IS NOT NULL
	BEGIN
		EXEC dbo.usp_nested2 
			@id = @id, 
			@additional_amount = @additional_amount,
			@result = @result OUTPUT
	END

	-- Ez végre fog hajtódni
	INSERT INTO [dbo].[t1]
		    ( [id], [amount], [created_at] )
	VALUES  ( 0, 0, '2010-01-01')

	SELECT 
            @@TRANCOUNT AS trancount, 
            @@NESTLEVEL AS nestedlevel, 
            OBJECT_NAME(@@PROCID, DB_ID()) AS spname


	IF @result = 0
		COMMIT
	ELSE 
		ROLLBACK
END
GO


CREATE PROCEDURE dbo.usp_nested2 
	@id INT,
	@additional_amount INT,
	@result INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @amount INT = 0

		BEGIN TRAN

		UPDATE [dbo].[t1]
		SET @amount = [amount] += @additional_amount
		WHERE [id] = @id

		SELECT 
                    @@TRANCOUNT AS trancount, 
                    @@NESTLEVEL AS nestedlevel, 
                    OBJECT_NAME(@@PROCID, DB_ID()) AS spname

		IF @amount > 10
		BEGIN
			SET @result = 1
        END

		IF @result = 0
			COMMIT
		ELSE 
			ROLLBACK

END
GO

Az usp_nested2 tárolt eljárás ROLLBACK-elni fog, ha adott sorban az amount értéke 10 főlé menne.  (Itt új sor lesz, így ha a @amount + additional_amount > 10 akkor rollback). Futtassuk.

EXEC dbo.[usp_nested1] 
    @id = 11,
    @amount = 10, 
    @created_at = '2010-01-01',
    @additional_amount = 1

Az eredmény egy nagy error:
01_error

És egy olyan sor, amit nem akartunk beszúrni:
02_inserted_row

Egészítsük ki a tárolt eljárást @@trancount változóval és tegyük bele a logikát, hogy csak 1 tranzakció nyitó és záró utasítás legyen.

ALTER PROCEDURE dbo.usp_nested1 
	@id INT,
	@amount INT,
	@created_at DATETIME,
	@additional_amount INT = null
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @result INT = 0

	IF @trancount = 0 
		BEGIN TRAN

		INSERT INTO [dbo].[t1] ([id], [amount], [created_at])
			VALUES (@id, @amount, @created_at);

		IF @additional_amount IS NOT NULL
		BEGIN
			EXEC dbo.usp_nested2 
				@id = @id, 
				@additional_amount = @additional_amount,
				@result = @result OUTPUT
		END

		SELECT 
                   @@TRANCOUNT AS trancount, 
                   @@NESTLEVEL AS nestedlevel, 
                   OBJECT_NAME(@@PROCID, DB_ID()) AS spname

	IF @trancount = 0 AND @@TRANCOUNT > 0
		IF @result = 0
			COMMIT
		ELSE 
			ROLLBACK
END
GO


ALTER PROCEDURE dbo.usp_nested2 
	@id INT,
	@additional_amount INT,
	@result INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @amount INT = 0

	IF @trancount = 0
		BEGIN TRAN

		UPDATE [dbo].[t1]
		SET @amount = [amount] += @additional_amount
		WHERE [id] = @id

		IF @amount > 10
			SET @result = 1


		-- Ez NEM fog végre hajtódni
		INSERT INTO [dbo].[t1]
				( [id], [amount], [created_at] )
		VALUES  ( -1, -1, '2010-01-01')

		SELECT 
                    @@TRANCOUNT AS trancount, 
                    @@NESTLEVEL AS nestedlevel, 
                    OBJECT_NAME(@@PROCID, DB_ID()) AS spname

	IF @trancount = 0 AND @@TRANCOUNT > 0
		IF @result = 0
			COMMIT
		ELSE
			ROLLBACK
END
GO

Ha ismét futtatjuk a tárolt eljárást és a tábla lekérdezést, akkor már nem kapunk se hibát, se “véletlen beszúrt” sort.

A tárolt eljárásokban nem a tökéletes kód volt a cél, hanem a @@trancount használatának bemutatása az egymásba ágyazott tárolt eljárásokban.
Megoldás lehet még a try-catch blokk használata, de én nem vagyok híve, hogy hibát generáljunk ott, ahol erre nincs szükség.

SQL Server Express mire elég?

Mindig érdekelt, hogy mit bír az SQL Server Express. Egy korábbi project kapcsán kiderült, hogy mi az amire biztosan elég.

Fogtuk a következőket:
– 2 db SSD diszk
– 2 db processzor (16 core)
– 16 GB memoria
– SQL Server Express 2014
– IIS webszerver
– ASP.NET nyelv

És rápakoltunk egy több adatbázisos monitoring rendszert, ami többszáz szervert monitoroz.

Az egyik adatbázis tábláján elég hangzatos számok jöttek ki:
– 1 tábla
– Napi 28 millió sor
– 1 év alatt 10 milliárd kiosztott id
– Millisec-es válaszidők
Nézzünk néhány képet is:

02_resource_monitor

 

01_queries

 

Bár lenne még mit optimalizálni, de az esetleg egy másik bejegyzés lesz.

 

MSSQL vs MySQL 1.0

Az utóbbi időben rendes kiképzést kaptam MySQL-ből és volt is mit optimalizálni MySQL szervereken, ezért erről is fogok írni a jövőben, első sorban olyan jellegű dolgokról, amit lényeges különbségnek találok a adatbáziskezelők működésében. Most nagy vonalakban írok a tapasztalataimról, később majd részelesebben.

Az optimalizálás során az alábbiakat tapasztaltam:

  1. Negatívumok:
    1. Cross / Outer Apply hiánya
    2. Tábla értékkel visszatérő függvények hiánya
    3. Execution plan szegényes információkat ad egy MS execution planhoz képest
    4. Jó végrehajtási tervvel is voltak lassú query-k, amiket több lekérdezésre kellett bontani
    5. Teljesítmény számlálók hiánya, amik csak adott lekérdezésre vonatkoznak. (pl: hány page-et olvasott fel, volt e lookup a végrehajtás során, stb)
    6. Trace-elés hiánya
    7. Sok mindenben kellett a megszerzett általános SQL ismeretekre támaszkodni, mert egyes helyeken kimérni nem, csak sejteni lehetett, hogy hol kell optimalizálni
  2. Pozitívumok:
    1. Set profiling: rengeteget segített a megoldásokban
    2. Query cache: zseniális találmány
    3. Az MS-hez képest egy nagyon rugalmasan kezelt replikációs megoldás
    4. Before trigger

 

Ami itt is előjött, hogy a jól megtervezett adatbázis struktúra a legfontosabb.

Végezetül: a MySQL egy nagyon jó adatbázis kezelő, megvan a felhasználási területe és oda pont ez való. Működésben sok helyen hasonlít az MS SQL-re, ez elég sokat segített a munka során.