Programozás kategória bejegyzései

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.

Reklámok

Az SQL-es logika

Több programozóval beszélve jutottam arra következtetésre, hogy ez téma megér egy postot.

A “hagyományos” programozás általában úgy megy, hogy a feladatot lebontjuk a legkisebb egységekre, ezeket lekódoljuk, majd ezen kódrészleteket használjuk fel. Nos, ez a logika az SQL-ben nem szokott hatékony lenni. Az SQL a halmazműveletekben erős, erre kell építkezni. Nézzünk egy nagyon egyszerű példát, aztán próbáljuk is ki:

Vannak user-eink, akik tárolnak nálunk pénzt. Kapunk egy user listát, akiknek x összegű bónuszt jóvá kell írni. Az adatbázisunk álljon 3 táblából:

  1. users tábla: id, név, egyenleg
  2. tranzakciók tábla: id, user_id, összeg, jogcím
  3. bónusz: user_id, bónusz összege

A “hagyományos” programozói logika a feladatot a következőképpen oldaná meg (Az update_one_user eljárás lesz):

  1. Ciklust indítunk a bónusz táblában lévő userekre
  2. Tranzakció indul
  3. Update adott user egyenlegén (UPDATE users SET összeg = összeg + bónusz WHERE id = @ciklus_user_id)
  4. Tranzakció insert adott userhez (INSERT INTO tranzakciók (user_id, összeg, jogcím) VALUES (@ciklus_user_id, bónusz, ‘bónusz jóváírás’)
  5. Tranzakció vége
  6. Ciklus vége

Ha megnézzük ezt a logikát, akkor látjuk, hogy users * 2 adatmódosító utasítás.

Ezzel szemben az SQL-es logika a következőt mondja (Az update_with_join eljárás lesz):

  1. Tranzakció indul
  2. Adjuk hozzá a userek egyenlegéhez a bónuszt (UPDATE u SET u.egyenleg = u.egyenleg + b.bónusz FROM users u JOIN bónusz b ON u.id = b.user_id)
  3. Könyveljük el a tranzakciók táblába is (INSERT INTO tranzakciók (user_id, összeg, jogcím) SELECT user_id, bónusz, ‘bónusz jóváírás’ FROM bónusz)
  4. Tranzakció vége

Nos, ebben az esetben ez két utasítás.

Köztes megoldás lehet, ha a tranzakciók táblára teszünk egy triggert, ami a tranzakcióhoz tartozó user egyenlegét frissíti. Részemről nem szeretem a triggereket, de a teszt kedvéért ezt is megcsináljuk, csak hogy lássuk mennyivel lassabb (Az update_with_trigger eljárás lesz):

  1. Tranzakció indul
  2. Könyveljük el a tranzakciók táblába is (INSERT INTO tranzakciók (user_id, összeg, jogcím) SELECT user_id, bónusz, ‘bónusz jóváírás’ FROM bónusz).
  3. Tranzakció vége

 

Nézzük a demót:
Hozzuk létre a táblákat és eljárásokat:

use tempdb
go

if object_id('users', 'U') is not null
	drop table users
go

if object_id('transactions', 'U') is not null
	drop table transactions
go

if object_id('user_bonus', 'U') is not null
	drop table user_bonus
go

if object_id('update_one_user', 'P') is not null
	drop procedure update_one_user
go

if object_id('update_with_join', 'P') is not null
	drop procedure update_with_join
go

if object_id('update_with_trigger', 'P') is not null
	drop procedure update_with_trigger
go

create table users (
	id int not null primary key,
	name varchar(255) not null,
	balance int not null
)

go

create table transactions (
	id int not null identity primary key,
	user_id int not null,
	amount int not null,
	pretence nvarchar(255) not null
)

go

create table user_bonus (
	user_id int not null primary key,
	bonus_amount int not null
)

go

create procedure update_one_user
	@user_id int,
	@amount int,
	@pretence nvarchar(255)
as
begin
	set nocount on;

	begin tran
		update users
		set balance += @amount
		where id = @user_id

		insert into transactions (user_id, amount, pretence)
		values (@user_id, @amount, @pretence)
	commit
end

go

create procedure update_with_join
as
begin
	set nocount on;

	begin tran
		update u
		set u.balance += b.bonus_amount
		from users u
		join user_bonus b
			on u.id = b.user_id

		insert into transactions (user_id, amount, pretence)
		select user_id, bonus_amount, N'bónusz jóváírás' as pretence
		from user_bonus
		order by user_id
	commit
end

go

create procedure update_with_trigger
as
begin
	set nocount on;

	begin tran
		insert into transactions (user_id, amount, pretence)
		select user_id, bonus_amount, N'bónusz jóváírás' as pretence
		from user_bonus
		order by user_id
	commit
end
go

Töltsük fel a táblákat:

use tempdb
go

truncate table users;
truncate table transactions;
truncate table user_bonus;
go

;with cte as (
	select top (10000)
		row_number() over(order by (select null)) as id
	from sys.columns c1
	cross join sys.columns c2
)

insert into users (id, name, balance)
select
	id,
	concat('Name ', id) as name,
	id * 1000 as balance
from cte

go

insert into transactions (user_id, amount, pretence)
select id as user_id, balance as amount, N'befizetés' as pretence
from users

go

insert into user_bonus (user_id, bonus_amount)
select id as user_id, left(abs(checksum(newid())),4) from users order by id
go

select * from users u
join user_bonus b
	on u.id = b.user_id
order by u.id

Végezzük el a tesztet:

use tempdb
go

set nocount on;

declare @user_id int
declare @bonus_amount int
declare C cursor for
	select user_id, bonus_amount
	from user_bonus
	order by user_id

open C
	fetch next from C into @user_id, @bonus_amount
	while (@@FETCH_STATUS = 0)
	begin
		exec update_one_user
			@user_id = @user_id,
			@amount = @bonus_amount,
			@pretence = N'bónusz jóváírás'
		fetch next from C into @user_id, @bonus_amount
	end
close C
deallocate C
go

execute update_with_join
go

if object_id('trg_update_user_balance', 'TR') is not null
	drop trigger trg_update_user_balance
go

create trigger trg_update_user_balance
	on dbo.transactions after insert
as
begin
	update u
	set u.balance += i.amount
	from users u
	join inserted i
		on u.id = i.user_id
end
go

exec update_with_trigger

Végezetül nézzük meg az eredményt:

use tempdb
go

select
	p.name,
	s.execution_count,
	s.total_worker_time,
	s.total_logical_reads,
	s.total_logical_writes,
	s.total_physical_reads
from sys.dm_exec_procedure_stats s
join sys.procedures p
	on s.object_id = p.object_id
where s.database_id = db_id()

Nálam ezt adta vissza a lekérdezés:

52_result

Tisztán látszik, hogy az SQL-es logika volt a legolcsóbb a szervernek.

Ha egyszerűen akarnám megfogalmazni az SQL-es logikát, akkor azt mondanám, hogy halmazonként (jelen esetben táblánként) hajtjuk végre a műveleteket (és nem pl: userenként, ami itt egy alkalmazás logikai egység), amit az SQL szerver nagyságrendekkel gyorsabban kezel.

Ez a példa nagyon leegyszerűsített, a valóságban ezek a feladatok jóval bonyolultabbak és nagyobb adatmennyiségeken kell dolgozni, ahol az SQL-es logikát is ciklusba kell helyezni (több millió sor update-je egyben fut x óráig, aztán lehet rollback lesz a vége mert nem lesz elég erőforrása a szervernek, közben elérhetetlen lesz az egész adatbázis, mert lock alá kerülnek a táblák), de nem egyesével, hanem nagyobb egységekben (pl: 10.000-esével) hajtódnak végre a műveletek.

Optimalizáció check constraint-el

A check constraint fő feladata, hogy az általunk meghatározott szabályoknak megfelelő adatokat (itt is figyelni kell a NULL-ra!) engedi be adott mezőbe, egyfajta validáció. Ez által az insert és update műveletek kicsit lassulnak a check constraint-el bíró mezőkön, hiszen a validálás idő.

Ha a check constraint-et a “with check” opcióval hozzuk létre, akkor a már meglévő adatokat is ellenőrzi, hogy megfelelnek e az általunk definiált feltétel(ek)nek. Az ezzel az opcióval létrehozott constraint egy nagyon erős teljesítménynövelő hatást is kiválthat. Hogyan?

Pl: Ha van egy státusz mezőnk, ami tinyint típusú, check contraint-al biztosítjuk, hogy 1-10-ig mehetnek bele adatok, a query-nk pedig így néz ki: SELECT * FROM tábla WHERE státusz = 11, akkor az optimalizer fogja tudni, hogy 11-es érték biztosan nincs az oszlopban, így eredmény sem lesz, ezért egy page-et sem fog felolvasni a táblából. Ebben az esetben sokkal hatékonyabb, mint bármelyik index!

Ez pedig arról jutott eszembe, hogy mostanában volt egy esetem. Olyan lekérdezésre bukkantam, amit már sehol nem használnak, de másodpercente sokkal többször hívták a kelleténél, toronymagasan vezette az egységnyi idő alatti CPU és diszk használatot. Ez a query így nézett ki (a query csak illusztráció 🙂 :

SELECT p.[user_id] p.[transaction_amount]
FROM [#tmp_user_property] p
JOIN [#tmp_user] u ON u.id = p.[user_id]
WHERE u.[name] = 'User 5'
    AND p.[transaction_date] > '2014-01-01 00:00:00.000'
GO

Csináljuk a lekérdezéshez adatokat:

USE [tempdb]
GO

IF object_id('tempdb..#tmp_user') IS NOT NULL
    DROP TABLE #tmp_user
GO

IF object_id('tempdb..#tmp_user_property') IS NOT NULL
    DROP TABLE #tmp_user_property
GO

CREATE TABLE #tmp_user (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,NAME VARCHAR(128) NOT NULL
    )
GO

CREATE TABLE #tmp_user_property (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,[user_id] INT NOT NULL
    ,transaction_amount INT NOT NULL
    ,transaction_date DATETIME NOT NULL
    )
GO

CREATE INDEX ix_user_id ON [#tmp_user_property] ([user_id])
GO

INSERT INTO [#tmp_user] ([id],[name])
SELECT TOP 10000
    row_number() OVER (ORDER BY (SELECT NULL)) AS id
    ,CONCAT ('User ',row_number() OVER (ORDER BY (SELECT NULL))) AS NAME
FROM sys.[columns] c1
CROSS JOIN sys.[columns] c2
GO

INSERT INTO [#tmp_user_property] (
    [id]
    ,[user_id]
    ,[transaction_amount]
    ,[transaction_date]
)
SELECT TOP 1000 t.id
    ,t.id AS [user_id]
    ,left(abs(checksum(newid())), 4) AS transaction_amount
    ,dateadd(day, row_number() OVER (ORDER BY (SELECT NULL)), '2010-01-01')
FROM [#tmp_user] t
ORDER BY t.[id]
GO

Ha megnézzük az adatokat, akkor láthatjuk, hogy 2014 évnél korábbiak vannak benne, tehát a fenti query soha nem ad eredményt.

Kapcsoljuk be az IO statisztikát:

set statistics io on;

Kapcsoljuk be az “Include Actual Execution Plan (Ctrl + M)” funkciót a management studióban, majd futtassuk a lekérdezést.

Ezeket kaptam:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#tmp_user_property__000000000006’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A plan pedig így néz ki:
050_result_01

Itt szépen látszik, hogy 6 darab 8 kb-os lapot érintett a lekérdezés és két táblát. Bár a végrehajtási terveben van egy Clustered Index Scan és egy jó index-el ezen lehet javítani, de nekünk most ennél több kell!

Rakjuk fel a check constraint-et:

ALTER TABLE [#tmp_user_property]
	WITH CHECK ADD CONSTRAINT CH_user_property_transaction_date CHECK (transaction_date < '2013-01-01')

Futtassuk ismét a lekérdezést, majd lássuk mit kapunk:
A Messages fülön: (0 row(s) affected)
A plan pedig ez:
050_result_02

A messages fülön láthatjuk, hogy egyetlen page-et sem érintettünk (a táblában szereplő adatokból), a végrehajtási tervből pedig látszik, hogy a táblához nem is nyúlt az optimalizer.

A fenti példánál mégegyszerű volt megoldani a problémát, mert megkértem a fejlesztőt, hogy nézzen már utána, hogy mire van használva a query. Viszont vannak esetek, amikor nincs fejlesztő, akivel lehet ezt le lehet kommunikálni, ilyen esetekben ez a check constraint nagyon jól jöhet.

A check constraint-ek egy másik, sokkal jobb felhasználási területe a particionált view. Akit érdekel a téma keressen rá, mert lehet, hogy holnap már hasznát tudja venni 🙂