SqlTransaction Zombie error after a timeout

Question

I have a all day running data process running with .NET C#

I am getting the zombie error on trying to Rollback a transaction after a timeout occurs:

This SqlTransaction has completed; it is no longer usable.|System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
 at System.Data.SqlClient.SqlTransaction.ZombieCheck()
 at System.Data.SqlClient.SqlTransaction.Rollback()

My code :

using (var transactionSql = connection.BeginTransaction())
{
    using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transactionSql))
    {       
      try
      {
          // Updating table1
          var commandUpd1 = new SqlCommand(query, connection, transactionSql);
          commandUpd1.ExecuteNonQuery();

          // Inserting table2
          var commandInsert1 = new SqlCommand(query2, connection, transactionSql);
          commandInsert1.ExecuteNonQuery();

          // My bulk on table3
          bulkCopy.WriteToServer(dataTable);

          // Updating table1
          var commandUpd2 = new SqlCommand(query3, connection, transactionSql);
          commandUpd2.ExecuteNonQuery();

          // Inserting table2
          var commandInsert3 = new SqlCommand(query4, connection, transactionSql);
          commandInsert3.ExecuteNonQuery();

          transactionSql.Commit();  // Timeout   
     }
     catch (Exception ex)
     {
          transactionSql.Rollback();  //Zombie error

          throw ex;
     }
   }
}

There is a side effect on the zombie error. Some sql I executed are committed. So I have a undesired state.

I know I could do something like that to avoid zombie exception : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqltransaction?redirectedfrom=MSDN&view=netframework-4.8

But that will not fix that undesired persisted sql on database.

Why am I getting that error? How can I avoid that sql persisted on database ?

Update 1 As asked, my update and insert sql sent a date like that:

...
var date = DateTime.Now.ToString("s");
public static void UpdateMethod(long id, int status, string date, SqlConnection connection, SqlTransaction transactionSql)
{
    date = date == null ? "NULL" : $"'{date}'";

    var query = $"UPDATE table SET Status = {status}, Date = {date} WHERE Id = {id}";

    var commandUpdate = new SqlCommand(query, connection, transactionSql);
    commandUpdate.ExecuteNonQuery();
}

Thanks

https://stackoverflow.com/a/616841/1390548 -> See if you have anything of this sort with datetime conversion. — Jawad, Dec 05 '19 at 17:03
Added my update method, that send a Date... Should it be the problem? — Paul, Dec 05 '19 at 17:27
If you get undesirable state, it means transactions don't work as you think they should. You code seems to indicate you're sharing the connection object with some other code. You should instead create the connection, begin transaction, do your job, commit/rollback, close. — Simon Mourier, Dec 11 '19 at 08:21
It always rollback fine when I do not get a zombie error. I also simulated timeout on each executenonquery and rollback worked fine. — Paul, Dec 11 '19 at 12:14
Is your database perhaps in a replica? If your app runs whole day maybe there's a shift in replicas and you lose the transaction? That could explain that you get partial commits on primary replica and then failed on secondary replica. — Iztoksson, Dec 13 '19 at 09:21

Jawad · Answer 1 · 2019-12-06T03:16:54.823

1

You can change the datetime format to your needs.

public static void UpdateMethod(long id, int status, string date, SqlConnection connection, SqlTransaction transactionSql)
{
    var query = $"UPDATE table SET Status = @status, Date = @date WHERE Id = @id";
    var commandUpdate = new SqlCommand(query, connection, transactionSql);
    if (DateTime.TryParseExact(date, new string[] { "yyyy-MM-dd HH:mm:ss" }, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.NoCurrentDateDefault, out DateTime datetime))
        commandUpdate.Parameters.Add(new SqlParameter("@date", datetime));

    else
        commandUpdate.Parameters.Add(new SqlParameter("@date", DBNull.Value));

    commandUpdate.Parameters.Add(new SqlParameter("@status", status));
    commandUpdate.Parameters.Add(new SqlParameter("@id", id));

    commandUpdate.ExecuteNonQuery();
}

edited Dec 06 '19 at 03:16

answered Dec 05 '19 at 18:23

Jawad

11,028
3
24
37

My service is working fine 99% of the time. The problem only occurs when the server has some performance issue and start to generate the timeouts... Do you think that Datetime could be the problem? – Paul Dec 05 '19 at 18:45
from various other reads on stackoverflow, your commits continue until it fails with error. When error happens, you wont see the error but see the Rollback – Jawad Dec 05 '19 at 18:46
Since your transaction has multiple ExecuteNonQuery, if eitehr of them fail, you'll have rollback. You will have to unit test each of them to make sure they work – Jawad Dec 05 '19 at 18:47
You can also increase the timeout value for your sqlconnection in the connectionString: "Data Source=(local);Initial Catalog=AdventureWorks;" + "Integrated Security=SSPI;Connection Timeout=60"; – Jawad Dec 05 '19 at 18:52
@Paul Increase the timeout for your transaction, usually for stuff like this, i put it in a while loop for retries (5-10) time's, you can set some wait period before retries. Make sure you log that it failed, but retry commited data. I use this method to handle deadlocks also if DB gets busy – Djuro Dec 11 '19 at 14:21

score 0 · Answer 2 · answered Dec 10 '19 at 13:58

I think that the using statement for your bulk copy is too broad - it should be disposed of when complete instead of waiting until after the following two updates:

using (var transactionSql = connection.BeginTransaction())
{

  try
  {
      // Updating table1
      var commandUpd1 = new SqlCommand(query, connection, transactionSql);
      commandUpd1.ExecuteNonQuery();

      // Inserting table2
      var commandInsert1 = new SqlCommand(query2, connection, transactionSql);
      commandInsert1.ExecuteNonQuery();

      using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transactionSql))
      { 
         // My bulk on table3
         bulkCopy.WriteToServer(dataTable);
      }
      // Updating table1
      var commandUpd2 = new SqlCommand(query3, connection, transactionSql);
      commandUpd2.ExecuteNonQuery();

      // Inserting table2
      var commandInsert3 = new SqlCommand(query4, connection, transactionSql);
      commandInsert3.ExecuteNonQuery();

      transactionSql.Commit();  // Timeout   
 }
 catch (Exception ex)
 {
      transactionSql.Rollback();  //Zombie error

      throw ex;
 }

}

SqlTransaction Zombie error after a timeout

2 Answers2