Q: "SQL run using a SqlTransaction is part of a SQL Server Transaction and can be rolled back."

A) True
B) False

The answer is of course "A", right? That's what I thought too.

Basic SqlTransaction Usage

Most of us assume that we can write this:

using (var con = new SqlConnection(".."))
{
  con.Open();
 
  using (var tran = con.BeginTransaction())
  {
    var cmd = con.CreateCommand();
    cmd.CommandText = "Update dbo.Customer ...";
    cmd.Transaction = tran;

    try
    {
      cmd.ExecuteNonQuery();
      tran.Commit();
    } 
    catch
    {
      tran.Rollback();
    }    
  }
}

This is plain and simple SqlTransaction based logic. Exec the command. If it completes without error, call Commit(). If it has an issue, then call Rollback();

But ... What Does This Do?

I challenge you to predict the behavior of the following "Retry" scenario. The code may seem a bit odd… Why would you execute more SQL after an exception? But it shows what one might do if the code implemented Retry semantics inside a transaction.

For retries, one might want to open a transaction, run a set of statements, and then retry any statements that had failed. The SQL transaction should remain in force in spite of any errors thrown by SQL server. For example, a foreign key violation does not automatically abort the transaction. Rather, the code would catch the FK violation and queue it for subsequent execution after completing the 1st pass.

This does in fact work… most of the time. I say most of the time, because deadlock errors in SQL Server cause some interesting behavior.

Here's a snippet that should easily run in LinqPad without too much work. The only pre-requisite is to create a DB in SqlExpress named "Scratch".

Pay close attention to lines 144-145. How will they behave after the deadlock? What is their impact on the database?

   1:      void Main()
   2:      {
   3:         var connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Scratch;Integrated Security=True";
   4:         
   5:         var sqlSchema = @"
   6:           DROP TABLE dbo.orders;
   7:           DROP TABLE dbo.customer2;
   8:           DROP TABLE dbo.customer;
   9:           
  10:           CREATE TABLE [dbo].[customer](
  11:              [customerid] [int] NOT NULL,
  12:              [firstname] [nvarchar](256) NULL,
  13:              [lastname] [nvarchar](256) NULL,
  14:              CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
  15:                 ([customerid] ASC) ON [PRIMARY]
  16:           ) ON [PRIMARY];
  17:           
  18:           CREATE TABLE [dbo].[orders](
  19:              [orderid] [int] NOT NULL,
  20:              [customerid] [int] NOT NULL,
  21:              [shippingid] [int] NOT NULL,
  22:              [otherid] [int] NULL,
  23:              CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
  24:              ([orderid] ASC) ON [PRIMARY]
  25:           ) ON [PRIMARY];
  26:           
  27:           ALTER TABLE [dbo].[orders] WITH CHECK
  28:              ADD CONSTRAINT [FK_orders_customer] FOREIGN KEY([customerid])
  29:              REFERENCES [dbo].[customer] ([customerid]);
  30:           
  31:           CREATE TABLE [dbo].[customer2](
  32:              [customerid] [int] NOT NULL,
  33:              [firstname] [nvarchar](256) NULL,
  34:              [lastname] [nvarchar](256) NULL,
  35:              CONSTRAINT [PK_customer2] PRIMARY KEY CLUSTERED
  36:                 ([customerid] ASC) ON [PRIMARY]
  37:           ) ON [PRIMARY];
  38:           
  39:           insert into customer (customerid, firstname, lastname) values (111, 'FN', 'LN');
  40:           insert into orders (orderid, customerid, shippingid, otherid) values (221, 111, 0, 0);
  41:           insert into customer2 (customerid, firstname, lastname) values (111, 'FN', 'LN');
  42:           insert into customer2 (customerid, firstname, lastname) values (112, 'FN', 'LN');
  43:        ";
  44:         
  45:         // Change this to a different value in order to see
  46:         //whether this run affected rows in the database.
  47:         var runId = "9";  
  48:       
  49:         // The following statements will force a deadlock.
  50:         var sql1a = @"
  51:           UPDATE Customer SET LastName = 'John_Updated" + runId + @"' WHERE CustomerId=111
  52:           WAITFOR DELAY '00:00:03' -- Wait for 5 ms
  53:           UPDATE Orders SET OtherId = " + runId + @" WHERE OrderId = 221";
  54:       
  55:         var sql2a = @"
  56:           UPDATE Orders SET ShippingId = 1" + runId + @" WHERE OrderId = 221
  57:           WAITFOR DELAY '00:00:03' -- Wait for 5 ms
  58:           UPDATE Customer SET FirstName = 'Mike_Updated" + runId + @"' WHERE CustomerId=111";
  59:       
  60:         
  61:         // These two statements affect a table that should not be locked by the deadlock.
  62:         var sql1c = @"UPDATE Customer2 SET LastName = 'Updated 1_" + runId + @"' WHERE CustomerId = 111";
  63:         var sql2c = @"UPDATE Customer2 SET LastName = 'Updated 1_" + runId + @"' WHERE CustomerId = 112";
  64:       
  65:         
  66:         using (var con1 = new SqlConnection(connectionString))
  67:         using (var con2 = new SqlConnection(connectionString))
  68:         using (var con3 = new SqlConnection(connectionString))
  69:         {
  70:            con1.Open();
  71:            con2.Open();
  72:            con3.Open();
  73:            using (var tran1 = con1.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
  74:            using (var tran2 = con2.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
  75:            {
  76:               try
  77:               {
  78:                  // Create tables and insert sample data.
  79:                  SqlRunner.RunSQL(con3, null, sqlSchema);
  80:                 
  81:                  // Show sample data
  82:                  SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER")
  83:                           .Dump("Data in CUSTOMER before changes");
  84:                  SqlRunner.GetDataTable(con3, null, "Select * from ORDERS")
  85:                           .Dump("Data in ORDERS before changes");
  86:                  SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER2")
  87:                           .Dump("Data in CUSTOMER2 before changes");
  88:       
  89:                  Console.WriteLine("Running SQL 1A");
  90:                  var t1 = Task.Factory.StartNew(() => SqlRunner.RunSQL(con1, tran1, sql1a));  
  91:                 
  92:                  // Allow slight delay to ensure deadlock.
  93:                  System.Threading.Thread.Sleep(1000);
  94:                 
  95:                  Console.WriteLine("Running SQL 2A");
  96:                  var t2 = Task.Factory.StartNew(() => SqlRunner.RunSQL(con2, tran2, sql2a));
  97:                 
  98:                  Console.WriteLine("Waiting");
  99:                  Task.WaitAll(t1, t2);   // This should throw SqlException for Deadlock.
 100:                  t1.Wait();
 101:       
 102:                  // The following should not run.
 103:                  // But it's what would run if things succeeded.
 104:                  Console.WriteLine("Committing");
 105:                  tran1.Commit();
 106:                  tran2.Commit();
 107:               }
 108:               catch (Exception ex)
 109:               {
 110:                  Console.WriteLine("Error:");
 111:                  Console.WriteLine(ex.ToString());
 112:                 
 113:                  var sqlEx = ex.InnerException as SqlException;
 114:                  if (null != sqlEx)
 115:                  {
 116:                     Console.WriteLine("SqlException Details:");   // Should be deadlock
 117:                     Console.WriteLine("Class     = {0}", sqlEx.Class);   // 13
 118:                     Console.WriteLine("Number    = {0}", sqlEx.Number); // 1205 = Deadlock
 119:                     Console.WriteLine("Procedure = {0}", sqlEx.Procedure); // ""
 120:                     Console.WriteLine("Server    = {0}", sqlEx.Server); // .\sqlexpress
 121:                     Console.WriteLine("Source    = {0}", sqlEx.Source); // .Net SqlClient Data Provider
 122:                     Console.WriteLine("State     = {0}", sqlEx.State);    // 51
 123:                  }
 124:                 
 125:                  // Should be open. Deadlocks don't close the connection.
 126:                  Console.WriteLine("Connection States (Should be Open):");
 127:                  Console.WriteLine("Con1 State = {0}", con1.State);
 128:                  Console.WriteLine("Con2 State = {0}", con1.State);
 129:                 
 130:                  Console.WriteLine("Transaction States Before 'C' SQL Statements:");
 131:                  SqlRunner.GetDataTable(con1, tran1,
 132:                           "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
 133:                           .Dump("Con1 State After Exception, Before '1C'");        
 134:                  SqlRunner.GetDataTable(con2, tran2,
 135:                          "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
 136:                          .Dump("Con2 State After Exception, Before '2C'");        
 137:                 
 138:                  try
 139:                  {
 140:                     // ******************************************
 141:                     // THESE ARE THE IMPORTANT LINES.
 142:                     // How will they behave after the deadlock
 143:                     // ******************************************
 144:                     SqlRunner.RunSQL(con1, tran1, sql1c);        
 145:                     SqlRunner.RunSQL(con2, tran2, sql2c);        
 146:                  }
 147:                  finally
 148:                  {
 149:                     Console.WriteLine("Transaction States After 'C' SQL Statements:");
 150:                     SqlRunner.GetDataTable(con1, tran1,
 151:                              "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
 152:                              .Dump("Con1 State After Run '1C' SQL");        
 153:                     SqlRunner.GetDataTable(con2, tran2,
 154:                              "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
 155:                              .Dump("Con2 State After Run '2C' SQL");        
 156:                  }
 157:       
 158:                  try { Console.WriteLine("Rolling Back 1"); tran1.Rollback(); }
 159:                  catch (Exception ex1) {
 160:                    Console.WriteLine("Rollback 1 Error:"); Console.WriteLine(ex1.ToString()); }
 161:                  try { Console.WriteLine("Rolling Back 2"); tran2.Rollback(); }
 162:                  catch (Exception ex2) {
 163:                    Console.WriteLine("Rollback 2 Error:"); Console.WriteLine(ex2.ToString()); }        
 164:       
 165:                  //try { Console.WriteLine("Committing 1"); tran1.Commit(); }
 166:                  //catch (Exception ex1) {
 167:                  //  Console.WriteLine("Commit 1 Error:"); Console.WriteLine(ex1.ToString()); }
 168:                  //try { Console.WriteLine("Committing 2"); tran2.Commit(); }
 169:                  //catch (Exception ex2) {
 170:                  //  Console.WriteLine("Commit 2 Error:"); Console.WriteLine(ex2.ToString()); }        
 171:       
 172:                  SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER")
 173:                           .Dump("Data in CUSTOMER after ROLLBACK");
 174:                  SqlRunner.GetDataTable(con3, null, "Select * from ORDERS")
 175:                           .Dump("Data in ORDERS after ROLLBACK");
 176:                  SqlRunner.GetDataTable(con3, null, "Select * from customer2")
 177:                           .Dump("Data in CUSTOMER2 after ROLLBACK");
 178:               }
 179:            }
 180:         }  
 181:      }
 182:       
 183:      // Define other methods and classes here
 184:       
 185:       
 186:      static class SqlRunner
 187:      {
 188:       
 189:         public static int RunSQL(SqlConnection con, SqlTransaction tran, string sql)
 190:         {
 191:            SqlCommand cmd = con.CreateCommand();
 192:            cmd.CommandText = sql;
 193:       
 194:            if (null != tran)
 195:               cmd.Transaction = tran;
 196:       
 197:            return cmd.ExecuteNonQuery();
 198:         }
 199:       
 200:         public static DataTable GetDataTable(SqlConnection con, SqlTransaction tran, string sql, params object[] parameters)
 201:         {
 202:            var cmd = con.CreateCommand();
 203:            cmd.CommandText = sql;
 204:       
 205:            if (null != tran)
 206:               cmd.Transaction = tran;
 207:       
 208:            if (null != parameters && parameters.Length > 0)
 209:            {
 210:               for (int i = 0; i < parameters.Length; i++)
 211:               {
 212:                  cmd.Parameters.AddWithValue("@p" + i.ToString(CultureInfo.InvariantCulture), parameters[i]);
 213:               }
 214:            }
 215:       
 216:            SqlDataAdapter da = new SqlDataAdapter(cmd);
 217:            DataTable tbl = new DataTable();
 218:            tbl.Locale = CultureInfo.CurrentCulture;
 219:            da.Fill(tbl);
 220:            return (tbl);
 221:         }
 222:       
 223:      }

(Download from Pastebin)

Shocked, Stupefied, and Generally Stumped

If you run the above code and look at the data that ends up in [dbo].[customer2], you'll notice a very strange thing:

The transactions were rolled back, but one of the "C" statement was still applied!

WHY???

Short Answer…

The Deadlock aborts the underlying transaction on SQL Server, but SqlTransaction and SqlCommand happily continue to execute more commands outside of any transaction.

Hindsight is 20/20

After diagnosing and fixing this issue, it almost makes sense, but it's not at all intuitive up front. I asked a couple colleagues to guess the outcome of the code, and both guessed wrong. I suspect that most of you will also be surprised by the results. Hindsight is 20/20, but up front, this one is nonsense.

Deadlocks in SQL server mean that SQL will kill one of the transactions and roll it back. This frees the locks and allows the other transaction to proceed. In the scenario above, we force a deadlock, meaning that one of the transactions is ROLLED BACK on the server. It's not just "zombied", which would cause "SELECT XACT_STATE()" to return "-1". No, in this case, the transaction is done. There is no more transaction.

So, how does one of those "C" statements get applied? Basically, SqlCommand doesn't know that there is a problem with the transaction on the server, nor does it ask the SqlTransaction object to check. Instead, it just sends the statement to the server, assuming that the server still has a transaction open on the current connection. But there is no transaction. As a result, those statements are executed with no transaction at all. The subsequent Rollback() has no means to undo their work.

Is This a Bug?

Calling something a bug is rather severe. In this case, I suspect that the framework can do better.

The system does some checks to ensure proper & safe usage of the SqlTransaction, but not enough. After a deadlock, the client still has a reference to a SqlTransaction object. This object still thinks that it has a transaction on the server. It will even allow you to call Rollback() on this transaction object after the deadlock. But it won't let you call Commit(). It seems that SqlTransaction knows that the transaction can't commit, and throws an error back at you. Additionally, if you use a SqlConnection with a SqlCommand, but don't pass the associated SqlTransaction, then it will throw an error.

So the system does some internal checks, but it doesn't check for the deadlock scenario. Well, I think it should do better. Before executing a command, it should check whether the associated transaction is gone. If the server transaction is gone, it shouldn't even run the command. It should just error. Otherwise, SqlCommand will continue to run the statements outside of any transaction.

I don't know if this is possible, or if it has some sever side-effects, but the current behavior of SqlTransaction is a very dangerous and sharp edge on the API surface of the .NET framework. A transaction that doesn't actually roll back the associated commands is not good.

Solution: Catch Deadlock Errors and Halt

The solution seems to require catching the SqlException, inspecting the Number to see if it's a deadlock, and then taking an alternate course of action. In the case of retry logic, the processing should immediately stop and rollback to prevent any subsequent commands from executing outside a transaction.

To catch a deadlock, look for SqlException.Number == 1205. This indicates a deadlock occurred.

Happy (Transaction) Coding!

Cheers!