none
Why SqlTransaction's Rollback mehtod TIMEOUT?

    Question

  • I've done the following test:
    First I insert 700,000 records to a table(The table has 46 columns);
    Then I call the SqlTransaction's Rollback method, after a little while, an EXECEPTION occured.

    The exception's information is below:
    -------------------------------------------------------
    System.Data.SqlClient.SqlException was unhandled
      Message="Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060
      Class=11
      LineNumber=0
      Number=-2
      Procedure=""
      Server=".\\sqlexpress"
      State=0
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at DBTimeoutTest.Form1.DBAccess.RollbackTransaction() in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 114
           at DBTimeoutTest.Form1.OnLoad(EventArgs e) in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 73
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.set_Visible(Boolean value)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at DBTimeoutTest.Program.Main() in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Program.cs:line 18
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:
    -------------------------------------------------------

    Then I tried the following ways, all of which can solve the above problem.
    (1) I use a SqlCommand setting it's CommandText as "Rollback Transaction" and CommandTimeout as 0, then call it's ExecuteNonQuery method. It can rollback correctly.
    (2) I set the Connection's Connect Timeout attribute as 0. And still call the SqlTransaction's Rollback method. It also can rollback correctly.

    Here the QUESTION:
    WHY the Rollback method throw a EXCEPTION?

    Base on the two solutions above, I guess that:
    When the Rollback method is called, it get the connection's Connect Timeout as it's CommandTimeout. And that's why just modify the connection's Connect Timeout can solve the problem.

    What do you think?

    Wednesday, July 9, 2008 3:16 AM

Answers

  • What?   Strange answer from Caddre.

     

    I think the question is why does a rollback timeout and what controls the timeout.

     

    I can answer this one.

     

    First of all, a rollback can take a very long time (as you have discovered).  The database may have to do lots of work to perform a rollback.  The amount of work depends upon things such as the number of modified records, the size of data modified (think huge LOB fields for example), and also the recovery settings on the database (full recovery versus simple recovery for example can have different behavior).

     

    Hence when performing large operations you need to be aware of this.

     

    But the thing to really be concerned about is IF a rollback times out, what really happens on the server side?   Is the rollback partially completed or fully completed?  A client initiated timeout will send a "cancel" command to the server.  This is called an "attention" from client.  This tells the server to stop the currently running batch and rollback it's operation.

     

    But if you send a rollback, and then tell the server to stop performing the rollback, does the server rollback the rollback?  Wouldn't this mean restoring the records?  Personally off the top of my head I was uncertain, so I asked transaction folks here internally (at SQL Server) what it meant before I responded.

     

    According to txn gurus, the rollback will continue even after the client initiates a timeout to "cancel" the rollback.

     

    Hence you are golden in your case, the rollback will complete.

     

    Secondly, concerning the timeout period.  Yes, the connect timeout from connection string is used.  I verified this by looking at source code, see SqlInternalConnection.ExecuteTransactionYukon ->

     

    _parser.TdsExecuteTransactionManagerRequest(null, requestType, transactionName, isoLevel,
                        ConnectionOptions.ConnectTimeout, internalTransaction, stateObj, isDelegateControlRequest);

     

    This timeout is used in a few other places such as BeginTransaction and when changing databases via SqlConnection object.

    Friday, July 11, 2008 10:52 PM

All replies

  • I can't see the rollback method on the stack. I see ExecuteNonQuery only.

     

    StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at DBTimeoutTest.Form1.DBAccess.RollbackTransaction() in C:\Documents and Settings\zhoujj\Desktop\SMT\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 114
           at DBTimeoutTest.Form1.OnLoad(EventArgs e) in C:\Documents and Settings\zhoujj\Desktop\SMT\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 73
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)

    You should use SqlTransaction to operate on transactions instead of starting/committing transactons using SqlConnection.ExecuteNonQuery.

    Wednesday, July 9, 2008 7:22 PM
  • Sorry, I've post the wrong exception information. What I've post is the exception of my another test. In that test I use SqlCommand to rollback the transaction. I set the CommandText as "Rollback Transaction" and set the CommandTimeout as 15.

    The following is the correct exception information, which is throwed by SqlTransaction's Rollback method.

    -----------------------------------------------------------------------------

    System.Data.SqlClient.SqlException was unhandled
      Message="Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060
      Class=11
      LineNumber=0
      Number=-2
      Procedure=""
      Server=".\\sqlexpress"
      State=0
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
           at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
           at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
           at System.Data.SqlClient.SqlInternalTransaction.Rollback()
           at System.Data.SqlClient.SqlTransaction.Rollback()
           at Test.Form1.DBAccess.RollbackTransaction() in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 127
           at Test.Form1.OnLoad(EventArgs e) in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Form1.cs:line 73
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.set_Visible(Boolean value)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at Test.Program.Main() in C:\Documents and Settings\zhoujj\Desktop\TEST\DBTimeoutTest\DBTimeoutTest\Program.cs:line 18
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    Thursday, July 10, 2008 3:25 AM
  • What exactly is the reason you are passing 700,000 records in 46 columns in one transaction operation?  The reason I am asking is I am assuming you are nesting but most nesting have levels of how much you can do in one operation.  You need to get into design mode and create views for all your operations so you can access each as a unit of work.

     

     

     

     

    Thursday, July 10, 2008 2:54 PM
  • In my work, there's a application, which is developed by us, throw this exception. I found that it was thrown by Rollback method of SqlTransaction. In order to investigate the bug's reason, I write a little test, which is metioned in my post. And I found that after update huge data in a transaction, then call the Rollback method, the exception will occur. I update 700,000 records in one transaction just for test, because in the application, which is mentioned just now, huge data are updated too. I just want to make the same case to reproduce the exception. The following codes are my test for the exception. I guess I didn't use nesting(Sorry, I'm not familiar with nesting. I don't know whether what I did in my following codes is nesting).

    By the way, is my guess in my post correct, which is Rollback method also have a timeout that equals to the timeout of connection?


    ------------------------------------------------------------------------

    Code Snippet


    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;

    namespace Test
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            protected override void OnLoad(EventArgs e)
            {
                base.OnLoad(e);

                DBAccess dbAccess = new DBAccess();
                DataTable table = dbAccess.GetTableStructure("TestTable");

                DataRow row = null;
                List<DataColumn> primaryKey = new List<DataColumn>(table.PrimaryKey);
                for (int i = 0; i < 700000; i++)
                {
                    row = table.NewRow();

                    foreach (DataColumn column in table.Columns)
                    {
                        if (primaryKey.Contains(column))
                        {
                            row[column] = i.ToString();
                        }
                        else if (!column.AllowDBNull && !column.DataType.Equals(typeof(bool)))
                        {
                            row[column] = (i.ToString().Length > column.MaxLength && column.MaxLength > 0) ?
                                i.ToString().Substring(0, column.MaxLength) : i.ToString();
                        }
                        else if (!column.AllowDBNull && column.DataType.Equals(typeof(bool)))
                        {
                            row[column] = false;
                        }
                        else
                        {
                            row[column] = column.DefaultValue;
                        }
                    }

                    table.Rows.Add(row);
                }

                SqlTransaction transaction = null;

                try
                {
                    transaction = dbAccess.BeginTransaction();
                    dbAccess.UpdateTable(transaction, table);
                   
                    throw new Exception(); // Just for test.
                   
                    dbAccess.CommitTransaction();
                }
                catch (Exception ex)
                {
                    dbAccess.RollbackTransaction(); // Here the exception will occur.
                }
                finally
                {
                    dbAccess.DisposeTransaction();
                }

                Dispose();
            }

            private class DBAccess
            {
                private const string CONNECTION_STRING = @"Data Source=.\sqlexpress;Initial Catalog=TEST_DB;Integrated Security=True;";//Connect Timeout=0";

                private SqlConnection conn = null;
                private SqlTransaction currentTransaction = null;

                private SqlConnection GetConnection()
                {
                    if (conn == null)
                    {
                        conn = new SqlConnection(CONNECTION_STRING);
                    }

                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    return conn;
                }

                public SqlTransaction BeginTransaction()
                {
                    //SqlConnection conn = new SqlConnection(CONNECTION_STRING);
                    //conn.Open();
                    currentTransaction = GetConnection().BeginTransaction();

                    return currentTransaction;
                }

                public void RollbackTransaction()
                {
                    currentTransaction.Rollback();
                    //SqlCommand cmd = new SqlCommand("Rollback Transaction", currentTransaction.Connection, currentTransaction);
                    //cmd.ExecuteNonQuery();
                }

                public void CommitTransaction()
                {
                    currentTransaction.Commit();
                }

                public void DisposeTransaction()
                {
                    SqlConnection conn = currentTransaction.Connection;

                    if (conn != null)
                    {
                        conn.Close();
                        conn.Dispose();
                    }

                    currentTransaction.Dispose();
                }

                public DataTable GetTableStructure(string tableName)
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", tableName), GetConnection());

                    DataTable table = new DataTable();
                    adapter.FillSchema(table, SchemaType.Source);

                    return table.Copy();
                }

                public void UpdateTable(SqlTransaction transaction, DataTable table)
                {
                    UpdateTable(transaction, transaction.Connection, table, table.TableName);
                }

                public void UpdateTable(DataTable table)
                {
                    UpdateTable(null, GetConnection(), table, table.TableName);
                }

                private void UpdateTable(SqlTransaction transaction, SqlConnection connection, DataTable table, string tableName)
                {
                    SqlCommand selectCommand = null;
                    if (transaction != null)
                    {
                        selectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", tableName), transaction.Connection, transaction);
                    }
                    else
                    {
                        selectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", tableName), connection);
                    }

                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

                    adapter.Update(table);
                }
            }
        }
    }

     

     

    Friday, July 11, 2008 2:34 AM
  • That is very good if you are not nesting then that and not wrapping your code in Using block could be the reasons for the time out, so check the two links below for how to nest your transactions.  In C# the second Using Statement calls dispose for you automatically on classes that implements IDisposable and the ADO.NET Connection object is one such class.

     

    http://msdn.microsoft.com/en-us/library/ms971557.aspx

     

    http://www.davidhayden.com/blog/dave/archive/2005/10/15/2517.aspx


    You must know if you want to roll all back you need to use stored procedure with ADO.NET transactions without nesting and Savepoints.  That is covered in the thread below.  Post again if you still have questions


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2632909&SiteID=1

     


     

    Friday, July 11, 2008 3:53 PM
  • What?   Strange answer from Caddre.

     

    I think the question is why does a rollback timeout and what controls the timeout.

     

    I can answer this one.

     

    First of all, a rollback can take a very long time (as you have discovered).  The database may have to do lots of work to perform a rollback.  The amount of work depends upon things such as the number of modified records, the size of data modified (think huge LOB fields for example), and also the recovery settings on the database (full recovery versus simple recovery for example can have different behavior).

     

    Hence when performing large operations you need to be aware of this.

     

    But the thing to really be concerned about is IF a rollback times out, what really happens on the server side?   Is the rollback partially completed or fully completed?  A client initiated timeout will send a "cancel" command to the server.  This is called an "attention" from client.  This tells the server to stop the currently running batch and rollback it's operation.

     

    But if you send a rollback, and then tell the server to stop performing the rollback, does the server rollback the rollback?  Wouldn't this mean restoring the records?  Personally off the top of my head I was uncertain, so I asked transaction folks here internally (at SQL Server) what it meant before I responded.

     

    According to txn gurus, the rollback will continue even after the client initiates a timeout to "cancel" the rollback.

     

    Hence you are golden in your case, the rollback will complete.

     

    Secondly, concerning the timeout period.  Yes, the connect timeout from connection string is used.  I verified this by looking at source code, see SqlInternalConnection.ExecuteTransactionYukon ->

     

    _parser.TdsExecuteTransactionManagerRequest(null, requestType, transactionName, isoLevel,
                        ConnectionOptions.ConnectTimeout, internalTransaction, stateObj, isDelegateControlRequest);

     

    This timeout is used in a few other places such as BeginTransaction and when changing databases via SqlConnection object.

    Friday, July 11, 2008 10:52 PM
  • (First of all, a rollback can take a very long time (as you have discovered).  The database may have to do lots of work to perform a rollback.)

     

    http://msdn.microsoft.com/en-us/library/ms174973.aspx
     

    That depends on the amount of data passed in and if there is no savepoint to stop the rollback because only data before savepoint is affected by the rollback, as of SQL Server 2000 Sp3a and above the user can roll back savepoints as needed.


    (But if you send a rollback, and then tell the server to stop performing the rollback, does the server rollback the rollback?)

     

    In a batch which is what 700,000 record is if it is stopped by the client SQL Server will automatically roll back any none committed transaction. This is not a better solution than either converting the data to smaller size views and using savepoint to reduce the amount of data that can be rolled back in each roll back. 

     

    You are the ADO.NET expert but I will not use a property with an open bug investigation.

     

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=329339&wa=wsignin1.0

     

     BeginTransaction:  SQL Server docs starts a local transcation that can be escalated to distributed transaction.

     


     

    Saturday, July 12, 2008 1:17 AM
  • Thank you all for your help and opinions.

    I've learnt a lot from you two experts. THANKS!

     

    Monday, July 14, 2008 2:42 AM
  • Sorry, here's another question:
    If transaction should have a timeout. Why don't give it a property, but use connection's timeout instead?

    Is it really a bug?

     

     

    Monday, July 14, 2008 3:13 AM
  • With the other drivers (ODBC + OLEDB) they have a timeout called general timeout that covers everything not handled by connection timeout and command timeout.  With SqlClient, we just have command timeout and connection timeout.


    Connection timeout is used for transaction activities and change database as well as connection activities.  We overloaded it a bit.

     

     

     

    Monday, July 14, 2008 4:07 AM