مدیریت تراکنش و حل تضاد دستورات در SQL Server

حل تضاد دستورات در SQL Server: راهنمای جامع مدیریت تراکنش‌ها

اگر تا به حال از خود پرسیده‌اید که در صورت گنجاندن یک دستور `ROLLBACK` در یک تریگر و سپس انجام `COMMIT` در بچ (batch) فراخواننده چه اتفاقی می‌افتد، پس در مسیر درک دستورات متناقض گام برداشته‌اید.

اما این موضوع تنها به تریگرها محدود نمی‌شود. هر زمان که بخشی از یک بچ، رویه ذخیره‌شده (stored procedure) یا تریگر، دستور صریح یا ضمنی متفاوتی در مورد نحوه اجرای یک تراکنش داشته باشد، ممکن است با دستورات متناقض مواجه شوید.

در حالی که ممکن است درک کلی از نحوه کار تراکنش‌ها داشته باشید، شاید از اینکه SQL Server چگونه ویژگی‌های ACID را حتی زمانی که شما صریحاً تراکنش‌ها را کنترل نمی‌کنید (مثلاً رویه شما فقط یک جدول را با یک تراکنش ضمنی که در صورت موفقیت `COMMIT` و در صورت شکست `ROLLBACK` می‌شود، به‌روزرسانی می‌کند) حفظ می‌کند، آگاه نباشید.

این یک مبحث عمیق است، اما چند مفهوم ساده وجود دارد که می‌توانید برای عبور از بیشتر پیچیدگی‌ها در ذهن داشته باشید.

کارکرد لاگ تراکنش چیست؟

برای درک دستورات متناقض، واقعاً باید بدانید که لاگ تراکنش (Transaction Log) چه کاری انجام می‌دهد. SQL Server (و بیشتر سیستم‌های مدیریت پایگاه داده رابطه‌ای دیگر) صرفاً داده‌ها را در فایل‌های داده شما نمی‌نویسد. در عوض، سوابق تغییرات را در لاگ تراکنش می‌نویسد. در یک نقطه زمانی بعدی، صفحات را از لاگ تراکنش به فایل‌های داده شما می‌نویسد.

این یک مفهوم حیاتی است و اغلب به اشتباه درک می‌شود.

هنگامی که یک دستور DML مانند این را اجرا می‌کنید:

UPDATE MyTable SET MyColumn = 'NewValue' WHERE Id = 1;

SQL Server یک رکورد در لاگ تراکنش می‌نویسد که نشان می‌دهد این به‌روزرسانی انجام شده است. بلافاصله در فایل داده نمی‌نویسد.

اگر مشکلی پیش بیاید (مانند قطعی برق، یا دستور `ROLLBACK`)، SQL Server می‌تواند از لاگ تراکنش برای لغو تغییرات استفاده کند. اگر همه چیز درست پیش برود، SQL Server در نهایت تغییرات `COMMIT` شده را از لاگ به فایل‌های داده می‌نویسد.

نقش @@TRANCOUNT

`@@TRANCOUNT` یک متغیر سراسری است که اغلب نادیده گرفته می‌شود و سطح تو در توی تراکنش (transaction nesting level) فعلی را به شما می‌گوید. هر بار که یک دستور `BEGIN TRANSACTION` را اجرا می‌کنید، `@@TRANCOUNT` یک واحد افزایش می‌یابد. هر بار که یک دستور `COMMIT TRANSACTION` یا `ROLLBACK TRANSACTION` را اجرا می‌کنید، `@@TRANCOUNT` یک واحد کاهش می‌یابد.

مهم است که توجه داشته باشید `@@TRANCOUNT` فقط تراکنش‌های صریح را منعکس می‌کند. تراکنش‌های ضمنی، مانند مواردی که توسط یک دستور DML واحد آغاز می‌شوند، بر `@@TRANCOUNT` تأثیری ندارند.

این مثال را در نظر بگیرید:

BEGIN TRANSACTION;
  UPDATE MyTable SET MyColumn = 'Value1' WHERE Id = 1;
  BEGIN TRANSACTION;
    UPDATE MyTable SET MyColumn = 'Value2' WHERE Id = 2;
  COMMIT TRANSACTION;
COMMIT TRANSACTION;

در این سناریو، `@@TRANCOUNT` پس از اولین `BEGIN TRANSACTION`، مقدار ۱ و پس از دومین `BEGIN TRANSACTION`، مقدار ۲ خواهد بود. پس از اولین `COMMIT TRANSACTION`، مقدار ۱ و پس از دومین `COMMIT TRANSACTION`، مقدار ۰ خواهد بود.

اگر در هر نقطه‌ای یک `ROLLBACK TRANSACTION` اجرا شود، تمام تراکنش‌های تو در تو را تا بیرونی‌ترین `BEGIN TRANSACTION` بازگردانی می‌کند و `@@TRANCOUNT` را به ۰ تنظیم می‌کند.

نقطه بازگشت (Savepoints)

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

در اینجا مثالی از استفاده از نقطه بازگشت آورده شده است:

BEGIN TRANSACTION;
  UPDATE MyTable SET MyColumn = 'Value1' WHERE Id = 1;
  SAVE TRANSACTION MySavepoint;
  UPDATE MyTable SET MyColumn = 'Value2' WHERE Id = 2;
  IF @@ERROR  0
    ROLLBACK TRANSACTION MySavepoint;
  ELSE
    COMMIT TRANSACTION;

در این مثال، اگر به‌روزرسانی دوم با شکست مواجه شود، ما فقط به `MySavepoint` بازگردانده می‌شویم و به‌روزرسانی اول حفظ می‌شود. با این حال، `@@TRANCOUNT` تحت تأثیر `ROLLBACK TRANSACTION MySavepoint` قرار نمی‌گیرد. هنوز برای تکمیل کامل تراکنش به یک `COMMIT TRANSACTION` یا `ROLLBACK TRANSACTION` برای بیرونی‌ترین تراکنش نیاز دارد.

دستورات متناقض در عمل

اکنون، به هسته دستورات متناقض می‌رسیم. یک تریگر را در نظر بگیرید که یک `ROLLBACK TRANSACTION` صادر می‌کند، در حالی که بچ فراخواننده یک `COMMIT TRANSACTION` صادر می‌کند.

چه اتفاقی می‌افتد؟
SQL Server ابتدا تریگر را پردازش می‌کند. اگر تریگر `ROLLBACK TRANSACTION` را اجرا کند، *تمام* تراکنش‌های باز را تا بیرونی‌ترین `BEGIN TRANSACTION` بازگردانی می‌کند و `@@TRANCOUNT` را به ۰ تنظیم می‌کند. سپس بچی که تریگر را فراخوانی کرده است، تلاش می‌کند `COMMIT TRANSACTION` را انجام دهد، اما چون `@@TRANCOUNT` قبلاً ۰ است، این `COMMIT TRANSACTION` اساساً کاری انجام نمی‌دهد زیرا هیچ تراکنش بازی برای `COMMIT` وجود ندارد.

این یک منبع رایج سردرگمی و رفتار غیرمنتظره است. بسیار مهم است که بدانید `ROLLBACK TRANSACTION` (بدون یک نقطه بازگشت) همیشه کل تراکنش بیرونی را بازگردانی می‌کند.

تراکنش‌های مستقل (Autonomous Transactions)؟

گاهی اوقات افراد در مورد “تراکنش‌های مستقل” در SQL Server می‌پرسند، مشابه آنچه ممکن است در Oracle یافت شود. SQL Server مفهوم بومی تراکنش‌های مستقل را ندارد.

با این حال، می‌توانید شکلی از تراکنش مستقل را با استفاده از Distributed Transaction Coordinator (DTC) SQL Server یا با فراخوانی یک رویه ذخیره‌شده جداگانه از طریق یک سرور لینک شده (linked server) یا حتی با استفاده از Service Broker شبیه‌سازی کنید. اینها مباحث پیشرفته‌ای هستند و عموماً فقط در سناریوهای خاصی استفاده می‌شوند که جداسازی یک تراکنش کاملاً حیاتی است و نمی‌توان آن را از طریق ابزارهای دیگر به دست آورد.

بهترین شیوه‌ها برای مدیریت تراکنش‌ها

برای جلوگیری از مشکلات مربوط به دستورات متناقض، این بهترین شیوه‌ها را در نظر بگیرید:

• از صدور `BEGIN TRANSACTION`, `COMMIT TRANSACTION`, یا `ROLLBACK TRANSACTION` در داخل تریگرها خودداری کنید. تریگرها معمولاً باید در بستر تراکنش فراخواننده عمل کنند. اگر تریگر نیاز به جلوگیری از یک تغییر دارد، باید خطا ایجاد کند که باعث می‌شود تراکنش فراخواننده `ROLLBACK` شود.
• با کنترل تراکنش خود صریح باشید. اگر یک تراکنش را شروع می‌کنید، مطمئن شوید که آن را `COMMIT` یا `ROLLBACK` می‌کنید. برای عملیات پیچیده صرفاً به تراکنش‌های ضمنی تکیه نکنید.
• قبل از انجام دستورات کنترل تراکنش، `@@TRANCOUNT` را بررسی کنید. این می‌تواند به شما کمک کند تا وضعیت فعلی تراکنش‌ها را درک کرده و از `COMMIT` یا `ROLLBACK`های ناخواسته جلوگیری کنید.
• از بلوک‌های `TRY…CATCH` برای مدیریت خطا و تراکنش استفاده کنید. این به شما امکان می‌دهد تا خطاها را به طور زیبا مدیریت کرده و اطمینان حاصل کنید که تراکنش‌ها به درستی `COMMIT` یا `ROLLBACK` می‌شوند.
• محدوده `ROLLBACK TRANSACTION` را درک کنید. به یاد داشته باشید که بدون یک نقطه بازگشت، `ROLLBACK TRANSACTION` همیشه کل تراکنش بیرونی را بازگردانی می‌کند.

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