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

در این مقاله مفاهیم زیر بررسی خواهد شد

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

توجه: مدیریت تراکنش توسط ساختار TRY…CATCH در مقاله بعد بطور مفصل بررسی می شود.

بررسی مفهوم ACID

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

Atomicity

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

Consistency

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

Isolation

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

توجه: این بحث مربوط به همزمانی (concurrency) و قفل گذاری (Locking) می شود که قصد بر این است که در سلسله مقالات بعدی آنها را معرفی کنیم.

Durability

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

توجه: بحث ترمیم تراکنش ها (transaction recovery) مبحث مفصلی است که خود یک فصل از یک کتاب را تشکیل می دهد و در حال حاضر تصمیم بر این نیست که راجب آن مطالبی بیان شود.

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

یک تراکنش 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 را نشان می دهد.

توجه: راجب موضوع همزمانی در سلسه مقالات بعدی به عنوان “همزمانی تراکنش ها” بحث خواهد شد.

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

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

یک تراکنش می تواند با یک منبع کار کند مثل یک database یا چندین منبع مثل چندین database. تراکنش هایی که محدود شده اند به یک منبع local transaction نامیده می شوند و تراکنش هایی که وابسته به چند منبع هستند distributed transaction نامیده می شوند. ما راجب تراکنش های local در ادامه بحث خواهیم نمود.

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

تراکنش های صریح عموما داخل stored procedure ها تعریف می شوند.
همانطور که در مقاله قبل توضیح داده شد، برای اینکه شروع یک تراکنش را اعلام کنیم از عبارت 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 بگویید کدام از دو گزینه را می خواهید.

نکته: اگر یک error شدید و جدی در جریان اجرای یک تراکنش اتفاق افتد، SQL Server تراکنش را بصورت خودکار roll back خواهد نمود. متاسفانه SQL Server تعاریفش را از error های جدی شفاف نساخته است. در مقالات بعدی خطاهایی که در صورت بروز تراکنش را بصورت خودکار rollback می کنند را معرفی خواهم نمود.

در نتیجه این فکر خوبی است که صراحتا 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

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

(کد زیر مربوط به یکی از مقالات سایت 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 خواهید یافت. پیاده سازی آن را به عنوان تمرین به شما می سپارم.

مدیریت تراکنش داخل Stored Procedure

مدیریت تراکنش ها داخل رویه های ذخیره شده ساده تر خواهد بود. چرا که مثل حلقه 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