Uncategorized kategória bejegyzései

Megjött (már egy ideje): dm_exec_function_stats

Az SQL Server 2016-al  végre megjött a dm_exec_function_stats DMV is. Még nem tud mindent, de azért már jó, hogy van. És jó tudni róla 🙂

Részletesen pl.: itt mutatják be.

Verziófrissítés előtt – “Deprecated Database Engine Features”

Hogy a verziófrissítés minél kevesebb izgalmat tartson, ajánlott az alábbi lekérdezést futtatni:

SELECT * 
FROM sys.[dm_os_performance_counters] 
WHERE [object_name] = 'SQLServer:Deprecated Features' 
    AND [cntr_value] > 0

Az eredményt pedig megvizsgálni minimum az alábbi oldalon, a megfelelő verziót kiválasztva.

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.