Transactions در SQL Server

فصل بیست و هشتم :

Transactions

A transaction is a single unit of work

تراکنش عبارتست از یک واحد کاری که اگر اجرای کل آن موفقیت امیز باشد تمامی اطلاعاتی که در ضمن ان تغییر یافته اند بطور دائمی در دیتابیس ذخیره میشوند (Commit) و اگر دچار اشکال شود تمامی تغییرات انجام شده به قبل (Rollback) باز خواهند گشت. .در نظر بگیرید جهت انجام محاسباتی نیازمند چندین دستور tsql هستیم .مثلا می خواهیم عمیات کارت به کارت را در عابر بانک ها انجام دهیم . ابتدا می خواهیم مبلغ مورد نظر کاربر را از حساب وی کسر کرده و سپس آنرا به حساب مقصد واریز نماییم. در ساده ترین حالت بایستی حد اقل 2 آپدیت انجام دهیم .حال اگر آپدیت اول را انجام دادیم یعنی پول را از حساب کاربر کم کردیم.و در هنگام اجرای آپدیت دوم که باید همان مبلغ را به حساب مقصد واریز کنیم . اشکالی پیش باید مثلا برق قطع شود . سرور دچار مشکل سخت افزاری و یا نرم افزاری شود.

و یا خود دستور آپدیت ما دچار مشکل زمان اجرا شود چه پیش خواهد آمد. پولی از حسابی کم شده ولیکن به حساب مقصد واریز نشده است. بنابراین بایستی مکانیزمی داشته باشیم که تضمین کند یکسرس دستورات tsql ما همگی با هم انجام شوند . و اگر مشکلی پیش امد کل تغییرات رخ داده به قبل باز گردد یا اصطلاحا میگوییم rollback شود. یعنی اپدیت اول هم انجام نشود و پولی از حساب کاربر متقاضی ما کم نشود .
یا همه اجرا شوند یا هیچکدام
Transaction ها باید دارای 4 خاصیت زیر باشند:

ACID

• Atomicity

اتمیک باشند بطور یک واحد عملیاتی که یا همه دستورات آن بدرستی انجام میشود و یا هیچکدام. اگر دراجرا جایی مشکلی پیش آمد تمامی تغییرات به قبل باز خواهند گشت.

• Consistency

(ثبات و دوام) این خاصیت تضمین میکند که هر تراکنش دیتابیس را از یک وضعیت صحیح و معتبر به وضعیت معتبر دیگری می برد. یعنی هر دیتایی که در دیتابیس نوشته میشود بایستی بر طبق قواعد و constraints هاو تریگر ها معتبر باشد. مثلا در مثال انتقال پول از یک کارت به کارت دیگر consistency تضمین میکند که قبل و بعد از تراکنش همواره بایستی مجموع پول حسابها برابر باشد. اگر از یک حساب پول برداشته شود حتما بایستی به حسابی دیگر واریز گردد. در غیر اینصورت وضعیت و یا state دیتابیس قبل و بعد از تراکنش با هم فرق خواهد کرد. پس در هنگام بروز خطا بایستی تغییرات انجام شده به قبل باز گردند تا دوباره مجموع پول حسابهای ما برابر شود .و سیستم قبل و بعد از تراکنش دارای وضعیت معتبری باشد

• Isolation

تغییرات ایجاد شده توسط یک تراکنش بایستی از تغییرات ناشی از تراکنش های همزمان دیگر ایزوله و مجزا باشد. یک تراکنش اطلاعات را هم در وضعیت قبل از تغییر توسط تراکنش دیگر می بیند و هم در وضعیت بعد از تراکنش دیگر . ولیکن اطلاعات را در وضعیت مابین اجرای تراکنش دیگر نمی بیند . مثلا در مثال انتقال پول اگر تراکنش دیگری همزمان با این تراکنش اجرا شود . اطلاعات را بگونه ایی که برداشت از حسابی انجام شده ولی هنوز بهب حساب دیگر واریز نشده است نمی بیند . یعنی نتایج ناقص یک تراکنش برای تراکنش دیگر قابل مشاهده نیست .

• Durability

پایداری و دوام
هنگامی که یک تراکنش کامل شد تغییرات ناشی از ان بطور دائمی در دیتابیس نوشته میشوند . یعنی هنگامی که یک تراکنش بطور کامل انجام شد.حتی از اس کیوال سرور دچار مشکل شود و سرویس restart نیز شود و اطمینان داریم که تغییرات ناشی از تراکنش در اطلاعات جداول لحاظ خواهند شد . تضمین میکند تراکنش کامل شده حتما در اطلاعات جداول لحاظ خواهند شد.

انواع تراکنش ها

Autocommit transactions

هر عبارت tsql منفردی یک تراکنش است . هر دستور tsql هنگامی که کامل شود امکان commit و یا rollback را دارد.اگر کامل و بدون خطا انجام شود در نهایت تغییرات commit خواهند شد ولی اگر در انجام آن خطایی رخ دهد کل تغییرات انجام شده rollback خواهند شد . این نوع تراکنش پیشفرض Default اس کیوال سرور می باشد.

Implicit Transactions

هنگامی که کانکشن به اس کیو ال سرور به این مد باشد. (implicit transaction mode) . Instance اس کیو ال سرور بطور اتوماتیک پس از اتمام هر تراکنش (commit , rollback)تراکنش جدیدی را شروع خواهد کرد . شما شروع تراکنش را معین نخواهید کرد ولی می توانید commit شده و یا rollback شدن آنرا تعیین کنید . بنابراین یک زنجیره پیوسته از تراکنش ها خواهیم داشت.
هنگامی که یک کانکشن به اس کیو ال سرور برقرار شد که در آن implicit transaction mode به on تنظیم شده باشد موتور پایگاه داده شروع میکند به start کردن تراکنش هنگامی که در ابتدا یکی یکی از دستورات ذیل را اجرا نماید :

ALTER TABLE	
INSERT
CREATE
OPEN
DELETE
REVOKE
DROP
SELECT
FETCH
TRUNCATE TABLE
GRANT
UPDATE
                          

تراکنش تا زمانی ادامه خواهد یافت که شما دستورات commit و یا rollback را اجرا نمایید.بعد از اتمام تراکنش اول اگر بار دیگر یکی از دستورات بالا را اجرا کرد تراکنشی را شروع می کند و این روال تا جایی ادامه می یابد که implicit transaction mode=off شود
SET IMPLICIT_TRANSACTIONS { ON | OFF }
SET IDENTITY_INSERT [color] ON;
insert into Color (id,[ColorName]) values(150,N'آبی' );
go
insert into Color (id,[ColorName]) values (160,N'بنفش' );
go
insert into Color (id,[ColorName]) values (170,N 'نارنجی' );
go
select * from Color
go
rollback
select * from Color

go

نکته چگونه identity را reset کنیم : DBCC CHECKIDENT

مثلا :
DBCC CHECKIDENT ('Color',RESEED,2);
عدد بعدی 3 خواهد بود .

Explicit Transactions

بطور صریح شروع و پایان آنرا مشخص میکنیم .
BEGIN TRANSACTION
شروع تراکنش را مشخص میکنیم

COMMIT TRANSACTION or COMMIT WORK
اتمام موفقیت امیز تراکنش اگر خطایی در ضمن ان رخ ندهد .تمامی اطلاعت تغییر یافته در ضمن تراکنش بطور دائم در دیتابیس ذخیره میشوند و تمامی منابع اختصاص یافته توسط تراکنش ازاد میشود .

ROLLBACK TRANSACTION or ROLLBACK WORK
در هنگام بروز خطا برای پاک کردن تراکنش استفاده میشود و تمامی تغییرات رخ داده را به قبل از تاکنش rollback میکند
تمامی منابع اختصاص یافته توسط تراکنش ازاد میشود .

Control Of Flow

دستورتی که جریان اجرای دستورات TSQL را عوض میکنند

IF...ELSE

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

تحمیل شرط جهت اجرای دستورات
create procedure InsertFirstName (@firatname nvarchar(100))
as
if @firatname like N'%آقای%'
insert into Student(FirstName) values(@firatname)
else
insert into Student (FirstName) values(N'آقای'+ Space(2)+ @firatname)

go

BEGIN...END

محصور کردن تعدادی از دستورات tsql یعنوان یک گروه

TRY...CATCH

بمنظور error handling در اس کیو ال سرور از این عبارت استفاده میشود .

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH

begin transaction
SET IDENTITY_INSERT [color] ON;
begin try
insert into Color (id,[ColorName]) values(150,N'آبی' );
insert into Color (id,[ColorName]) values (160,N'بنفش' );
insert into Color (id,[ColorName]) values (150,N'نارنجی' );
commit transaction
end try
begin catch
rollback transaction
end catch

پیغام خطایی باز نمی گردد . در واقع خطای پیش آمده کنترل شده و با توجه به آن ترنزکشن کنترل میشود.

RETURN

هنگامی که اس یکو ال سرور به این دستور میرسد بدون قید و شرطی از کوئری ، batch و یا procedure ما خارج می شود .
create procedure InsertFirstName (@firatname nvarchar(100))
as
return;
if @firatname like N'%آقای%'
insert into Student(FirstName) values(@firatname)
else
insert into Student (FirstName) values(N'آقای'+ Space(2)+ @firatname)

go

WAITFOR

اجرای دستورات را بمدت مشخصی به تعویق می اندازد
اجرای یک BATCH و یا SP و یا تراکنش را متوقف می سازد تازمانیکه ساعت خاصی نرسیده و یازمان مشخصی سپری نشده است

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}

WAITFOR TIME '22:20'
update student set firstname=N'محمد' where id=1

WAITFOR DELAY '02:00';
WAITFOR DELAY '02:20'
update student set firstname=N'محمد' where id=1

خوب چند تا از Control flow ها رو در بالا نام بردیم حالا تابع

@@ERROR

در اس کیوال سرور هر خطایی دارای کدیinteger منحصر به فرد است
فانکشن @@error هنگامی که اخرین دس تور اجرا شده دچار خطایی شود کد آن خطا را باز میگرداند و اگر بدئون خطا باشد 0 باز میگرداند
insert into Color (id,[Color Name]) values(1,N'آبی' );
if @@error=544
print 'امکان درج در ستون identity نیست'

begin transaction

SET IDENTITY_INSERT [color] ON;
insert into Color (id,[ColorName]) values(150,N'آبی' );
insert into Color (id,[ColorName]) values (160,N'بنفش' );
insert into Color (id,[ColorName]) values (150,N'نارنجی' );
if @@ERROR>0
rollback transaction
else
commit transaction

go

برای بررسی بروز خطا ها می توان از control flow try...chatch استفاده کرد مثال ذیل را ببینید
create procedure moneytransfer
(@id1 smallint,@id2 smallint,@mablagh money)
as
set transaction isolation level repeatable read
begin transaction
begin try
update Student set Salary=Salary-@mablagh where id=@id1;
WAITFOR DELAY '00:00:10'
update Student set Salary=Salary+@mablagh where id=@id2;
commit transaction
end try
begin catch
rollback transaction
end catch

می توانیم تنها قسمتی از تراکنش را rollback کنیم نه همه آنرا
در داخل یک تراکنش می توانیم یک savepoint داشته باشیم که در صورت نیاز به آن نقطه باز گردیم
نکته بسیار مهم : هنگامی که یک savepoint داریم و به آن باز میگردیم . هنوز تذاکنش ما پایان نپذبرفته است و منابعی که تراکنش روی آنها کار میکند آزاد نشده اند پس بعد از rollback transaction [savepoint name] بایستی حتما تراکنش را یا rollback کنیم و یا commit تا منابع آزاد شوند . این منابع برلای اینکه یک تراکنش بتواند خواص ACID خود را حفظ کند قفل میشوند .

begin transaction
SET IDENTITY_INSERT [color] ON;
begin try
insert into Color (id,[Color Name]) values(250,N'آبی' );
save transaction a
insert into Color (id,[Color Name]) values (260,N'بنفش' );
insert into Color (id,[Color Name]) values (250,N'نارنجی' );
commit transaction
end try
begin catch
rollback transaction a
commit transaction
end catch

go
select * from Color

ISOLATION LEVEL

همانطور که قبلا بحث شد تراکنش ها امکان isolation رادارند یعنی منابعی که در حال کار بروی انها هستند را در مقابل تراکنش های دیگر همزمان ایزوله میکنند. حال اینکه این ایزولاسیون چگونه و به چه صورتی باشد توسط isolation level قابل تنظیم است. یک تراکنش همواره قفلی بروی اطلاعاتی را که بروی انها تغییر انجام میدهد میگذارد و تا تراکنش تکمیل نشود ان قفل را ازاد نمی کنند. و این مسائه از نوع ISOLATIN LEVEL که ما تنظیم می کنیم تاثیر نمی پذیرد .

SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]

READ UNCOMMITTED

مشخص می کند که دستورات TSQL می توانند اطلاعات تغییر یافته یک تراکنش دیگر راببینند حتی اگر هنوز آن تراکنش کامل نشده باشد. در این حالت دستورات دیگر ممکن است اطلاعات هنوز COMMIT نشده را بخوانند که به آن DIRTY READ میگویند . فرض کنید در ضمن یک تراکنش دوبار مقدار فیلدی را UPDATE میکینیم و در نهایت هنگامی که تراکنش را COMMIT میکیم می خواهیم مقدار ناهیی در جدول نوشته شود. اگر محاسبات اما انقدر سنگین و پیچیده باشند که فاصله بین 2 UPDATE ما زیاد شود و در فاصله زمانی بین دو اپدیت کاربر دیگری به اس کیولل سرور SESSION بزند و مقدار فیلد را بخواند . در حقیقت یک مقدار نهایی و صحیح را نخوانده. در نظر بگیرید که ایم مقدار می تواند موجودی پول یک کاربر باشد.
این نوع ایزولیشن لول . پایین ترین سطح می باشد که اجازه میدهدتا شما اطلاعاتی را که هم اکنون توسط سایر تراکنش ها تغییر یافته اند را بخوانید . همچنین اجازه میدهد سایر تراکنش ها اطلاعاتی را که در حال خواندن انها هستید را تغییر دهند.
دو مفهوم مهم

Dirty Reads : هنگامی که ما اطلاعات COMMIT نشده را می خوانیم
PHANTOM READS : هنگامی رخ میدهد که اطلاعاتی که خوانده اید و هر حال کار بروی انها هستید بعد از خوانده شدن توسط شما توسط تراکنش دیگری تغییر بیابند . در یک تراکنش اگر دوبار SELECT بزنیم نتایج متفاوت میگیریم .

کوئری دوم ما صبر نمی کند تا کوئری اول تمام شود.از آن مهم تر اینکه کوئری دوم اطلاعات DIRTY را باز میگرداند . در انتهای کوئری اول ما اطلاعات اپدیت شده را rollback می کنیم. ولی کوئری دوم اطلاعات غلطی را برای ما باز گرداند .

Read Committed

این ایزولیشن لول پیشفرض خود اس کیو ال سرور می باشد. هنگامی که در مثالهای قبل نوع ایزولیشن لول را مشخص نکردیم اس کیو ال سروراین نوع را استفاده میکند.
معنی ان این است که select ها تنها اطلاعات commit را باز میگردانند . سلکت صبر میکند تا تراکنشی که بروی اطلاعات کار میکند کارش تمام شود انوقت نتایج را باز میگرداند

Repeatable Read

همانند read commited و لی بایک تفاوت که گارانتی میکند اگر شما در متن یک تراکنش یک سلکت را دوبار اجرا کنید هر دوبار نتیجه یکسان دریافت خواهید کرد.

Serializable

این نوع ایزولیشن لول همانند repeatable read است بعلاوه اینکه گارانتی میکند که حتی رکوردی هم insert نشود پس جلوی phantom read را میگیرد . بنابریان باعث میشود سایر تراکنش ها که سعی در تغییر و یا درج اطلاعات جدید دارند ابتدا صبر کنند تا کار تاکنش تمام شود و قفل ها آزاد شوند و سپس کار خود راانجام دهند .

Snapshot

همانند serializable است و همان ایتم هایی را که serializable گارانتی میکند به ما میدهد با این تفاوت که روش کارش متفاوت است . در این روش وقتی تراکنش ما بروی اطلاعاتی کار میکند ، جلو بروز رسانی و یادرج رکورد جدید توسط سایر تراکنش ها گرفته نمی شود بلکه برای هر رکورد ورژن های گوناگونی در نظر گرفته میشود .و هنگامی که دیتا توسط تراکنش دیگری تغییر یافت نسخه قدیمی در tempdb نگهداری میشود . بنابراین تراکنش ما که این نوع ایزولیشن لول را دارد اطلاعات را همانند قبل و بدون تغییر میبیند . هنگامی که تمامی تراکنش هایی که قبل از تغییرات شروع شده اند تمام شدند اس کیو ال سرور اطلاعات نسخه قبلی را از tempdb پاک میکند . بنابراین شما جلوی کسی را سد نکرده ایید که تغییراتی ایجاد نکند. بلکه فقط منابع بیشتری را جهت ذخیره ورژن های گوناگون اطلاعات تراکنش کردید. که البته این منابع آزاد خواهند شد.
برای استفاده از این امکان شما بایستی ابتدا دستور ذیل را در دیتابیس اجرا کنیدتا این امکان فعال شود.
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

نکته مهم اینکه تا جایی که می توانید از سطوح پایین تر ایزولیشن استفاده کنید. مگر اینکه مجبور شوید.

چگونه ترنزکشن های کارامد بنویسیم :

1 . تا حد ممکن کوتاه باشد چرا که وقتی یک تراکنش اغاز میشود موتور پایگاه داده منابع زیادی را برای تضمین aCID مصرف میکند و تا تراکنش تمام نشود آنها را ازاد نمیکند.
2. در ضمن یک تراکنش هیچگاه از کاربر مقادیر را نگیرید. ابتدا تمامی اطلاعات مورد نظر را میگیریم سپس تراکنش راانجام م یدیهیم
3. هنگامی که می خواهید یک سری انالیز های اولیه را انجام دهید ترنزکشن را شروع نکنید. دیترین موقع باز کنید زود ترین موقع ببندید.
4. از سطوح پایین isolation استفاده کنید . بسیاری از تراکنش ها با read commited . بخوبی و بدرستی کا خواهند کرد.
5. به کمترین حجم اطلاعات ممکن در ضمن یک تراکنش دستیابی کنید.

بسیاری از مفاهیم درفیلم اموزشی بطور مفصل شرح داده شده اند . لذا حتما فیلم آموزشی این فصل را ببینید .

مفاهیم و موضوعات این فصل عبارتند از :

• Transactions

• ACID

• Autocommit transactions

• Implicit Transactions

• Explicit Transactions

• Control Of Flow

• ISOLATION LEVEL