مدیریت پیشرفته تراکنش ها(Transactions) در SQL Server راهنمای جامع

مدیریت پیشرفته تراکنش‌ها(Transactions) در SQL Server: راهنمای جامع

در دنیای پایگاه داده، تراکنش‌ها (Transactions) یکی از مفاهیم اساسی هستند که تضمین‌کننده ثبات و یکپارچگی داده‌ها می‌باشند. یک تراکنش مجموعه‌ای از عملیات است که به عنوان یک واحد منطقی کار می‌کنند؛ یا همه آن‌ها با هم موفق می‌شوند (commit می‌شوند) یا هیچ‌کدام اتفاق نمی‌افتند (rollback می‌شوند). این رویکرد “همه یا هیچ” برای حفظ قابلیت اطمینان و دقت داده‌ها بسیار حیاتی است.

چهار ویژگی کلیدی وجود دارد که یک تراکنش واقعی باید آن‌ها را دارا باشد که به اختصار با عنوان ACID شناخته می‌شوند:

1. اتمی بودن (Atomicity): به این معنی است که تراکنش یک واحد منفرد و تجزیه‌ناپذیر از کار است. یا همه عملیات درون تراکنش با موفقیت انجام می‌شوند، یا هیچ‌کدام انجام نمی‌شوند.
2. سازگاری (Consistency): تضمین می‌کند که داده‌ها پس از اجرای تراکنش از یک حالت معتبر به حالت معتبر دیگری منتقل می‌شوند. یعنی تراکنش نباید یکپارچگی داده‌ها را نقض کند.
3. جداسازی (Isolation): تضمین می‌کند که اجرای همزمان چندین تراکنش، منجر به حالتی می‌شود که گویی تراکنش‌ها به صورت متوالی اجرا شده‌اند. این ویژگی از تداخل تراکنش‌ها با یکدیگر جلوگیری می‌کند.
4. پایایی (Durability): تضمین می‌کند که پس از تأیید (commit) یک تراکنش، تغییرات آن دائمی هستند و حتی در صورت خرابی سیستم نیز از بین نمی‌روند.

با درک این ویژگی‌ها، می‌توانیم عمیق‌تر به نحوه مدیریت تراکنش‌ها در SQL Server بپردازیم.

انواع تراکنش‌ها در SQL Server

SQL Server از سه نوع تراکنش پشتیبانی می‌کند:

1. تراکنش‌های خودکار (Autocommit Transactions): این رایج‌ترین نوع تراکنش است. هر عبارت T-SQL به عنوان یک تراکنش مستقل در نظر گرفته می‌شود و بلافاصله پس از اجرا، در صورت موفقیت، commit و در صورت شکست، rollback می‌شود. شما نیازی به تعریف صریح `BEGIN TRANSACTION` برای این نوع تراکنش‌ها ندارید.
2. تراکنش‌های صریح (Explicit Transactions): این تراکنش‌ها به وضوح با دستور `BEGIN TRANSACTION` آغاز شده و با `COMMIT TRANSACTION` یا `ROLLBACK TRANSACTION` پایان می‌یابند. این به شما کنترل دقیقی بر روی عملیاتی که می‌خواهند به عنوان یک واحد منطقی اجرا شوند، می‌دهد.
3. تراکنش‌های ضمنی (Implicit Transactions): زمانی اتفاق می‌افتند که گزینه `SET IMPLICIT_TRANSACTIONS ON` تنظیم شده باشد. در این حالت، SQL Server به طور خودکار یک تراکنش جدید را پس از هر `COMMIT` یا `ROLLBACK` شروع می‌کند و شما فقط باید آن را به صورت صریح `COMMIT` یا `ROLLBACK` کنید.

تراکنش‌های صریح و دستورات کلیدی

تراکنش‌های صریح ابزار قدرتمندی برای کنترل جریان کار و اطمینان از صحت داده‌ها در سناریوهای پیچیده هستند. دستورات اصلی برای مدیریت این تراکنش‌ها عبارتند از:

BEGIN TRANSACTION: یک تراکنش جدید را آغاز می‌کند.

BEGIN TRANSACTION;

این دستور نقطه شروع یک واحد کاری منطقی را مشخص می‌کند. تمام دستورات T-SQL بعدی تا زمان `COMMIT` یا `ROLLBACK` بخشی از این تراکنش خواهند بود.

COMMIT TRANSACTION: تغییرات انجام شده در تراکنش جاری را دائمی می‌کند.

COMMIT TRANSACTION;

وقتی این دستور اجرا می‌شود، تمام تغییرات اعمال شده از زمان `BEGIN TRANSACTION` به طور دائم در پایگاه داده ذخیره می‌شوند.

ROLLBACK TRANSACTION: تمام تغییرات انجام شده در تراکنش جاری را لغو می‌کند.

ROLLBACK TRANSACTION;

اگر مشکلی پیش بیاید یا تصمیم بگیرید که تغییرات نباید اعمال شوند، این دستور پایگاه داده را به حالتی که قبل از `BEGIN TRANSACTION` بود بازمی‌گرداند.

مثال عملی: فرض کنید می‌خواهیم موجودی یک کالا را به‌روزرسانی کنیم و سپس یک رکورد در جدول تاریخچه اضافه کنیم. هر دو عملیات باید با هم موفق شوند.

BEGIN TRANSACTION;

UPDATE Products
SET Quantity = Quantity - 1
WHERE ProductID = 123;

INSERT INTO OrderHistory (ProductID, QuantityChanged, ChangeDate)
VALUES (123, -1, GETDATE());

-- اگر همه چیز خوب پیش رفت، تغییرات را دائمی کن
COMMIT TRANSACTION;

اگر در حین `UPDATE` یا `INSERT` خطایی رخ دهد، می‌توانیم از `ROLLBACK TRANSACTION` برای لغو همه تغییرات استفاده کنیم:

BEGIN TRANSACTION;

UPDATE Products
SET Quantity = Quantity - 1
WHERE ProductID = 123;

-- فرض کنید این INSERT با مشکل مواجه شود (مثلاً نقض کلید خارجی)
INSERT INTO OrderHistory (ProductID, QuantityChanged, ChangeDate)
VALUES (999, -1, GETDATE()); -- فرض کنید ProductID 999 وجود ندارد

-- در صورت بروز خطا، تغییرات را برگردان
ROLLBACK TRANSACTION;

آشنایی با @@TRANCOUNT

تابع سیستمی `@@TRANCOUNT` تعداد تراکنش‌های `BEGIN TRANSACTION` فعال را برای نشست جاری بازمی‌گرداند. این یک ابزار مهم برای ردیابی وضعیت تراکنش در کد شماست.

SELECT @@TRANCOUNT;

نحوه عملکرد `@@TRANCOUNT`:

. هر `BEGIN TRANSACTION` باعث افزایش `@@TRANCOUNT` به اندازه 1 می‌شود.
. هر `COMMIT TRANSACTION` باعث کاهش `@@TRANCOUNT` به اندازه 1 می‌شود.
. هر `ROLLBACK TRANSACTION` (بدون تعیین نقطه ذخیره) باعث تنظیم `@@TRANCOUNT` به 0 می‌شود. یعنی تمام تراکنش‌های باز را لغو می‌کند.

مثال برای درک `@@TRANCOUNT`:

-- ابتدا، تعداد تراکنش‌ها باید 0 باشد
SELECT 'شروع: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

BEGIN TRANSACTION;
SELECT 'بعد از BEGIN TRAN 1: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

BEGIN TRANSACTION;
SELECT 'بعد از BEGIN TRAN 2: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

COMMIT TRANSACTION;
SELECT 'بعد از COMMIT TRAN 1: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

ROLLBACK TRANSACTION; -- این ROLLBACK همه تراکنش‌ها را لغو می‌کند
SELECT 'بعد از ROLLBACK TRAN نهایی: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

خروجی این کد به ترتیب 0، 1، 2، 1، 0 خواهد بود. این نشان می‌دهد که چگونه `@@TRANCOUNT` با هر دستور تراکنش تغییر می‌کند.

نقاط ذخیره (Savepoints)

نقاط ذخیره به شما این امکان را می‌دهند که بخشی از یک تراکنش را به عقب برگردانید، بدون اینکه کل تراکنش را لغو کنید. این قابلیت برای مدیریت خطاهای جزئی یا اجرای منطق شرطی در داخل یک تراکنش بزرگ بسیار مفید است.

برای ایجاد یک نقطه ذخیره، از دستور `SAVE TRANSACTION` استفاده می‌کنیم:

SAVE TRANSACTION SavepointName;

برای برگرداندن تراکنش به یک نقطه ذخیره خاص، از `ROLLBACK TRANSACTION` همراه با نام نقطه ذخیره استفاده می‌کنیم:

ROLLBACK TRANSACTION SavepointName;

مثال با Savepoints:

BEGIN TRANSACTION;
-- تغییرات اولیه
INSERT INTO MyTable (Value) VALUES ('Initial');

SAVE TRANSACTION FirstSavepoint;
-- تغییرات مرحله اول
INSERT INTO MyTable (Value) VALUES ('Step 1');

SAVE TRANSACTION SecondSavepoint;
-- تغییرات مرحله دوم
INSERT INTO MyTable (Value) VALUES ('Step 2');

-- فرض کنید خطایی در مرحله دوم رخ داده و می‌خواهیم فقط آن را برگردانیم
ROLLBACK TRANSACTION SecondSavepoint;
-- حالا MyTable شامل 'Initial' و 'Step 1' است، 'Step 2' حذف شده.

-- اگر بعداً بخواهیم همه چیز را از FirstSavepoint به بعد لغو کنیم
-- ROLLBACK TRANSACTION FirstSavepoint;

COMMIT TRANSACTION; -- تمام تغییرات باقی مانده را نهایی می‌کند

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

تراکنش‌های تو در تو (Nested Transactions)

در SQL Server، تراکنش‌های تو در تو به این صورت عمل می‌کنند که هر `BEGIN TRANSACTION` فقط مقدار `@@TRANCOUNT` را افزایش می‌دهد. یک `COMMIT TRANSACTION` تنها زمانی تغییرات را به طور دائمی ذخیره می‌کند که `@@TRANCOUNT` به 0 برسد (یعنی بیرونی‌ترین تراکنش commit شود).

هر `ROLLBACK TRANSACTION` (بدون تعیین نقطه ذخیره) تمام تراکنش‌های باز را لغو کرده و `@@TRANCOUNT` را به 0 بازمی‌گرداند، صرف نظر از سطح تو در تو بودن. این رفتار بسیار مهم است زیرا به این معنی است که حتی یک `ROLLBACK` داخلی می‌تواند کل تراکنش را لغو کند.

مثال تراکنش‌های تو در تو:

SELECT 'شروع: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

BEGIN TRANSACTION; -- سطح 1
SELECT 'بعد از BEGIN TRAN 1: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

    BEGIN TRANSACTION; -- سطح 2
    SELECT 'بعد از BEGIN TRAN 2: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

        BEGIN TRANSACTION; -- سطح 3
        SELECT 'بعد از BEGIN TRAN 3: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

        -- یک ROLLBACK در اینجا کل تراکنش را لغو می‌کند
        -- ROLLBACK TRANSACTION;

    COMMIT TRANSACTION; -- این COMMIT صرفاً @@TRANCOUNT را کاهش می‌دهد
    SELECT 'بعد از COMMIT TRAN 2: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

COMMIT TRANSACTION; -- این COMMIT نهایی، تغییرات را دائمی می‌کند
SELECT 'بعد از COMMIT TRAN 1 نهایی: ' + CAST(@@TRANCOUNT AS NVARCHAR(10));

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