It looks like the transaction was only executed halfway or like a broken rollback.

But we were using Microsoft SQL Server and I could not imagine the existence of a bug that does such a thing and wasn’t already fixed to the current version.

By luck I got a clue and I could fix it in our code. Here is what happened:

The database

For the sake of simplicity let’s use this database with just two tables:

• Table Data
  • Id (PK, int, not null)
  • Data (varchar(50), not null)
• Table Log
  • Id (PK, uniqueidentifier, not null, autovalue)
  • Text (varchar(max), not null)

The code

try
{
 using (var transaction = new TransactionScope(TransactionScopeOption.Required))
 using (var connection = new SqlConnection(ConnectionString)) 

 {
 connection.Open();

    var insertDataCmd = connection.CreateCommand();
    insertDataCmd.CommandText = "INSERT INTO Data(Id, Data) VALUES(1, 'Foo')";

    try
    {
          insertDataCmd.ExecuteNonQuery();
    }
    catch (SqlException e)
    {
         Console.WriteLine(e.Message);
         // for some reason i don't care for this PK conflict
     }

    var insertLogCmd = connection.CreateCommand();
    insertLogCmd.CommandText = "INSERT INTO Log(Text) VALUES('this should not find its way to the database')";
    insertLogCmd.ExecuteNonQuery();     
    // don't commit this transaction       transaction.Complete();
 }
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

We use TransactionScope to handle transactions. The first INSERT (with Id = 1) will throw an exception since a record with this id already exists. I don’t care about this exception, catch it and go on. Now I insert something into the log table. Finally, I will not commit the transaction at all.

This is a very artificial program to show just one event that has happened.

After running this piece of code I can find a new record in the log table even if the transaction was never committed! – That scared me!

The analysis

The first insert does not only throw a primary-key-already-exists-exception but the SQL Server itself aborted the transaction at this point. The following commands are executed without any transaction or transaction scope at all. I couldn’t imagine that this can happen with that code!

The reason for this behavior of SQL Server was a trigger like this:

CREATE TRIGGER InsteadOfInsertIntoData
 ON [Data]
 INSTEAD OF INSERT
 AS
 BEGIN
   INSERT INTO [Data] (Id, Data)
   SELECT
   Id,
   [Data]
   FROM inserted
 END
 GO

On tables without such a trigger my code works great. I can catch the exception and go on.

With this trigger, the PK error does not raise in user code but within the trigger. If an error happens within a trigger the transaction is always aborted.

(Please don’t start a discussion about this trigger itself. I don’t like it, but it was in the database. Cannot argue about it.)

Maybe with more explicated transaction handling

A colleague pointed out, that TransactionScope may be the problem and I should try explicit transaction handling with the pain old Transaction-class.

try
{
 using (var connection = new SqlConnection(ConnectionString))
 {
   connection.Open();
   var transaction = connection.BeginTransaction();
   var insertDataCmd = connection.CreateCommand();
   insertDataCmd.CommandText = "INSERT INTO Data(Id, Data) VALUES(1, 'Foo')";
   insertDataCmd.Transaction = transaction;

   try
   {
       insertDataCmd.ExecuteNonQuery();
   }
   catch (SqlException e)
   {
       Console.WriteLine(e.Message);
       // for some reason i don't care for this PK conflict
   }
    
    var insertLogCmd = connection.CreateCommand();
    insertLogCmd.CommandText = "INSERT INTO Log(Text) VALUES('this should not find its way to the database')";
    insertLogCmd.Transaction = transaction;
    insertLogCmd.ExecuteNonQuery();
    transaction.Rollback();
  }
}
catch (Exception e)
{
      Console.WriteLine(e.Message);
}

But it does not change much: the INSERT INTO Log got written to the database even with the Rollback() call at the end.

But you can observe something interesting on the transaction instance after SQL Server has aborted the transaction: The transaction-object got a private (!) property names “IsZombied” that got true afterwards. Buts its private, so I cannot access it in my code.

At the same moment the public property Connection of transaction lost its value and got null. A clue one could check but not very explicit and I don’t like to check for such a property every time I want to call ExectuteNonQuery().

A better solution (?)

I figured out that I can dig deeper into the SqlException and check if this exception aborted my transaction:

private static bool WasTransactionAborted(SqlException ex)
{
  foreach (SqlError error in ex.Errors)
  {
     // The statement has been terminated.
     if (error.Number == 3621)
        return true;
  }
  return false;
}

If any of the SqlErrors within the SqlException was of number 3621 then the transaction is dead and I don’t want to run any more code against my SQL Server connection.

The code from above can be changed at the point where I catch (and ignore) the exception to check for the described condition and don’t ignore that one.

 try
 { 
    cmd.ExecuteNonQuery();
 }
 catch (SqlException e) when (WasTransactionAborted(e))
 {
    Console.WriteLine("That's it - sorry - over and out");
    throw;
 }
 catch(SqlException e)
 {
    Console.WriteLine(e.Message);
    // for some reason I don't care for this PK conflict
 }

If the transaction was aborted we re-throw the exception end quit this method. Otherwise I can safely ignore the exception and go on.