none
SqlTransaction and SqlConnection state after worker thread aborted RRS feed

  • Question

  • Hi,

    I have a project that I need to use background worker thread to update database in one transaction.  But when I abort the worker thread at the middle of transaction, the State of SqlConnection is closed in catch block.  Because the connection state is closed, the Rollback in fact did not really rollback the transaction.  I checked  Activity Monitor in Sql Server 2005, the connection was still there and was in transaction state.  So, if I rerun the update again, I got the dead lock.  I am thinking it is a bug in ADO.NET or in Sql Server 2005.  I try to use SqlConnection.ClearAllPools but still cannot kill that connection in Activity Monitor.  Is there any way to work around this?

    Below is a sample code that will cause this problme.

     

     

    private void button1_Click(object sender, EventArgs e)

    {

    worker = new Thread(new ThreadStart(this.Start));

    worker.IsBackground = true;

    worker.Start();

    }

    private void ExecuteCmd(string cmdText)

    {

    using (SqlCommand cmd = cnn.CreateCommand())

    {

    cmd.Connection = cnn;

    cmd.Transaction = tx;

    cmd.CommandText = cmdText;

    cmd.CommandType = CommandType.Text;

    cmd.CommandTimeout = 15;

    try

    {

    cmd.ExecuteNonQuery();

    }

    catch (ThreadAbortException)

    {

    ConnectionState state = cnn.State;

    if (tx != null)

    {

    tx.Rollback();

    tx = null;

    }

    throw;

    }

    }

    }

    private void Start()

    {

    // using (TransactionScope scope = new TransactionScope())

    {

    try

    {

    cnn = new SqlConnection("connection string");

    cnn.Open();

    toContinue = true;

     

    tx = cnn.BeginTransaction("hello");

    Thread.SetData(Thread.GetNamedDataSlot("tx"), tx);

    while (toContinue)

    {

    ExecuteCmd( "Update A_TABLE set counter=counter+1 where test_id=1");

    }

    //scope.Complete();

    tx.Commit();

    }

    catch (Exception)

    {

    if (tx != null)

    {

    tx.Rollback();

    }

    }

    finally

    {

    worker = null;

    }

    }

    }

    private void button2_Click(object sender, EventArgs e)

    {

    worker.Abort();

    }

    private void button3_Click(object sender, EventArgs e)

    {

    toContinue = false;

    }

    Thursday, October 25, 2007 4:21 PM

All replies

  • There is a hot fix for System.Transaction but it could be related to you mixing both ADO.NET transactions with System.Transaction, the former is atomic while the later is not. 

     

    http://support.microsoft.com/kb/936983

     

    Thursday, October 25, 2007 9:06 PM
  • Thanks for your help. 

    I think the symptom of that hotfix is different than mine.  My problem is when I execute a time consuming SqlCommand.ExecuteNonQuery inside transaction in a worker thread and then abort the thread, the SqlConnection.State is 'Closed' and I cannot rollback the tx because ADO.NET thinks the connection is closed  and I cannot kill that connection either. It leads to a dead lock when I rerun the same update query by ExecuteNonQuery.  I also try to use TransactionScope as an alternative but it has the same problem.

       

     

    Friday, October 26, 2007 2:02 PM
  • You are right the problem is actually related to the duration of the transaction and the try/catch block Microsoft says long operation use stored procedures and users says a Using statement will help with the rollback instead of the try/catch.  Read the user comments and use the link there are more comments.

     

    http://msdn2.microsoft.com/en-us/library/2k2hy99x(VS.80).aspx

    Friday, October 26, 2007 3:42 PM
  • I also tested this issue by OracleConnection/OracleTransaction.  OracleConnection keeps opened after catching ThreadAbortedException so I can successfully rollback the transaction without the problem.  I am thinking it is a bug in SQL ADO.NET provider.  I found a way to work around this problem.  For MSS SQL, instead of calling SqlTransaction.Rollback directly, I check its connection state first.  If the connection state is 'Open' then I call Rollback().  If the connection state is 'Closed', the code will open the connection again and  executes 'KILL [previous connection spid]' query to kill the previous connection that left in server and internally rollback the transaction.  It may not be the best solution but it works to me.   

     

    Monday, October 29, 2007 6:52 PM