none
SqlTransaction.Rollback() does not throw InvalidOperationException in case the transaction has already been rolled back. RRS feed

  • Question

  • According to MSDN SqlTransaction.Rollback() method must throw InvalidOperationException in case the transaction has already been committed or rolled back. 

    However, in my tests I don't get any exceptions. Here is the code:

    CREATE PROCEDURE dbo.USP_TEST_TX_PROC
    AS
    BEGIN
    	SELECT 1/0;
    	ROLLBACK TRANSACTION;
    END
    GO
    
    
                using(SqlConnection con = new SqlConnection(@"Data Source=XXX;Initial Catalog=TestDB;Integrated Security=True"))
                {
                    con.Open();
                    SqlTransaction tr = con.BeginTransaction();
                    SqlCommand cmd = new SqlCommand("dbo.USP_TEST_TX_PROC", con, tr) { CommandType = CommandType.StoredProcedure};
                    try
                    {
                        cmd.ExecuteNonQuery();
                        tr.Commit();
                    }
                    catch (Exception ex)
                    {
                        try
                        {
                            tr.Rollback();
                        }
                        catch (Exception ex2)
                        {
                            Console.WriteLine(" Message: {0}", ex2.Message);
                        }
                    }
    
    What am I doing wrong?
    Thank you.


    Alexey

    • Moved by Dan GuzmanMVP Sunday, April 12, 2015 5:08 PM Move to most appropriate forum
    Sunday, April 12, 2015 1:52 PM

All replies

  • I don't think you are doing anything wrong.  This could be classified as a bug in SqlClient and the documentation is incorrect or incomplete.  I'll move this question to the ADO.NET Managed Providers forum so that others can weigh in.

    The current behavior is that no exception is raised when Rollback is invoked with no outstanding transaction after execution of the command.  The expected InvalidOperationException is raised only if Rollback is called again. Note that SqlClient is aware of whether or not an outstanding transaction exists because SQL Server returns the transaction status back to the client via a TDS DONE message after completion of the command.  SqlClient apparently short-circuits the Rollback call without an exception when no outstanding transaction exists due to COMMIT/ROLLBACK on the server.  The InvalidOperationException provides little value other than noise in the case where the transaction has already been rolled back.  Also, a server round trip is avoided when no work needs to be undone. 

    All that being said, the best practice is to perform transaction handling on the server or client but never both.  SqlClient has no way to distinguish between a rollback or commit on the server side.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, April 12, 2015 5:06 PM
  • All that being said, the best practice is to perform transaction handling on the server or client but never both.

    OK. I can change the SP according to best practices:

    CREATE PROCEDURE dbo.USP_TEST_TX_PROC
    AS
    BEGIN
    	SET XACT_ABORT, NOCOUNT ON;
    	DECLARE @OuterTranCount int = @@TRANCOUNT;
    
    	IF(@OuterTranCount = 0) 
               BEGIN TRANSACTION;
    
    	SELECT 1/0;
    
            IF (@OuterTranCount = 0)
               COMMIT TRANSACTION;
    END
    

    Now, if I run the same .NET code, I still will not get InvalidOperationException exception.


    Alexey

    Sunday, April 12, 2015 5:46 PM
  • Hello Alexey,

    I created a client side demo which could throw the InvalidOperationException:

    using (SqlConnection connection = new SqlConnection(@"Server=(localdb)\Projects;Database=DFDB;Trusted_Connection=True;"))
    
                    {
    
                        connection.Open();
    
    
                        SqlCommand command = connection.CreateCommand();
    
                        SqlTransaction transaction;
    
                        transaction = connection.BeginTransaction("SampleTransaction");
    
                        command.Connection = connection;
    
                        command.Transaction = transaction;
    
    
                        try
    
                        {
    
                            command.CommandText = "ProInsertIntoOrder 1,'1'";
    
                            command.ExecuteNonQuery();
    
                            transaction.Rollback();
    
                            throw new Exception();
    
                        }
    
                        catch (Exception ex)
    
                        {
    
    
                            // Attempt to roll back the transaction. 
    
                            try
    
                            {
    
                                transaction.Rollback();
    
                            }
    
                            catch (InvalidOperationException ex2)
    
                            {
    
                                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
    
                                Console.WriteLine("  Message: {0}", ex2.Message);
    
                            }
    
                        }
    
                    }
    

    You could have a try. However, it is not clear why it could detect the ROLLBACK statement on the server side, I suggest that you could post this feedback to:

    https://connect.microsoft.com/VisualStudio/feedback/LoadSubmitFeedbackForm

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 14, 2015 9:58 AM
    Moderator