مدیریت تراکنش های تو در تو SQL Server با XACT_STATE

مدیریت کارآمد تراکنش‌ها در رویه‌های ذخیره‌شده تو در توی SQL Server با XACT_STATE()

در نگاه اول، ممکن است فکر کنید استفاده از BEGIN TRAN، COMMIT TRAN و ROLLBACK TRAN به همراه @@TRANCOUNT برای ردیابی تعداد تراکنش‌های باز کافی است. با این حال، در محیط رویه‌های تو در تو، @@TRANCOUNT به تنهایی قادر به تشخیص وضعیت دقیق تراکنش نیست، زیرا فقط تعداد تراکنش‌های باز را نشان می‌دهد و نمی‌تواند وضعیت Rollback شده یک تراکنش را مشخص کند. به همین دلیل، SQL Server تابعی قدرتمند به نام XACT_STATE() را ارائه می‌دهد که برای حل این مشکل طراحی شده است.

بیایید یک رویه ذخیره‌شده ساده را در نظر بگیریم که تراکنش‌ها را به صورت تو در تو مدیریت می‌کند. فرض کنید یک رویه ذخیره‌شده بیرونی داریم که رویه دیگری را فراخوانی می‌کند و هر دو شامل بلاک‌های BEGIN TRAN، COMMIT TRAN و ROLLBACK TRAN هستند.

کد زیر نحوه ایجاد دو رویه ذخیره‌شده را نشان می‌دهد: یکی usp_ParentProcedure و دیگری usp_ChildProcedure. رویه والد، رویه فرزند را فراخوانی می‌کند.

CREATE PROCEDURE usp_ParentProcedure
AS
BEGIN
    BEGIN TRY
        PRINT 'Parent Procedure - Before Transaction, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        BEGIN TRAN
            PRINT 'Parent Procedure - After BEGIN TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

            EXEC usp_ChildProcedure;

            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRAN
                PRINT 'Parent Procedure - After COMMIT TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
            END
    END TRY
    BEGIN CATCH
        PRINT 'Parent Procedure - In CATCH block, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            PRINT 'Parent Procedure - After ROLLBACK TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        THROW;
    END CATCH
END;
GO

CREATE PROCEDURE usp_ChildProcedure
AS
BEGIN
    BEGIN TRY
        PRINT 'Child Procedure - Before Transaction, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        BEGIN TRAN
            PRINT 'Child Procedure - After BEGIN TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

            -- Intentional error to demonstrate rollback
            -- SELECT 1/0;

            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRAN
                PRINT 'Child Procedure - After COMMIT TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
            END
    END TRY
    BEGIN CATCH
        PRINT 'Child Procedure - In CATCH block, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            PRINT 'Child Procedure - After ROLLBACK TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        THROW;
    END CATCH
END;
GO

در مثال بالا، اگر خط SELECT 1/0; را در usp_ChildProcedure از حالت کامنت خارج کنیم، خطایی رخ می‌دهد. انتظار می‌رود که تراکنش شروع شده در رویه والد نیز Rollback شود. با این حال، رفتار @@TRANCOUNT ممکن است گمراه‌کننده باشد. زمانی که یک ROLLBACK TRAN در رویه فرزند اجرا می‌شود، تمام تراکنش‌های باز تا نقطه ابتدایی (نقطه BEGIN TRAN بیرونی‌ترین) را Rollback می‌کند و @@TRANCOUNT را به صفر می‌رساند. این باعث می‌شود که COMMIT TRAN در رویه والد با خطا مواجه شود، زیرا هیچ تراکنشی برای Commit وجود ندارد.

اینجاست که تابع XACT_STATE() وارد عمل می‌شود. این تابع وضعیت تراکنش فعلی را با دقت بیشتری نسبت به @@TRANCOUNT نشان می‌دهد.

XACT_STATE()

این تابع یک مقدار smallint برمی‌گرداند که می‌تواند یکی از سه حالت زیر باشد:

• 1 (یک): نشان‌دهنده یک تراکنش قابل Commit است. (Commitable transaction)
• 0 (صفر): نشان‌دهنده عدم وجود تراکنش است. (No transaction)
• -1 (منفی یک): نشان‌دهنده یک تراکنش غیرقابل Commit است که Rollback شده یا در حالت Rollback-Only قرار گرفته است. (Uncommittable transaction/Rollback-Only)

استفاده از XACT_STATE() به شما امکان می‌دهد تا حتی پس از وقوع خطا و Rollback در یک رویه تو در تو، تشخیص دهید که آیا تراکنش اصلی همچنان نیاز به Rollback دارد یا خیر. به این ترتیب می‌توانید منطق Rollback را به درستی پیاده‌سازی کنید.

اکنون، بیایید رویه‌های ذخیره‌شده را برای استفاده از XACT_STATE() اصلاح کنیم. این تغییرات به رویه‌های ما کمک می‌کند تا وضعیت تراکنش را به درستی مدیریت کنند، حتی زمانی که رویه‌های تو در تو درگیر خطا می‌شوند.

ALTER PROCEDURE usp_ParentProcedure
AS
BEGIN
    PRINT 'Parent Procedure - Current Transaction State: ' + CAST(XACT_STATE() AS VARCHAR(10));
    BEGIN TRY
        PRINT 'Parent Procedure - Before Transaction, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        BEGIN TRAN
            PRINT 'Parent Procedure - After BEGIN TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));

            EXEC usp_ChildProcedure;

            -- Only COMMIT if XACT_STATE is 1 (Commitable)
            IF XACT_STATE() = 1
            BEGIN
                COMMIT TRAN
                PRINT 'Parent Procedure - After COMMIT TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
            END
            ELSE IF XACT_STATE() = -1
            BEGIN
                -- Transaction is in an uncommittable state, perform a rollback
                ROLLBACK TRAN
                PRINT 'Parent Procedure - Transaction was rolled back by child, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
            END
            
    END TRY
    BEGIN CATCH
        PRINT 'Parent Procedure - In CATCH block, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        PRINT 'Parent Procedure - Current Transaction State in CATCH: ' + CAST(XACT_STATE() AS VARCHAR(10));

        -- Only ROLLBACK if XACT_STATE is 1 (Commitable) or -1 (Uncommittable)
        -- If XACT_STATE is 0, no active transaction to rollback
        IF XACT_STATE()  0 
        BEGIN
            ROLLBACK TRAN
            PRINT 'Parent Procedure - After ROLLBACK TRAN in CATCH, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        THROW;
    END CATCH
END;
GO

ALTER PROCEDURE usp_ChildProcedure
AS
BEGIN
    PRINT 'Child Procedure - Current Transaction State: ' + CAST(XACT_STATE() AS VARCHAR(10));
    BEGIN TRY
        PRINT 'Child Procedure - Before Transaction, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        
        -- Start a transaction only if not already in a committable transaction
        IF XACT_STATE() = 0 -- No active transaction
        BEGIN
            BEGIN TRAN
            PRINT 'Child Procedure - Started new transaction. After BEGIN TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        ELSE IF XACT_STATE() = 1 -- Existing committable transaction
        BEGIN
            PRINT 'Child Procedure - Participating in existing transaction. @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        ELSE IF XACT_STATE() = -1 -- Existing uncommittable transaction
        BEGIN
            -- Do nothing, transaction is already doomed.
            PRINT 'Child Procedure - Existing transaction is uncommittable. @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END


        -- Intentional error to demonstrate rollback
        -- SELECT 1/0;

        -- Only COMMIT if a new transaction was started by THIS child procedure and XACT_STATE is 1
        IF @@TRANCOUNT > 0 AND XACT_STATE() = 1 AND @@TRANCOUNT = 1 -- Simplified logic if we only started one transaction here
        BEGIN
            COMMIT TRAN
            PRINT 'Child Procedure - After COMMIT TRAN, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        ELSE IF XACT_STATE() = -1 AND @@TRANCOUNT > 0
        BEGIN
            -- If child procedure started a transaction and it became uncommittable due to an internal error, rollback.
            -- This rollback will affect the outer transaction as well.
            ROLLBACK TRAN
            PRINT 'Child Procedure - Transaction rolled back due to error, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END

    END TRY
    BEGIN CATCH
        PRINT 'Child Procedure - In CATCH block, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        PRINT 'Child Procedure - Current Transaction State in CATCH: ' + CAST(XACT_STATE() AS VARCHAR(10));
        -- If an error occurs, and there's an active transaction (even if already doomed), rollback.
        IF XACT_STATE()  0
        BEGIN
            ROLLBACK TRAN
            PRINT 'Child Procedure - After ROLLBACK TRAN in CATCH, @@TRANCOUNT: ' + CAST(@@TRANCOUNT AS VARCHAR(10));
        END
        THROW;
    END CATCH
END;
GO

این رویه‌های اصلاح شده با استفاده از XACT_STATE() تضمین می‌کنند که وضعیت تراکنش در هر مرحله به درستی بررسی می‌شود. رویه فرزند قبل از شروع یک تراکنش جدید، ابتدا وضعیت فعلی تراکنش را بررسی می‌کند. اگر تراکنش بیرونی قبلاً Rollback شده باشد یا در حالت Rollback-Only باشد (XACT_STATE() = -1)، رویه فرزند از شروع یک تراکنش جدید یا تلاش برای Commit کردن آن خودداری می‌کند. به این ترتیب، از بروز خطاهای COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION جلوگیری می‌شود و یکپارچگی تراکنش‌ها حفظ می‌گردد.

برای تست این کد، می‌توانید ابتدا رویه‌ها را بدون فعال کردن خطای SELECT 1/0; اجرا کنید و سپس با فعال کردن آن، رفتار Rollback را مشاهده کنید. در هر دو حالت، XACT_STATE() به شما کمک می‌کند تا تصمیمات صحیحی در مورد COMMIT یا ROLLBACK بگیرید و از هدر رفتن زمان برای دیباگ کردن مشکلات پیچیده تراکنش جلوگیری کنید.

مدیریت تراکنش‌های تو در تو با XACT_STATE() راهکاری قدرتمند و استاندارد برای اطمینان از عملکرد صحیح و پایدار برنامه‌های SQL Server است. با به‌کارگیری این تابع، می‌توانید ساختار رویه‌های ذخیره‌شده خود را بهبود بخشید و خطاهای مربوط به تراکنش را به حداقل برسانید.