مدیریت خطای تراکنش در SQL Server

مدیریت خطای تراکنش در SQL Server نقشی اساسی در حفظ انسجام و سلامت داده‌ها در پایگاه داده دارد. در فرآیندهای تراکنشی، هر عملیات باید یا به‌طور کامل انجام شود یا در صورت بروز خطا، تمام تغییرات آن بازگردانده شود تا داده‌ها در وضعیت ناسازگار قرار نگیرند. SQL Server ابزارها و سازوکارهای متنوعی مانند بلوک‌های TRY...CATCH، دستورات BEGIN TRANSACTION، COMMIT و ROLLBACK را برای کنترل خطا و مدیریت تراکنش‌ها فراهم کرده است. با استفاده‌ی درست از این قابلیت‌ها، می‌توان از بروز مشکلاتی مانند از بین رفتن داده، ناسازگاری رکوردها یا تراکنش‌های نیمه‌تمام جلوگیری کرد و قابلیت اعتماد و پایداری سیستم‌های مبتنی بر پایگاه داده را افزایش داد.

مدیریت تراکنش در SQL Server

مدیریت تراکنش، در این مقاله مفاهیم ACID، بررسی خواهد شد. همچنین معرفی تراکنش های محلی (local) و تراکنش توزیع شده (distributed) و تعریف یک تراکنش به صورت صریح (explicit) توسط GOTO با یک برچسب (با و بدون @@trancount) با دو برچسب توسط حلقه‌ی WHILE و دستور BREAK داخل Stored Procedure با کمک دستور RETURN معرفی متغیر سیستمی سراسری @@trancount خواهد شد. 

مدیریت خطای تراکنش در SQL Server، یکی از مهمترین مباحث در مدیریت تراکنش در SQL Server در حقیقت مدیریت خطا در تراکنش‌ها می‌باشد، در این مقاله می‌خواهیم با به دام انداختن خطاهای تراکنش در SQL Server آشنا شویم. 

اصول مدیریت تراکنش

مدیریت خطای تراکنش در SQL Server، در این بخش از مقاله به معرفی اصول مدیریت تراکنش خواهیم پرداخت. با ما همراه باشید:

بررسی مفهوم ACID

کلمه ACID در مدیریت تراکنش خلاصه شده چهار کلمه Atomicity Consistency Isolation Durability می باشد. هر تراکنش دارای چهار ویژگی فوق می باشد (که به ترتیب از سمت چپ به راست دارای معانی اتمی بودن، سازگاری، جداسازی و پایداری می باشند). که هر کدام به معنا و مفهوم زیر هستند. شما می‌توانید کوئری نویسیرا به صورت گام‌به‌گام از نیک آموز فرا بگیرید. 

مفهوم Atomicity

مدیریت تراکنش، مام تغییرات داده در داخل تراکنش می‌بایست با موفقیت انجام شود یا اینکه هیچ یک از تغییرات انجام نشود (همه یا هیچ کدام). به عبارتی عملیات دستکاری داده ها در تراکنش یک واحد به حساب می آیند و غیر قابل تجزیه هستند. یا همه با هم با موفقیت اجرا میشن یا اینکه هیچ کدام اجرا نمی شوند.

مفهوم Consistency

مدیریت خطای تراکنش در SQL Server، تراکنش‌ها، سازگاری و جامعیت داده‌های پایگاه داده (database) را حفظ می کنند. به بیان دیگر، تراکنش، پایگاه داده را از یک حالت سازگار (consistent) به حالت سازگار دیگری تبدیل می کند. به این معنا که در صورت roll back شدن تراکنش (با شکست مواجه شدن یکی از دستورات) پایگاه داده به حالت (state) سازگاری قبل از اجرا شدن تراکنش بر میگرد. یا بعد از پذیرفته شدن ترکانش (commit) یک وضعیت سازگاری جدید شکل میگیرد (مطابق نمودار هایی که در مقاله قبل ارائه شد). در نتیجه هیچ گاه پایگاه داده را در حالت ناسازگار رها نخواهد کرد (یکسری دستورات با موفقیت اجرا شده و یکسری شکست خورده اند).

مفهوم Isolation

تغییرات هر تراکنش مستقل از سایر ترکانش ها می باشد. تراکنش ها جدا از یکدیگر هستند. به بیان دیگر، هر چند به طور کلی ممکن است چند تراکنش به طور همزمان اجرا شوند اما به هنگام سازی هر یک از این تراکنش ها از بقیه ی تراکنش‌ها پنهان نگه داشته می شود تا وقتی که تراکنش پذیرفته شود. به عبارت دیگر برای دو تراکنش متمایز T1 و T2، تراکنش T1 می تواند به هنگام سازی های T2 را ببیند (پس از پذیرفته شدن T2) یا تراکنش T2 می تواند به هنگام سازی های T1 را ببیند (پس از پذیرفته شدن T1) اما هر دو به طور همزمان نمی توانند به هنگام سازی های یکدیگر را ببینند.

مفهوم Durability

 در مدیریت تراکنش، پس از آن که تراکنشی پذیرفته شد (committed) به هنگام سازی های آن، در پایگاه داده باقی می ماند و state جدید پایگاه داده قابل دسترسی بوده، حتی اگر سیستم اندکی بعد به دلیل مشکلات سخت افزاری و یا نرم افزاری از کار بیفتد. در SQL Server عملا این ترمیم و بازسازی توسط checkpoint در SQL Server صورت گرفته و رویه ترمیم پایگاه داده (recovery) در هنگام startup انجام میشود.

فهمیدن ویژگی های ACID

مدیریت خطای تراکنش در SQL Server، یک تراکنش ATM (دستگاه خود پرداز بانکی) را تصور کنید، که شما ۱۰۰$ قصد دارید از حساب بانکی خود بیرون بکشید. داده ها برای این تراکنش می‌توانند در یک پایگاه داده‌ها به صورت یک جدول با دو ستون که یکی از آنها شماره حساب یا AccountID شما و دیگری موجودی حساب یا AccountBalance شما را نگهداری می کنند در نظر گرفته بشوند.

(واژه شناسی: یکی از معانی کلمه balance در زبان انگلیسی موجودی می باشد و منظور از account balance همان موجودی حساب بانکی است که مترادف با سرمایه یا fund می باشد).

برای شروع، موجودی شما ۱۰۰ دلار است، بنابراین بعد از بیرون کشیدن ۱۰۰ دلار از حسابتان، موجودی شما باید صفر شود. همچنین طبیعتا باید سیستم تضمین کند که شما به اندازه کافی سرمایه در حساب خود دارید قبل از اینکه پول نقد داده شود.
دو عمل فوق یعنی بروزرسانی موجودی حساب شما و بررسی سرمایه حساب شما در قالب دو پرس و جو پایگاه داده‌ها (database query) انجام می شود که هر دوی آن باید در یک تراکنش قرار گیرند. ابتدا با پرس و جوی زیر بررسی می کنیم آیا به اندازه درخواست شده در حساب شما موجودی هست یا خیر.

SELECT AccountBalance FROM Account WHERE AccountId = @AccountId

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

UPDATE Account
SET AccountBalance -= 100
WHERE AccountId = @AccountId

این دو عمل مجزا در این تراکنش دو پرس و جوی پایگاه داده ها هستند که عمل بیرون کشیدن پول نقد را پشتیبانی می کنند. هر دوی آنها باید با موفقیت اجرا شوند تا پول بیرون آید در غیر اینصورت با شکست یکی از آنها کل عملیات شکست خواهد خورد و پولی بیرون نخواهد آمد. این اولین ویژگی یک تراکنش به نام Atomicity می باشد. به معنای تجزیه ناپذیری.

و حالا اجازه دهید کمی ماهیت تراکنش مورد نظر را تغییر دهیم. فرض کنید موجودی حساب ۱۵۰$ است و داخل یک تراکنش کاربر درخواست برداشت پول نقد به مقدار ۱۰۰$ و همچنین انتقال ۷۵$ دیگر به حساب دوم را دارد. اولین پرس و جوی بروزرسانی موفق خواهد بود، تغییر موجودی حساب به $۱۵۰-$۱۰۰=$۵۰. اما عمل دوم شکست خواهد خورد زیرا پول کافی به اندازه $۷۵ در حساب فرد مورد نظر باقی نخواهد ماند. بنابراین شما احتیاج دارید راهی که توسط آن پول بیرون آمده را برگردانده و پایگاه داده را به وضعیت قبل از اجرای تراکنش برگردانید.

در سناریوهای جهان واقعی (real-world) شما بصورت نرمال عمل بیرون کشیدن پول و انتقال وجه را در یک تراکنش با یکدیگر ترکیب نخواهید کرد، اما این تنها یک مثال ساده بود تا نشان دهد داده ها می توانند در یک وضعیت ناسازگار (inconsistent) قرار بگیرند. در این شرایط که یکی از چند عمل با شکست مواجه می شود برای سازگاری پایگاه داده ها باید عملیاتی که موفق بودن roll back شوند. این موضوع ویژگی دوم ACID یعنی consistency را شرح می دهد.

(واژه شناسی: کلمه roll در فرم فعل آن به معانی مختلف است که یکی از آنها غلتیدن و قِل خوردن است؛ و به تبع roll back که سرهم نوشته می شود به معنای غلتیدن به عقب است).

اکنون فرض می کنیم اعمال بیرون کشیدن پول و انتقال وجه به دو تراکنش مجزا به جای یک تراکنش تجزیه شده اند، اما اتفاقی بصورت همزمان اجرا شده اند. هر تراکنش مجبور خواهد بود موجودی فعلی حساب را توسط اجرای query ای مشابه این بررسی کند:

SELECT AccountBalance FROM Account WHERE AccountId = @AccountId

اگر سیستم عمل خواندن داده‌ها بصورت همزمان از جدول را بصورت صریح بلوکه نکرده باشد آنگاه هر دو تراکنش یک نتیجه یکسان بدست خواهند آورد: $۱۵۰. به این معنا که هر دو تراکنش تصور می کنند که در حساب به اندازه کافی سرمایه وجود دارد. یک تراکنش $۱۰۰ را خواهد داد و دیگری $۷۵ را انتقال خواهد داد. نتیجه کسر $۱۷۵ از حساب در مجموع خواهد بود در حالی که در حساب در واقع تنها $۱۵۰ موجود است.

در بیشتر سیستم‌ها مخصوصا برنامه های مالی این تراکنش ها باید از همدیگر جدا بوده تا از عملی به نام خواندن کثیف (dirty read) جلوگیری به عمل آید. این جداسازی تراکنش ها از یکدیگر سومین ویژگی ACID یعنی isolation را نشان می دهد.

مدیریت خطای تراکنش در SQL Server، وقتی که شما تمام عملیات موجود در تراکنش تان را با موفقیت به پایان رساندین، شما نمی خواهید تغییراتی که بوجود آمده است را از دست دهید. به عبارت دیگر خرابی سیستم نباید تاثیری روی اعمال شدن تراکنش بگذارد. این ویژگی مرتبط است به چهارمین خصیصه ACID به نام durability.

آشنایی با خطاهای رایج در SQL Server

در مسیر کار با مدیریت خطای تراکنش در SQL Server، آشنایی با خطاهای متداول یک ضرورت است. در ویدیوی زیر، نگاهی کلی به برخی خطاهای رایجی می‌اندازیم که ممکن است در فرآیند اجرای دستورات یا مدیریت تراکنش‌ها با آن‌ها روبه‌رو شوید.

مدیریت تراکنش در SQL Server

• انواع روش های به دام انداختن خطا (Error Trapping).
• خظای @@error.
• مفهوم TRY…CATCH.
• سطح شدت خطا (Error Severity Level).
• استفاده از تراکنش در TRY…CATCH و برعکس.
• خطاهایی که به دام نمی افتند.

SQL Server بطور پیش فرض در صورت بروز خطا در داخل یک تراکنش، آن را بصورت اتوماتیک و ضمنی برای شما ROLLBACK نخواهد کرد. پس ناچاریم با به دام انداختن خطا ها کار Rollback و صدور پیغام مقتضی را خود به عهده بگیریم. بنابراین لازم است کمی با بحث Error Handling در T-SQL آشنا شوید.

در مقاله مدیریت خطای تراکنش در SQL Server (بررسی ویژگی های تراکنش و تعریف آن) به ابتدایی ترین روش مدیریت خطا که مدتها بود برنامه نویسان SQL Server از آن استفاده می کردند (یعنی بررسی مقدار متغیر @@error) اشاره ای مختصر و در حد نیاز داشتیم. اما این شیوه به دلیلی که در ادامه به آن اشاره خواهد شد کارامد نیست.

یکی از مشکلاتی که در این شیوه مطرح است، مربوط به مدت زمان اعتبار مقدار متغیر @@error است. در واقع اعتبار مقدار این متغیر تا قبل از اجرای عبارت بعدی است. لذا اگر در مکانی از کد، احتمال بروز خطا متصور است، حتما از یک متغیر محلی و ثبت مقدار @@error و یا پردازش مستقیم خطای رخ داده استفاده کنید.

برای پی بردن به این واقعیت در اینجا دو مثال آورده ‎ایم.

مثال ۱:

کدهای زیر را در یک پنجره Query اجرا کنید و نتیجه (قسمت Result) را مشاهده کنید. چه می‌بینید؟

SELECT 1/0
SELECT @@ERROR
SELECT @@ERROR

اولین دستور با شکست مواجه خواهد شد و پیغام خطایی مبنی بر تقسیم بر صفر (Divide by zero) صادر خواهد کرد. پس متغیر @@error حاوی شماره مربوط به خطا خواهد شد (که در اینجا شماره خطا ۸۱۳۴ است)

دومین دستور مقدار متغیر @@error را بر میگرداند (که همان عدد مذکور است) و سپس مقدار صفر به متغیر @@error اختصاص داده می شود (که به معنای اجرای موفقیت آمیز آخرین دستور است).

حالا سومین دستور مقدار ۰ را به جای عدد ۸۱۳۴ برمیگرداند.
این موضوع محدود به اینگونه دستورات نمیشود. بر اساس مستندات Microsoft حتی عبارات شرطی مثل IF مقدار @@error را reset می کنند.

مثال ۲:

SELECT 1/0
IF @@ERROR > 0 SELECT @@ERROR

اولین @@error آخرین شماره خطا را نگه داشته است که عددی بزرگتر از صفر است. پس شرط دستور IF صحیح است در نتیجه @@error انتخاب می شود، اما دومین @@error که بعد از بررسی دستور IF اجرا می شود بر خلاف تصور شماره خطا مربوط به آخرین دستور که شرط IF باشد را نگهداشته است که عدد صفر است. به بیان دیگر دستور IF مقدار @@error را reset کرده است.

1. نکته ۱: لیست تمام پیغام های خطا در ویوی sys.messages قرار دارد. توجه داشته باشید که هر پیغام خطا به چند زبان در جدول وجود دارد. لذا تعداد سطرهای این جدول متناظر با تعداد کل خطاها نیست.
2. نکته ۲: در مقاله قبل برای اینکه بررسی کنیم آیا خطایی رخ داده است یا خیر، مقدار متغیر @@error را با عملگر “نامساوی با صفر” مقایسه میکردیم. اما بر اساس داده های این جدول خطایی با شماره ی کمتر از ۲۱ نداریم. در نتیجه بنظر میرسد عملگر “بزرگتر از صفر” کفایت کند. و از طرفی پیغام خطاهایی که کاربر تعریف می کند (user-defined) بواسطه ی sp_addmessage باید شماره ای بالاتر از پنجاه هزار داشته باشند.

آیا می‌توانید “نکته ۲” را نقض کنید؟

سطح شدت خطا (Error Severity Level) ، Error ها دارای پارامتری به نام  Severity هستند که شدت خطا را تعیین می کند. خطاهایی با شدت ۱۰ و کمتر از آن به عنوان هشدار (Warning) یا پیام های اطلاع راسانی در نظر گرفته می شوند. و Error هایی با سطح شدت ۱۱ و بالاتر به عنوان Error محسوب می شوند (فرقی نمی کند که این پیغام با دستور raiserror تولید شده یا توسط دستور دیگر). نکته ای که قصد بیان آن را داشتم این است که @@error شماره خطاهایی با شدت ۱۰ و کمتر از آن را برنمیگرداند.

ساختار TRY…CATCH

می‌توان ادعا کرد که برای هر بخش از کد این امکان متصور است که در زمان اجرا به مشکلی پیش بینی نشده برخورد کرده و خطایی را تولید کند. در بیشتر مواقع این موضوع که بتوان راهکاری برای به دام انداختن این خطا پیدا کرد، از اهمیت ویژه ای برخوردار است. همانطور که مشاهده شد، برای آزمایش هر خط، از @@error استفاده کردیم که تعداد دستوراتمان را دو برابر می کند. واکنش بهتری نیز وجود دارد، به طوری که اگر یک مجموعه از عبارت ها، خطایی را در زمان اجرا تولید کرد، بتوان آنها را شناسایی و مهار نمود، اینجاست که ساختار TRY…CATCH مطرح می شود.

مدیریت خطای تراکنش در SQL Server، در T-SQL مشابه زبان برنامه نویسی Visual C# می توان Error ها را مورد پردازش قرار داد. این ساختار شامل دو بخش می باشد بلاک TRY و بلاک CATCH. زمانی که یک خطا در یکی از عبارات T-SQL ای که داخل بلاک TRY قرار دارد تشخیص داده شود کنترل پاس داده می شود به بلاک CATCH جایی که خطا پردازش می شود.

شایان ذکر است که این ویژگی در نسخه ۲۰۰۵ به T-SQL افزوده شده است پس در نسخه ی ۲۰۰۰ به دنبال آن نگردید.  بعد از اینکه در بلاک CATCH خطا مدیریت شد کنترل منتقل می شود به اولین عبارت T-SQL که در ادامه ی عبارت END CATCH قرار دارد. در بلاک TRY عبارات T-SQL که بعد از عبارتی که موجب بروز خطا شد قرار دارند اجرا نخواهند شد. شما می‌توانید کوئری نویسیرا به صورت گام‌به‌گام از نیک آموز فرا بگیرید. اگر هیچ خطایی داخل بلاک TRY وجود نداشته باشد کنترل پاس داده می شود به عبارتی که بلافاصله بعد از دستور END CATCH وجود دارد (توضیحات تکمیلی را میتوانید از Book Online بدست آورید).

بررسی Syntax این ساختار:

BEGIN TRY
{T-SQL Statement}
END TRY
BEGIN CATCH
{T-SQL Statement}
END CATCH

همانند @@error این ساختار خطاهایی با شدت ۱۰ و کمتر از آن را Handle نمی کند. به این معنا که اجرای دستورات داخل بلاک TRY ادامه پیدا میکنند و کنترل به بلاک CATCH منتقل نمی شود. داخل محدوده ی بلاک CATCH توابع سیستمی زیر به جهت بدست آوردن اطلاعات پیرامون خطای رخ داده می توانند مورد استفاده قرار گیرند مثل شماره خطا، شدت خطا، پیام خطا و غیره.

ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()

اولین تابع مشابه @@error شماره خطا را برمیگرداند. چهارمین تابع از بالا، نام SP یا Trigger ای را که خطا در آن اتفاق افتاده است را برمیگرداند.

• نکته: توابع فوق خارج از محدوده ی بلاک CATCH مقدار NULL را بر میگردانند. متاسفانه تمام خطاها توسط این ساختار شناسایی نمی شوند. مثل خطای ارجاع معلق (Object Name Resolution). بطور نمونه میخواهیم داده های جدولی که وجود خارجی ندارد (قبلا ایجاد نشده است) را بازیابی کنیم.

begin try
select * from NonExistentTable
end try
begin catch
print 'error'
end catch

در اینجا کنترل به دست بلاک CATCH نمی افتد و پیغامی که مدنظر ما بود نمایش داده نمی شود. حالا اگر همین دستور SELECT را داخل یک SP اجرا کنیم آنگاه توسط بلاک Catch مدیریت خواهد شد، به این صورت:

create procedure usp_example
as select * from NonExistentTable
begin try
execute usp_example
end try
begin catch
select ERROR_MESSAGE() as message_error
end catch

استفاده از TRY…CATCH در تراکنش

مثال ۱:
مثال زیر نشان می دهد که چگونه بلاک TRY…CATCH داخل یک تراکنش کار می کند. عبارت داخل بلاک TRY خطای “نقض قید” تولید می کند.

 

USE AdventureWorks2012;
GO
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

توضیح اجمالی اینکه در این مثال عبارت موجود در بلاک Try قیدی را نقض کرده در نتیجه کنترل به بلاک Catch منتقل می شود و در آنجا اطلاعاتی راجب خطای رخ داده صادر می شه و در صورتی که تراکنش فعالی وجود داشته باشه تراکنش Rollback می شه و کنترل اجرا برنامه به دستور بعد از END CATCH منتقل می شود و سپس برای اینکه تراکنشی که قبلا Rollback شده Commit نشود بررسی می کنیم که آیا تراکنش فعالی داریم یا خیر در صورتی که تراکنش فعالی داشتیم به این معناست که عبارات بدون خطا اجرا شدن پس تراکنش را Commit می کنیم.

مثال ۲:

در این مثال تراکنش داخل بلاک TRY تعریف شده است. پرسش از خواننده: چه لزومی دارد داخل بلاک Catch مقدار @@trancount بررسی شود؟ چرا که بنظر میرسد زمانی که کنترل به بلاک Catch منتقل شده است هنوز عمل commit صورت نگرفته است پس قطعا تراکنش فعال داریم.

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.invoice_header
(invoice_number, client_number)
VALUES (2367, 19)
INSERT INTO dbo.invoice_detail
(invoice_number, line_number, part_number)
VALUES (2367, 1, 84367)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION
-- And do some cool error handling
END CATCH

خطاهایی که به دام نمی‌افتند!

خطاهایی هستند که شما آنها را نمی توانید بصورت سنتی (@@error) به دام بی اندازید. مثل Conversion Failed که زمانی رخ میدهد که سعی کنید مقدار رشته ای را در ستونی عددی درج کنید یا امثالهم. این کد را امتحان کنید تا در عمل این موضوع را مشاهده کنید:

--Declaring a variable table
declare @t table(i int);
select 'statment befor error';
--Conversion failed
insert into @t values ('string_value');
select 'statement after error', @@ERROR;

توضیح مثال: ابتدا یک جدول (از نوع متغیری) ایجاد می کنیم تا به واسطه ی آن عمل درج داشته باشیم. سپس یک مقدار را انتخاب می کنیم (statement befor error) که در خروجی نیز ظاهر می شود (قسمت Result) سپس سعی میکنیم مقدار string_value را داخل ستون i که از نوع integer هست درج کنیم که با شکست مواجه می شود. که این شکست باعث break شدن batch نیز می شود در نتیجه دستورات بعدی موجود در این batch نیز اجرا نخواهد شد پس توسط تابع @@error نمی توانیم کد خطا را بدست آوریم.

البته می توانید کد فوق را به دو batch توسط GO تقسیم کنید. اما این ایده ی خوبی نیست. آیا میتوان داخل بدنه ی Stored Procedure از GO استفاده نمود؟

اما این مشکل در ساختار TRY…CATCH وجود ندارد. امتحان کنید:

--Declaring a variable table
declare @t table(i int);
begin try
--Conversion failed
insert into @t values ('string_value');
end try
begin catch
select @@ERROR as error_number
end catch

انواع تراکنش از لحاظ منبع

مدیریت تراکنش می تواند با یک منبع کار کند مثل یک database یا چندین منبع مثل چندین database. تراکنش هایی که محدود شده اند به یک منبع local transaction نامیده می شوند و تراکنش هایی که وابسته به چند منبع هستند distributed transaction نامیده می شوند. ما راجب تراکنش های local در ادامه بحث خواهیم نمود. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند. 

تراکنش‌های Explicit یا صریح 

تراکنش های صریح عموما داخل استور پروسیجر تعریف می شوند. همانطور که در مقاله قبل توضیح داده شد، برای اینکه شروع یک تراکنش را اعلام کنیم از عبارت BEGIN TRANSACTION استفاده می کنیم. عبارت BEGIN TRANSACTION از syntax زیر استفاده می کند:

BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }]

در این عبارت شما می توانید یک نام برای تراکنش تعیین کنید بوسیله استفاده از transaction_name یا @tran_name_variable. همانطور که مشاهده می شود به جای کلید واژه ی TRANSACTION می توان از کلید واژه ی کوته شده ی TRAN نیز استفاده نمود.

بعد از اینکه شروع تراکنش را اعلام کردین تمام عملگرها DML که بعد از BEGIN TRANSACTION قرار گرفته اند داخل تراکنش در نظر گرفته می شوند تا اینکه تراکنش تایید یا رد شود. به این معنا که تغییرات ذخیره شده (commiting) یا اینکه تمام تغییرات برگشت داده شوند (rolling back).

اگر شما میخواهید تغییرات را دائمی کنید عبارت COMMIT TRANSACTION را اجرا کنید که از syntax زیر استفاده می کند:

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]

اینجا یک مثال هست که دو عبارت DML (یکی UPDATE و دیگری دستور INSERT) را در یک تراکنش صریح نشان می دهد:

BEGIN TRANSACTION
UPDATE Table1 SET Column1 = 'One'
INSERT INTO Table2 (Column2) VALUES ('Two')
COMMIT

SQL Server تعداد تراکنش ها را نگهداری می کند، که تعداد تراکنش های فعال برای connection جاری را بر می گرداند. شما می توانید تعداد تراکنش های جاری را بوسیله تابع @@TRANCOUNT بدست آورید.

هر بار که شما BEGIN TRANSACTION را صدا می زنید به مقدار @@TRANCOUNT یکی اضافه می شود. و به طور مشابه هر بار که شما COMMIT TRANSACTION را صدا می زنید SQL Server مقدار @@TRANCOUNT را یکی کاهش می دهد. تا مادامی که مقدار @@trancount به صفر برنگشته است تراکنش فعال خواهد ماند. زمانی که شما BEGIN TRANSACTION را داخل یک بلاک تراکنش صدا می زنید عملا شما یک تراکنش تو در تو (nested transaction) ایجاد کرده اید. اگر شما می خواهید تغییرات دائمی و پایدار نشوند و در عوض آن می خواهید پایگاه داده ها به وضعیت قبلی خود بازگردد می توانید تغییرات را roll back نموده با عبارت ROLLBACK TRANSACTION، که بر اساس syntax ای است که در ادامه قرار گرفته است:

ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]

یادداشت: در حالی که ROLLBACK داده‌ها را به حالت قبل از شروع تراکنش برمی‌گرداند برخی از ویژگی‌ها مثل seed یک ستون identity به قبل بر نمی‌گردد (Reset نمی‌شود). بطور نمونه اگر ده سطر بعد از درج لغو شوند seed این ستون ۱۰ واحد افزوده خواهد شد.

ROLLBACK متضاد COMMIT است. بجای ذخیره تغییراتی که در تراکنش بوجود آمده اند آنها را برگردانده. این مهم است که بدانید SQL Server هرگز COMMIT را در نظر نمی گیرد. اگر شما از SQL Server قطع شوید بدون اینکه صراحتا COMMIT کرده باشید، SQL Server یک ROLLBACK برای تراکنش در نظر می گیرد. به هر حال شما نباید هرگز تراکنش را اینگونه ترک کنید تا تصمیم به دست SQL Server بی افتد. شما باید صراحتا به SQL Server بگویید کدام از دو گزینه را می خواهید.

در نتیجه این فکر خوبی است که صراحتا ROLLBACK را هنگامی که خطا رخ داد صدا کنیم. (چراکه همانطور که توضیح داده شد SQL Server تنها برای برخی از خطاها تراکنش را بصورت ضمنی/اتوماتیک/خودکار rollback می کند). البته برای این منظور که با بروز هر گونه خطا تراکنش بصورت خودکار rollback شود هم راه حلی وجود دارد که در جای مناسبش توضیح داده خواهد شد.
یک تفاوت مهم دیگر نیز بین COMMIT و ROLLBACK وجود دارد. COMMIT TRANSACTION مقدار @@trancount را یکی کاهش می دهد اما ROLLBACK TRANSACTION همیشه مقدار @@trancount را به صفر نزول می دهد. در مورد تراکنش های تو در تو بعدا توضیح خواهیم داد.

مدیریت تراکنش توسط WHILE

در یک تراکنش که مجموعه ای از عملیات به عنوان یک واحد منطقی کار (logical unit of work) در نظر گرفته می شوند در یک بلاک BEGIN TRAN … COMMIT TRAN قرار دارند. پس باید تمام این دستورات به درستی اجرا شده یا در صورت بروز خطا در یکی از دستورات باید تمام به هنگام سازی هایی که از شروع تراکنش تا جایی که خطا اتفاق افتاده است لغو شده و کنترل ادامه برنامه به بعد از COMMIT TRAN منتقل شود.

پس باید به دنبال مکانیزمی برای پیاده سازی این الگوریتم در مدیریت تراکنش باشیم. ابتدایی ترین روش این است که شما به ازای تک تک عملیات به هنگام سازی برای بررسی خطای احتمالی مقدار متغیر سیستمی @@error را بررسی کنید و در صورتی که این متغیر دارای مقداری غیر از صفر بود تراکنش را roll back نموده و اجرای دستورات را به بعد از دستور COMMIT منتقل کنید.

به مثال زیر توجه کنید. دو دستور UPDATE داخل یک تراکنش قرار گرفته اند (که یکی از آنها کار بروزرسانی شماره تلفن نویسنده با کد ذکر شده را به عهده دارد و دیگری وظیفه ی ویرایش شهر و کشور انتشارات را انجام می دهد این جداول در بانک pubs موجودند):

WHILE (1 = 1)
BEGIN
BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
BREAK
END
UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
BREAK
END
COMMIT TRAN
BREAK
END

روند کار به این شکل است که در صورت بروز خطا تراکنش ROLLBACK شده و کنترل برنامه به بعد از پایان تراکنش منتقل می شود. دستور BREAK در حلقه‌ی WHILE به منظور پایان داده به ادامه اجرای حلقه است. و باعث می شود حلقه به پایان برسد و دستورات بعد از آن (break) نیز اجرا نشود.

مدیریت تراکنش توسط برچسب و GOTO

در این بخش از مقاله مدیریت تراکنس توسط برچسب و GOTO بر شما همراهان گرامی آموزش داده می شود. با ما همراه باشید:

الگوریتم با یک برچسب:

(کد زیر مربوط به یکی از مقالات سایت CodeProject است؛ تنها جهت تسریع در تکمیل مقاله از کدهای موجود استفاده شده است). روش سنتی به این شکل بود که با بروز خطا کنترل اجرای برنامه به یک Label منتقل شده و کار ROLLBACK شدن در آنجا صورت می گرفت. در اینجا چون کد بعد از برچسب در هر حال اجرا می شد چه کنترل برنامه با GOTO به آنجا رسیده باشد چه روال طبیعی خود را طی کرده باشد، باید مقدار تابع  @@error را داخل یک متغیر ریخته و از آن برای بررسی شرط استفاده کنیم. توجه داشته باشید که طول عمر تابع @@error بسیار اندک است به این معنا که بعد از اجرا شدن یک دستور جدید مقدار آن نیز تغییر خواهد کرد.

توجه داشته باشید که در صورت عدم بروز خطا مقدار تابع @@error صفر خواهد بود. پس در صورتی که این مقدار نامساوی با صفر باشد به معنای بروز خطاست.

DECLARE @intErrorCode INT;
BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'
SET @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'
SET @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END

(ایده زیر مربوط به آقای محمد سلیم آبادی است)

تابع @@trancount را به یاد دارید؟ که با اجرای BEGIN TRAN یک مقدار به آن افزوده و با COMMIT شدن یک مقدار از آن کاسته می شد؟ کد فوق را می توان با بررسی این تابع در دستور IF ساده تر نمود. با فرض اینکه هیچ تراکنش فعالی در حال حاضر وجود ندارد BEGIN TRAN باعث می شود مقدار این تابع به ۱ تغییر کند.

اگر تراکنش بدون خطا به پایان برسد هیچ یک از دستورات GOTO اجرا نمی شود در نتیجه دستور COMMIT اجرا خواهد شد و مقدار این تابع را به ۰ بر می گرداند. با بررسی مقدار این تابع می توانیم شرط ROLLBACK را به درستی تعیین کنیم. به این معنا که اگر تراکنش با موفقیت و بدون خطا اجرا شد که COMMIT می شود (و البته مجددا تراکنش commit شده rollback نخواهد شد!) در غیر اینصورت تراکنش فعال را ROLLBACK می کنیم. به این کد توجه کنید:‍‍‍‍

BEGIN TRAN
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'
IF (@@ERROR <> 0) GOTO PROBLEM
UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'
IF (@@ERROR <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@@TRANCOUNT = 1)
BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END

الگوریتم با دو برچسب

(این ایده از کتاب آقای C.J. Date استخراج شده است) می توانیم با کمک دو Lable الگوریتم را ساده تر کنیم به گونه ای که دیگر نیاز به شرط قبل از اجرای دستور ROLLBACK نداریم. به این شکل که در صورت بروز خطا کنترل اجرای برنامه توسط دستور GOTO به فرمان ROLLBACK هدایت می شود و اگر هیچ خطایی رخ نداد دستور COMMIT اجرا شده و از روی دستور ROLLBACK پرش می کند که این کار نیز توسط دستور GOTO صورت می گیرد. یعنی یک Lable قبل و یک Lable بعد از دستور ROLLBACK تعریف می کنیم.

توضیح FlowChart

کل دستوراتی که باید Atomic در نظر گرفته شوند داخل بلاک beging tran… commit tran قرار گرفته اند که در کادر مستطیلی شکل مشخص شده است. منظور از DML همان دستورات دستکاری داده هاست مثل INSERT. اگر دستور با موفقیت به پایان رسید (منظور اجرا بدون خطاست) آنگاه کنترل اجرای کد به دستور بعدی هدایت می شود (این کار بصورت خودکار اجرا می شود چرا که همانطور که شما از من بهتر می دانید کدها سطر به سطر از بالا به پایین به ترتیب اجرا می شوند) در غیر این صورت کنترل اجرا به خارج از بلاک هدایت می شود جایی که دستور ROLLBACK مستقر شده است. حال تصور کنید تمام دستورات به درستی (با موفقیت و بدون بروز هیچ گونه خطایی) اجرا شده اند.

در این صورت کنترل اجرا کد به آخر بلاک یعنی جایی که دستور Commit Tran قرار گرفته است می رسد، بعد از تایید تراکنش دیگر نباید دستور Rollback اجرا شود پس آن را توسط دستور Goto و تعریف یک Lable دیگر که بعد از دستور Rollback وجود دارد دور می زنیم. الگوریتم GOTO با دو Lable را به مراتب ساده تر و خواناتر نسبت به یک Lable خواهید یافت. پیاده سازی آن را به عنوان تمرین به شما می سپارم.

سخن پایانی مدیریت خطای تراکنش در SQL Server

مدیریت تراکنش ها داخل رویه های ذخیره شده ساده تر خواهد بود. چرا که مثل حلقه WHILE دارای دستوری است که توسط آن به اجرای رویه ی ذخیره شده پایان می دهیم یعنی عبارت RETURN. عموما برگرداندن مقدار ۰ به معنای اجرای موفقیت آمیز پروسیجر بوده و مقدار غیر صفر به معنا ناموفق اجرا شدن پروسیجر است. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
AS
BEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)
IF (@@ERROR <> 0)
BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
END
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0)
BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
END
COMMIT TRAN
RETURN 0

مدیریت خطای تراکنش در SQL Server، در این مقاله نحوه خطایابی در تراکنش ها و برطرف کردن آنها را برای شما عزیزان آموزش دادیم. مدیریت خطا در تراکنش‌ها مبحث بسیار مهمی در تراکنش ها است، البته این خطا ها باید مدیریت و برطرف شوند. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

 💡 مطالعه‌های تکمیلی برای درک بهتر موضوع:

• با مهم ترین ابزارهای امنیت SQL Server آشنا شوید
• امنیت بکاپ ها در SQL Server | تهدیدها، راهکارها و استراتژی
• بک‌آپ از SQL Server با Powershell