۱۴۰۴/۰۷/۰۳ Nebular

تراکنش ها در SQL Server

تراکنش (Transaction) — خلاصهٔ ساده

تراکنش یعنی یک واحد منطقی از عملیات که یا همه اجرا می‌شوند یا هیچ‌کدام آن‌ها اجرا نمی‌شود — یعنی «همه یا هیچ» (atomic).
مثال واقعی: انتقال پول از حساب A به حساب B — یا پول از A کم و به B اضافه می‌شود، یا هیچ‌کدام انجام نمی‌شود.

۱ — خصوصیات ACID (چه انتظاری از تراکنش داریم)

Atomicity (اتمیک بودن): یا همهٔ عملیات داخل تراکنش انجام می‌شوند یا هیچ‌کدام (COMMIT / ROLLBACK).
Consistency (همگنی/قابلیت‌سازگاری): تراکنش دیتابیس را از یک وضعیت معتبر به وضعیت دیگر معتبر منتقل می‌کند (قیدها و کانستِرِینت‌ها رعایت می‌شوند).
Isolation (جداسازی): اجرای هم‌زمان تراکنش‌ها نباید همدیگر را به‌صورت غیرمنتظره تحت‌تأثیر قرار دهد (از طریق isolation levelها کنترل می‌شود).
Durability (پایداری): پس از COMMIT، تغییرات دائمی و قابل بازیابی‌اند (ذخیره‌شدن در transaction log و پس از آن persistence).

۲ — حالت‌های تراکنش در SQL Server (مفاهیم و دستورات)

حالت پیش‌فرض (Autocommit): هر دستور یک تراکنش مجزا است و بلافاصله بعد از اجرا commit می‌شود.
Explicit transaction (متداول‌ترین): کنترل صریح:

BEGIN TRANSACTION;
-- چند دستور DML مثل INSERT/UPDATE/DELETE
COMMIT; -- یا در صورت خطا ROLLBACK;

Implicit transactions: با SET IMPLICIT_TRANSACTIONS ON هر دستور منتخب (مثل INSERT/UPDATE/DELETE/SELECT INTO و …) یک تراکنش جدید شروع می‌کند ولی COMMIT باید دستی انجام شود. (معمولاً استفاده نمی‌شود مگر نیاز خاص)
Savepoint / SAVE TRANSACTION: نقطۀ میانی برای rollback جزئی:

SAVE TRANSACTION savepoint_name;
-- کارهای بعدی
ROLLBACK TRANSACTION savepoint_name; -- فقط تا آن نقطه برمی‌گردد

Nested transactions: BEGIN TRAN چندبار باعث افزایش @@TRANCOUNT می‌شود؛ COMMITها به‌تعداد کاهش می‌یابد ولی تنها COMMIT بیرونی (وقتی @@TRANCOUNT به صفر برسد) واقعاً پایدارسازی را انجام می‌دهد. ROLLBACK بدون نام معمولاً تمام تراکنش را به‌طور کامل برمی‌گرداند و @@TRANCOUNT را صفر می‌کند.
مشاهده وضعیت:
- @@TRANCOUNT → تعداد تراکنش‌های باز در session.
- XACT_STATE() → وضعیت تراکنش (1 = تراکنش فعال، 0 = تراکنش وجود ندارد، -1 = تراکنش خراب/uncommittable که فقط ROLLBACK لازم است).

۳ — الگوی استاندارد مدیریت خطا در تراکنش (TRY / CATCH)

الگوی پیشنهادی برای اطمینان از rollback در صورت خطا:

BEGIN TRY
    BEGIN TRANSACTION;

    -- دستورات DML
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    -- ثبت خطا یا دوباره پرتاب
    THROW;
END CATCH;

توضیح مختصر:

اگر خطا رخ دهد وارد CATCH می‌شویم، با XACT_STATE() بررسی می‌کنیم که تراکنش قابل rollback هست یا نه؛ سپس rollback می‌کنیم و خطا را دوباره پرتاب می‌کنیم.

۴ — Isolation Levels (سطوح جداسازی) و اثرات آنها

SQL Server چند سطح جداسازی مهم دارد که رفتار تداخل تراکنش‌ها را مشخص می‌کنند:

READ UNCOMMITTED
- کمترین جداسازی؛ dirty reads ممکن است — تراکنش می‌تواند داده‌هایی را بخواند که هنوز commit نشده‌اند.
- معادل NOLOCK hint.
READ COMMITTED (پیش‌فرض)
- جلوگیری از dirty reads؛ ولی non-repeatable reads و phantom reads ممکن است.
- در SQL Server می‌توان آن را با row-versioning تبدیل کرد (READ_COMMITTED_SNAPSHOT).
REPEATABLE READ
- جلوی non-repeatable reads را می‌گیرد (همان ردیف‌ها تکراراً خوانده شوند همان مقادیر را نشان می‌دهند)؛ اما phantomها ممکن هستند.
SERIALIZABLE
- بالاترین سطح؛ جلوی phantom و non-repeatable و dirty را می‌گیرد — به‌صورت موثر تراکنش‌ها را شبیه اجرای سریالی می‌کند (احتمال قفل‌گذاری و بلوکه زیاد).
SNAPSHOT (Row-versioning)
- با فعال‌سازی در سطح دیتابیس (ALLOW_SNAPSHOT_ISOLATION ON) و SET TRANSACTION ISOLATION LEVEL SNAPSHOT خواندن مبتنی بر نسخهٔ لحظه‌ای (statement-level) است؛ تراکنش‌ها خواندن بدون قفل‌گذاری انجام می‌دهند و از سرچشمه‌ی نسخه استفاده می‌کنند. نیاز به فعال‌سازی و نگهداری row versions در tempdb.

نکته: انتخاب isolation مناسب بین کارایی و درستی داده توازن برقرار می‌کند.

۵ — اثرات تراکنش روی لاگ و کارایی

هر تغییر داخل تراکنش در Transaction Log ثبت می‌شود.
اگر تراکنش طولانی باشد (مدت زمان بالا یا تعداد زیاد تغییرات)، لاگ بزرگ می‌شود و ممکن است مانع truncate شدن لاگ شود → مصرف دیسک زیاد.
تراکنش طولانی = قفل‌های طولانی = blocking و احتمال deadlock افزایش می‌یابد.
قاعدهٔ کلیدی: تراکنش‌ها را کوتاه نگه دارید — فقط کارهای ضروری را داخل تراکنش انجام دهید.

۶ — مثال‌های عملی و تریس مرحله‌به‌مرحله

مثال A — انتقال وجه (نمونهٔ کلاسیک Atomicity)

هدف: 100 واحد از AccountA به AccountB منتقل شود.

کد:

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE AccountID = 1;  -- Account A

    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2;  -- Account B

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW; -- بازپرتاب خطا
END CATCH;

تریس (مرحله‌به‌مرحله):

BEGIN TRANSACTION → تراکنش باز می‌شود، @@TRANCOUNT = 1.
UPDATE حساب A → مقدار در حافظه و لاگ نوشته می‌شود، هنوز commit نشده (دیگران بسته به isolation ممکن است نتوانند مقادیر جدید را ببینند).
UPDATE حساب B → انجام می‌شود.
COMMIT TRANSACTION → لاگ کامل flushed و تغییرات پایداریافته؛ @@TRANCOUNT = 0.
اگر در هر مرحله خطا رخ دهد → وارد CATCH می‌شویم و ROLLBACK اجرا می‌شود؛ هیچ‌کدام از دو UPDATE اثر نمی‌گذارند.

مثال B — استفاده از SAVEPOINT و rollback جزئی

هدف: بخشی از عملیات را برگردانیم ولی بقیه را نگه داریم.

کد:

BEGIN TRANSACTION;

-- کاری انجام می‌دهیم
INSERT INTO Orders (...) VALUES (...);

SAVE TRANSACTION sp1;

-- کاری دوم (ممکن است خطا کند)
INSERT INTO OrderDetails (...) VALUES (...);

-- فرضاً خطا رخ داد، بخشی را برگردانیم
ROLLBACK TRANSACTION sp1;

-- ادامهٔ کار و در نهایت commit
COMMIT TRANSACTION;

تریس:

BEGIN → @@TRANCOUNT = 1
INSERT Orders → لاگ می‌شود
SAVE TRANSACTION sp1 → savepoint ساخته می‌شود
INSERT OrderDetails → فرضاً خطا → ROLLBACK TRANSACTION sp1 → همه تغییراتی که بعد از sp1 انجام شده برمی‌گردد، اما تغییرات قبل از sp1 (مثل INSERT Orders) باقی می‌ماند.
COMMIT → تغییرات قبل از sp1 پایدار می‌شوند.

توجه: در SQL Server ROLLBACK TRANSACTION savepoint_name باعث بازگشت به آن savepoint می‌شود، و تراکنش کلی هنوز باز است.

مثال C — الگوی TRY/CATCH با XACT_STATE

BEGIN TRY
    BEGIN TRANSACTION;

    -- اعمالی که ممکن است خطا ایجاد کنند
    UPDATE ...;
    INSERT ...;

    COMMIT;
END TRY
BEGIN CATCH
    -- اگر تراکنش قابل commit نیست (مثلاً deadlock یا severe error)
    IF XACT_STATE() = -1
    BEGIN
        -- تراکنش غیرقابل‌اعتماد است؛ فقط rollback
        ROLLBACK;
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        -- تراکنش سالم ولی خطا وجود دارد؛ rollback و شاید retry
        ROLLBACK;
    END

    -- ثبت خطا
    DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
    RAISERROR(@ErrMsg, 16, 1);
END CATCH;

نکته: XACT_STATE() کمک می‌کند تصمیم دقیقی بگیریم که rollback کافی است یا نه.

مثال D — دمو برای dirty read (نمایش تأثیر isolation)

فرض دو session (A و B):

Session A:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN;
SELECT Balance FROM Accounts WHERE AccountID = 1; -- ممکن است مقدار نا‌پایدار ببیند
-- بدون commit یا rollback فعلاً

Session B:

BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1; -- هنوز commit نشده
-- بعد از مدتی:
ROLLBACK; -- یا COMMIT

با READ UNCOMMITTED، Session A ممکن است مقدار قبل از rollback را بخواند — این dirty read است.
اگر Session B در نهایت ROLLBACK کند، Session A دادهٔ خوانده‌شده نادرست بوده است.

۷ — خطاهای رایج و نکات جلوگیری

تراکنش‌های طولانی → مشکل لاگ بزرگ، blocking، و deadlock.
→ راهکار: تراکنش کوتاه، ایندکس مناسب، تقسیم کار.
عدم استفاده از TRY/CATCH → ممکن است تراکنش باز بماند.
→ همیشه الگوی TRY/CATCH را برای تراکنش‌های تغییر دهنده استفاده کنید.
استفادهٔ نامناسب از isolation بالا (مثل SERIALIZABLE) → کاهش concurrency و افزایش قفل‌ها.
→ فقط وقتی لازم است استفاده کنید.
فراموش کردن COMMIT / ROLLBACK در حالت IMPLICIT TRANSACTIONS → باعث تراکنش باز و مشکلات لاگ می‌شود.

۸ — بهترین شیوه‌ها (Best Practices)

تراکنش‌ها را کوتاه نگه دارید — فقط کد ضروری داخل تراکنش باشد.
از TRY/CATCH و XACT_STATE() استفاده کنید تا در صورت خطا تراکنش به‌درستی rolled-back شود.
قبل از افزایش isolation به سطوح بالاتر، تأثیر آن بر کارایی را تست کنید.
از SAVE TRANSACTION برای نقاط برگشت منطقی در تراکنش‌های چندمرحله‌ای استفاده کنید.
از snapshot isolation یا READ_COMMITTED_SNAPSHOT برای کاهش قفل‌گذاری و افزایش concurrency استفاده کنید (پس از تست و فعال‌سازی در سطح دیتابیس).
از transactions‌ طولانی که شامل تعامل کاربر یا IO سنگین هستند (مثل آپلود فایل) خودداری کنید.
برای عملیات توزیع‌شده حواستان به MSDTC و پیچیدگی‌های مرتبط باشد.

۹ — چک‌لیست سریع (دستورات پراهمیت)

BEGIN TRANSACTION; — شروع تراکنش
COMMIT TRANSACTION; — پایدارسازی
ROLLBACK TRANSACTION; — بازگشت کلی
SAVE TRANSACTION name; — ایجاد savepoint
ROLLBACK TRANSACTION name; — بازگشت تا savepoint
@@TRANCOUNT — تعداد تراکنش‌های باز
XACT_STATE() — وضعیت تراکنش در catch
SET TRANSACTION ISOLATION LEVEL ... — تعیین isolation

مثال های بیشتر

چند سناریوی واقعی تعریف می‌کنم (همه در حوزهٔ مالی/حساب‌ها چون ملموس و واضح‌اند)، برای هر سناریو کدی که از تراکنش استفاده می‌کند می‌نویسم، سپس خط‌به‌خط تریس (trace) می‌کنم تا دقیقاً بفهمی چه اتفاقی می‌افتد و چرا از چه نوع تراکنشی استفاده کردیم.
هر سناریو مستقل است و با جدول نمونهٔ Accounts شروع می‌کنیم (تا شبیه‌سازی ساده و قابل فهم باشد).

آماده‌سازی مشترک (تعریف جدول و داده نمونه)

این بلوک را یک‌بار اجرا کن تا سناریوها روی یک دادهٔ یکسان کار کنند.

-- جدول نمونه
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    AccountHolder NVARCHAR(50),
    Balance DECIMAL(18,2)
);

-- دادهٔ اولیه
TRUNCATE TABLE Accounts; -- اگر جدول از قبل وجود داشت پاک کن
INSERT INTO Accounts (AccountID, AccountHolder, Balance) VALUES
(1, 'Ali', 1000.00),
(2, 'Sara', 500.00),
(3, 'Reza', 2000.00);

بعد از اجرای بالا وضعیت اولیه:

Account 1 (Ali) = 1000.00
Account 2 (Sara) = 500.00
Account 3 (Reza) = 2000.00

سناریو 1 — انتقال ساده (Atomicity: BEGIN TRAN / COMMIT)

شرح: انتقال 200 از Ali (1) به Sara (2). یا کامل انجام می‌شود یا هیچ‌چیز تغییر نمی‌کند.

کد:

BEGIN TRAN;

UPDATE Accounts
SET Balance = Balance - 200
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 200
WHERE AccountID = 2;

COMMIT;

تریس (مرحله به مرحله):

BEGIN TRAN → تراکنش باز می‌شود، @@TRANCOUNT = 1.
UPDATE حساب 1 → محاسبه و لاگ تغییر در transaction log (در حالت موقت). حساب 1 موقتاً = 800.00.
UPDATE حساب 2 → موقتاً = 700.00.
COMMIT → لاگ flush می‌شود و تغییرات پایدار می‌شوند؛ @@TRANCOUNT = 0.

وضعیت نهایی:

Ali = 800.00, Sara = 700.00, Reza = 2000.00

چرا تراکنش: اگر در میان کار خطا داشتیم باید هر دو UPDATE با هم برگشت بخورند، در غیر این صورت پول از بین می‌رفت.

سناریو 2 — انتقال با بررسی موجودی (TRY/CATCH و ROLLBACK)

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

کد:

BEGIN TRY
    BEGIN TRAN;

    -- چک موجودی
    DECLARE @fromBalance DECIMAL(18,2);
    SELECT @fromBalance = Balance FROM Accounts WHERE AccountID = 1;

    IF @fromBalance < 200
    BEGIN
        THROW 50000, 'Insufficient funds', 1;
    END

    UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 2;

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;
    DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
    PRINT 'Transaction rolled back: ' + @msg;
END CATCH;

تریس:

فرض اول: Ali = 1000 (کافی) → انتقال انجام و commit می‌شود.
فرض دوم (اگر مقدار خواهان بیشتر از 1000 بود): THROW اجرا می‌شود → وارد CATCH → ROLLBACK → هیچ تغییری باقی نمی‌ماند.

نکته: استفاده از XACT_STATE() برای تشخیص وضعیت تراکنش در CATCH مفید است.

سناریو 3 — استفاده از SAVEPOINT (بازگردانی جزئی)

شرح: دو عملیات انتقال متوالی انجام می‌دهیم؛ اگر دومین انتقال مشکل داشت فقط آن را برمی‌گردانیم اما اولین انتقال پابرجا بماند.

کد:

BEGIN TRAN;

-- عملیات اول: انتقال 100 از Ali به Sara
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- ایجاد savepoint
SAVE TRAN Save1;

-- عملیات دوم: انتقال 500 از Ali به Reza (ممکن است به خطا بخورد)
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 3;

-- فرض کنیم اینجا تصمیم می‌گیریم که عملیات دوم اشتباه بوده:
ROLLBACK TRAN Save1;

COMMIT;

تریس:

ابتدا: Ali=1000, Sara=500, Reza=2000
بعد از عملیات اول: Ali=900, Sara=600, Reza=2000
SAVE TRAN Save1 ساخته می‌شود.
عملیات دوم انجام می‌شود (موقت): Ali=400, Reza=2500
ROLLBACK TRAN Save1 → همهٔ تغییرات بعد از Save1 لغو می‌شود؛ برمی‌گردیم به وضعیت بعد از عملیات اول: Ali=900, Sara=600, Reza=2000
COMMIT → وضعیت نهایی ذخیره می‌شود.

وضعیت نهایی: Ali=900, Sara=600, Reza=2000

چرا مفید است: وقتی چند مرحله داریم و می‌خواهیم انتخاب کنیم کدام مرحله‌ها را نگه داریم.

سناریو 4 — Nested Transactions (تعداد تراکنش‌ها و COMMIT داخلی)

شرح: نشان می‌دهیم که COMMIT داخلی فقط شمارنده تراکنش را کم می‌کند؛ عمل نهایی فقط وقتی رخ می‌دهد که تراکنش بیرونی commit شود.

کد:

-- نمایش مقدار @@TRANCOUNT برای آموزش
PRINT 'Before any transaction: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

BEGIN TRAN OuterTran;
PRINT 'After BEGIN OuterTran: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

    UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1;

    BEGIN TRAN InnerTran;
    PRINT 'After BEGIN InnerTran: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

        UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 2;

    COMMIT TRAN InnerTran;
    PRINT 'After COMMIT InnerTran: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

COMMIT TRAN OuterTran;
PRINT 'After COMMIT OuterTran: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

تریس (مخرّن شده با مقادیر):

ابتدا @@TRANCOUNT = 0
بعد BEGIN TRAN OuterTran → @@TRANCOUNT = 1
بعد BEGIN TRAN InnerTran → @@TRANCOUNT = 2
بعد COMMIT TRAN InnerTran → @@TRANCOUNT = 1 (تغییرات هنوز به صورت واقعی نوشته نشده چون تراکنش بیرونی باز است)
بعد COMMIT TRAN OuterTran → @@TRANCOUNT = 0 و تغییرات نهایی می‌شوند.

نکته: اگر در هر نقطه‌ای ROLLBACK کلی انجام شود، تمامی سطوح تراکنش به صفر می‌رسد و همهٔ تغییرات برگشت می‌خورند.

سناریو 5 — تراکنش با خطای غیرقابل‌اعتماد (XACT_STATE)

شرح: در صورت بروز خطای severe که تراکنش را غیرقابل commit می‌کند، باید فقط rollback کنیم.

کد:

BEGIN TRY
    BEGIN TRAN;

    -- کاری که ممکنه خطای جدی بده (مثال فرضی)
    UPDATE Accounts SET Balance = Balance - 999999999 WHERE AccountID = 1;

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        -- تراکنش خراب است؛ فقط rollback ممکن است
        ROLLBACK;
        PRINT 'Transaction uncommittable, rolled back.';
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        ROLLBACK;
        PRINT 'Transaction rolled back.';
    END

    THROW; -- یا ثبت خطا
END CATCH;

تریس:

اگر SQL Server خطایی بده که تراکنش را غیرقابل‌اعتماد کند (مثلاً خطای جدی، deadlock، یا خطای مرتبط با تراکنش log)، XACT_STATE() = -1 و تنها عمل مجاز ROLLBACK است.

نکتهٔ عملی: همیشه در CATCH از XACT_STATE استفاده کن تا رفتار درست را انجام دهی.

سناریو 6 — Isolation level مثال (نمایش dirty read و جلوگیری از آن)

شرح: نشان می‌دهیم دو session چگونه تحت isolationهای مختلف رفتار می‌کنند.

تنظیم اولیه:

فرض دو session (A و B).

Session A (خواندن، حالت READ UNCOMMITTED — اجازه dirty read):

-- Session A:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN;
SELECT Balance FROM Accounts WHERE AccountID = 1; -- ممکن است مقدار ناپایدار ببیند
-- منتظر می‌مانیم تا Session B تغییر و سپس rollback/commit کند

Session B (تغییر و سپس ROLLBACK):

-- Session B:
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 300 WHERE AccountID = 1; -- موقتاً تغییر
-- حالا Session A اگر select کرده باشد، مقدار جدید (مثلاً 700) را می‌بیند (dirty read)
ROLLBACK; -- تغییرات برمی‌گردد

تریس و نتیجه:

اگر Session A در READ UNCOMMITTED باشد، ممکن است مقدار 700 را ببیند، ولی چون Session B در ادامه ROLLBACK کرده، مقدار واقعی هنوز 1000 است — این وضعیت dirty read نامیده می‌شود (نادرست خواندن دادهٔ موقتی).
اگر Session A از READ COMMITTED (پیش‌فرض) استفاده کند، در زمان اجرا SELECT مقدار قبلی (1000) را می‌بیند زیرا تغییر Session B هنوز commit نشده و قفل‌ها خواندن را بلاک می‌کنند (یا در صورت فعال بودن snapshot read_committed_snapshot از نسخهٔ قبلی استفاده می‌شود).

نکته عملی: برای جلوگیری از dirty read از READ COMMITTED یا سطوح بالاتر یا از SNAPSHOT استفاده کن.

سناریو 7 — تراکنش توزیع‌شده (Distributed Transaction) — معرفی و مثال ساده

شرح: وقتی تراکنش باید هم‌زمان روی دو دیتابیس / سرور مختلف انجام شود، از قابلیت «تراکنش توزیع‌شده» و MSDTC استفاده می‌شود. این سناریو پیچیده است و نیاز به تنظیم سرویس DTC دارد؛ برای آگاهی، مثال کلی:

-- در SQL Server (با فعال بودن MSDTC)
BEGIN DISTRIBUTED TRANSACTION;

-- Update در دیتابیس محلی
UPDATE LocalDB.dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

-- Update در دیتابیس روی سرور دیگر (linked server)
UPDATE LinkedServer.RemoteDB.dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

COMMIT TRANSACTION; -- یا ROLLBACK

نکته و تریس:

SQL Server با MSDTC هماهنگ می‌کند که هر دو سرور commit یا rollback را هم‌زمان انجام دهند.
اگر یکی از سرورها خطا بدهد، همهٔ طرف‌ها rollback می‌شوند.
هشدار عملی: تنظیم، خطایابی و کارایی توزیع‌شده سخت است؛ فقط وقتی ضروری است از آن استفاده کن.

خلاصهٔ نکات کاربردی (چه‌وقتی از هر الگو استفاده کنیم)

Distributed transactions: فقط برای عملیات بین سرور/دیتابیس‌ها و با MSDTC؛ پیچیده است.

BEGIN/COMMIT: برای هر تغییر اتمیک عمومی.

TRY/CATCH + ROLLBACK: هرگاه احتمال خطا وجود دارد (توصیهٔ استاندارد).

SAVE TRANSACTION: وقتی تراکنش چندمرحله‌ای است و امکان بازگشت جزئی لازم است.

Nested Transactions: معمولاً مراقب باش — COMMIT داخلی به‌تنهایی داده‌ها را نهایی نمی‌کند؛ COMMIT بیرونی مهم است.

XACT_STATE(): برای تصمیم‌گیری در CATCH (rollback یا نه).

Isolation levels: برای کنترل خواندن و قفل‌ها؛ بسته به نیاز به consistency یا performance انتخاب کن.

🔹 `XACT_STATE()` چیست؟

یک تابع سیستمی در SQL Server است.
مقدار وضعیت تراکنش جاری (Current Transaction State) را برمی‌گرداند.
خیلی پرکاربرد در بخش CATCH تراکنش‌ها (داخل TRY…CATCH) برای تصمیم‌گیری درست در مورد COMMIT یا ROLLBACK.

🔹 مقادیر بازگشتی

XACT_STATE() فقط سه مقدار ممکن دارد:

0 → هیچ تراکنش فعالی وجود ندارد
- یعنی داخل هیچ تراکنشی نیستی.
- بنابراین اگر ROLLBACK یا COMMIT بزنی، خطا می‌گیری.

1 → تراکنش فعال و قابل COMMIT
- یعنی یک تراکنش باز داری و هنوز سالمه.
- در این حالت می‌تونی یا COMMIT بزنی یا اگر خواستی همه رو ROLLBACK کنی.
- مثال: BEGIN TRAN; SELECT XACT_STATE(); -- خروجی = 1

-1 → تراکنش فعال اما Uncommittable (غیرقابل COMMIT)
- یعنی تراکنش بازه ولی به خاطر یک خطای جدی (severity level خاص) خراب شده.
- در این حالت فقط ROLLBACK مجازه.
- اگر سعی کنی COMMIT بزنی، خطا می‌گیری.
- مثال: BEGIN TRAN; -- خطای جدی (مثل تقسیم بر صفر داخل تراکنش) ایجاد کنیم DECLARE @x INT = 1/0; -- باعث خطا میشه SELECT XACT_STATE(); -- خروجی = -1

🔹 چرا به درد می‌خوره؟

داخل CATCH نمی‌دونی وضعیت تراکنش چیه.

شاید تراکنشی نداشتی (0)
شاید تراکنشت سالمه و می‌تونی ROLLBACK بزنی (1)
شاید تراکنشت خراب شده و فقط مجازه ROLLBACK بزنی (-1)

بنابراین معمولاً توی CATCH این الگو استفاده میشه:

BEGIN TRY
    BEGIN TRAN;
    -- کدهای عملیات
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        -- تراکنش خراب شده → فقط ROLLBACK
        ROLLBACK;
        PRINT 'Transaction was doomed. Rolled back.';
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        -- تراکنش سالمه → میشه ROLLBACK کرد
        ROLLBACK;
        PRINT 'Transaction rolled back safely.';
    END
    ELSE
    BEGIN
        PRINT 'No transaction was active.';
    END
END CATCH;

🔹 خلاصه در یک جدول

مقدار	معنی	مجاز
0	هیچ تراکنشی فعال نیست	هیچ کاری نمی‌تونی بکنی
1	تراکنش فعال و سالم	میشه COMMIT یا ROLLBACK کرد
-1	تراکنش فعال ولی خراب (Uncommittable)	فقط ROLLBACK مجازه

بریم یک سناریوی واقعی طراحی کنیم تا هر سه حالت XACT_STATE() رو با چشم ببینی.

🟢 جدول آزمایشی

CREATE TABLE TestTrans (
    ID INT PRIMARY KEY,
    Value INT
);

TRUNCATE TABLE TestTrans;
INSERT INTO TestTrans VALUES (1, 10), (2, 20);

🔹 حالت 1 → `XACT_STATE() = 0` (هیچ تراکنشی وجود ندارد)

-- بیرون از تراکنش
SELECT XACT_STATE() AS State;  
-- خروجی = 0

📌 توضیح: چون هیچ BEGIN TRAN نزدی، هیچ تراکنشی فعال نیست.

🔹 حالت 2 → `XACT_STATE() = 1` (تراکنش فعال و سالم)

BEGIN TRAN;

UPDATE TestTrans SET Value = Value + 1 WHERE ID = 1;

SELECT XACT_STATE() AS State;  
-- خروجی = 1

ROLLBACK; -- یا COMMIT

📌 توضیح: یک تراکنش باز کردی، همه‌چیز سالمه → میشه COMMIT یا ROLLBACK کرد.

🔹 حالت 3 → `XACT_STATE() = -1` (تراکنش فعال اما خراب = Uncommittable)

BEGIN TRAN;

UPDATE TestTrans SET Value = Value + 1 WHERE ID = 1;

-- خطای جدی عمداً ایجاد کنیم (تقسیم بر صفر)
DECLARE @x INT;
SET @x = 1 / 0;  

SELECT XACT_STATE() AS State;  
-- خروجی = -1

COMMIT;   -- ❌ خطا می‌دهد
ROLLBACK; -- ✅ تنها گزینه ممکن

📌 توضیح: تراکنش هنوز باز است، اما چون خطای جدی اتفاق افتاده، غیرقابل COMMIT شده.
فقط باید ROLLBACK بزنی تا سیستم به حالت عادی برگردد.

📝 جمع‌بندی

0 → تراکنش نداری.
1 → تراکنش داری و سالمه. (COMMIT یا ROLLBACK هر دو ممکنه)
-1 → تراکنش داری ولی خراب شده. (فقط ROLLBACK مجازه)

تراکنش ها در SQL Server

تراکنش (Transaction) — خلاصهٔ ساده

۱ — خصوصیات ACID (چه انتظاری از تراکنش داریم)

۲ — حالت‌های تراکنش در SQL Server (مفاهیم و دستورات)

۳ — الگوی استاندارد مدیریت خطا در تراکنش (TRY / CATCH)

۴ — Isolation Levels (سطوح جداسازی) و اثرات آنها

۵ — اثرات تراکنش روی لاگ و کارایی

۶ — مثال‌های عملی و تریس مرحله‌به‌مرحله

مثال A — انتقال وجه (نمونهٔ کلاسیک Atomicity)

مثال B — استفاده از SAVEPOINT و rollback جزئی

مثال C — الگوی TRY/CATCH با XACT_STATE

مثال D — دمو برای dirty read (نمایش تأثیر isolation)

۷ — خطاهای رایج و نکات جلوگیری

۸ — بهترین شیوه‌ها (Best Practices)

۹ — چک‌لیست سریع (دستورات پراهمیت)

آماده‌سازی مشترک (تعریف جدول و داده نمونه)

سناریو 1 — انتقال ساده (Atomicity: BEGIN TRAN / COMMIT)

کد:

تریس (مرحله به مرحله):

سناریو 2 — انتقال با بررسی موجودی (TRY/CATCH و ROLLBACK)

کد:

تریس:

سناریو 3 — استفاده از SAVEPOINT (بازگردانی جزئی)

کد:

تریس:

سناریو 4 — Nested Transactions (تعداد تراکنش‌ها و COMMIT داخلی)

کد:

تریس (مخرّن شده با مقادیر):

سناریو 5 — تراکنش با خطای غیرقابل‌اعتماد (XACT_STATE)

کد:

تریس:

سناریو 6 — Isolation level مثال (نمایش dirty read و جلوگیری از آن)

تنظیم اولیه:

Session A (خواندن، حالت READ UNCOMMITTED — اجازه dirty read):

Session B (تغییر و سپس ROLLBACK):

تریس و نتیجه:

سناریو 7 — تراکنش توزیع‌شده (Distributed Transaction) — معرفی و مثال ساده

نکته و تریس:

خلاصهٔ نکات کاربردی (چه‌وقتی از هر الگو استفاده کنیم)

🔹 XACT_STATE() چیست؟

🔹 مقادیر بازگشتی

🔹 چرا به درد می‌خوره؟

🔹 خلاصه در یک جدول

🟢 جدول آزمایشی

🔹 حالت 1 → XACT_STATE() = 0 (هیچ تراکنشی وجود ندارد)

🔹 حالت 2 → XACT_STATE() = 1 (تراکنش فعال و سالم)

🔹 حالت 3 → XACT_STATE() = -1 (تراکنش فعال اما خراب = Uncommittable)

📝 جمع‌بندی

🔹 `XACT_STATE()` چیست؟

🔹 حالت 1 → `XACT_STATE() = 0` (هیچ تراکنشی وجود ندارد)

🔹 حالت 2 → `XACT_STATE() = 1` (تراکنش فعال و سالم)

🔹 حالت 3 → `XACT_STATE() = -1` (تراکنش فعال اما خراب = Uncommittable)