CODEWITHSUNDEEP
c#ado.net
Maslow
Maslow

Reputation: 18746

Do I need to catch rollback exceptions?

Related but not the same to How to catch exception on RollBack

If we catch and explicitly rollback, it appears we need to try/catch wrap the rollback call. Would eliminating the try/catch entirely still rollback, and if the rollback fails, still send up the root cause exception instead of the rollback exception? I've tried to figure out how to replicate this, but I have no idea how to force a rollback timeout.

This is the legacy pattern:

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
    using (SqlTransaction trans = conn.BeginTransaction())
    {
        try
        {
            //do stuff
            trans.Commit();
        }
        catch
        {
            trans.Rollback();
            throw;
        }
    }
}

Upvotes: 2

Views: 2126

Answers (1)

conkman
conkman

Reputation: 130

This is a pattern I myself have used and seen throughout my career. Recently I experienced a situation where we had a exception occur in production and the stack trace showed the Rollback timing out instead of the actual exception that occurred. I am seeing from my analysis that it is a better practice to not use the explicit Rollback in the catch but instead to let the using statement handle it.

This allows for the correct root cause exception to bubble up and the transaction will be rolled back on the server. To replicate the Rollback timeout I create a table and a procedure and called the stored procedure in a transaction from a Unit Test.

    /****** Object:  Table [dbo].[Table_1]    Script Date: 10/24/2014 12:07:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
    [id] [int] NULL,
    [GuidTest] [uniqueidentifier] NULL,
    [GuidTest2] [uniqueidentifier] NULL,
    [GuidTest3] [uniqueidentifier] NULL
) ON [PRIMARY]





  /****** Object:  StoredProcedure [dbo].[Test_RollBack]    Script Date: 10/24/2014 12:08:04 PM ******/

/****** Object:  StoredProcedure [dbo].[Test_RollBack]    Script Date: 10/24/2014 12:08:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Test_RollBack]  
AS
BEGIN
    DECLARE @counter int = 1

    while @counter < 3000000
    BEGIN
        INSERT INTO Table_1(id, GuidTest, GuidTest2, GuidTest3)
        VALUES(@counter, newId(), newId(), newId())
        set @counter = @counter + 1
    END


    update Table_1
    SET GuidTest = newid()
END

GO


[TestMethod()]
    public void RollBackTestTimeout()
    {
        using (SqlConnection conn = new SqlConnection("Your ConnectionString"))
        {
            conn.Open();

            using (SqlTransaction trans = conn.BeginTransaction())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {

                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Test_RollBack";

                        cmd.ExecuteNonQuery();

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();

                        throw;
                    }
                }
            }

        }
    }

    [TestMethod()]
    public void RollBackTestTimeout_WithUsing()
    {
        using (SqlConnection conn = new SqlConnection("Your ConnectionString"))
        {
            conn.Open();

            using (SqlTransaction trans = conn.BeginTransaction())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Test_RollBack";

                    cmd.ExecuteNonQuery();

                    trans.Commit();
                }
            }

        }
    }

For me the RollBackTestTimeout test Method throws a SqlCommandTimeout but reports a Rollback timeout and RollBackTestTimeout_WithUsing actually shows the Root Cause Exception. So from what I have found I would say let the using handle so you can debug your problem in production later.

Upvotes: 3

Related Questions