SqlTransaction and Deadlocks

void Main()
{
   var connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Scratch;Integrated Security=True";

   var sqlSchema = @"
      DROP TABLE dbo.orders;
      DROP TABLE dbo.customer2;
      DROP TABLE dbo.customer;

      CREATE TABLE [dbo].[customer](
         [customerid] [int] NOT NULL,
         [firstname] [nvarchar](256) NULL,
         [lastname] [nvarchar](256) NULL,
         CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
            ([customerid] ASC) ON [PRIMARY]
      ) ON [PRIMARY];

      CREATE TABLE [dbo].[orders](
         [orderid] [int] NOT NULL,
         [customerid] [int] NOT NULL,
         [shippingid] [int] NOT NULL,
         [otherid] [int] NULL,
         CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
         ([orderid] ASC) ON [PRIMARY]
      ) ON [PRIMARY];

      ALTER TABLE [dbo].[orders] WITH CHECK
         ADD CONSTRAINT [FK_orders_customer] FOREIGN KEY([customerid])
         REFERENCES [dbo].[customer] ([customerid]);

      CREATE TABLE [dbo].[customer2](
         [customerid] [int] NOT NULL,
         [firstname] [nvarchar](256) NULL,
         [lastname] [nvarchar](256) NULL,
         CONSTRAINT [PK_customer2] PRIMARY KEY CLUSTERED
            ([customerid] ASC) ON [PRIMARY]
      ) ON [PRIMARY];

      insert into customer (customerid, firstname, lastname) values (111, 'FN', 'LN');
      insert into orders (orderid, customerid, shippingid, otherid) values (221, 111, 0, 0);
      insert into customer2 (customerid, firstname, lastname) values (111, 'FN', 'LN');
      insert into customer2 (customerid, firstname, lastname) values (112, 'FN', 'LN');
   ";

   // Change this to a different value in order to see
   //whether this run affected rows in the database.
   var runId = "9";

   // The following statements will force a deadlock.
   var sql1a = @"
      UPDATE Customer SET LastName = 'John_Updated" + runId + @"' WHERE CustomerId=111
      WAITFOR DELAY '00:00:03' -- Wait for 5 ms
      UPDATE Orders SET OtherId = " + runId + @" WHERE OrderId = 221";

   var sql2a = @"
      UPDATE Orders SET ShippingId = 1" + runId + @" WHERE OrderId = 221
      WAITFOR DELAY '00:00:03' -- Wait for 5 ms
      UPDATE Customer SET FirstName = 'Mike_Updated" + runId + @"' WHERE CustomerId=111";


   // These two statements affect a table that should not be locked by the deadlock.
   var sql1c = @"UPDATE Customer2 SET LastName = 'Updated 1_" + runId + @"' WHERE CustomerId = 111";
   var sql2c = @"UPDATE Customer2 SET LastName = 'Updated 1_" + runId + @"' WHERE CustomerId = 112";


   using (var con1 = new SqlConnection(connectionString))
   using (var con2 = new SqlConnection(connectionString))
   using (var con3 = new SqlConnection(connectionString))
   {
      con1.Open();
      con2.Open();
      con3.Open();
      using (var tran1 = con1.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
      using (var tran2 = con2.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
      {
         try
         {
            // Create tables and insert sample data.
            SqlRunner.RunSQL(con3, null, sqlSchema);

            // Show sample data
            SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER")
                     .Dump("Data in CUSTOMER before changes");
            SqlRunner.GetDataTable(con3, null, "Select * from ORDERS")
                     .Dump("Data in ORDERS before changes");
            SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER2")
                     .Dump("Data in CUSTOMER2 before changes");

            Console.WriteLine("Running SQL 1A");
            var t1 = Task.Factory.StartNew(() => SqlRunner.RunSQL(con1, tran1, sql1a));

            // Allow slight delay to ensure deadlock.
            System.Threading.Thread.Sleep(1000);

            Console.WriteLine("Running SQL 2A");
            var t2 = Task.Factory.StartNew(() => SqlRunner.RunSQL(con2, tran2, sql2a));

            Console.WriteLine("Waiting");
            Task.WaitAll(t1, t2);   // This should throw SqlException for Deadlock.
            t1.Wait();

            // The following should not run.
            // But it's what would run if things succeeded.
            Console.WriteLine("Committing");
            tran1.Commit();
            tran2.Commit();
         }
         catch (Exception ex)
         {
            Console.WriteLine("Error:");
            Console.WriteLine(ex.ToString());

            var sqlEx = ex.InnerException as SqlException;
            if (null != sqlEx)
            {
               Console.WriteLine("SqlException Details:");   // Should be deadlock
               Console.WriteLine("Class     = {0}", sqlEx.Class);   // 13
               Console.WriteLine("Number    = {0}", sqlEx.Number); // 1205 = Deadlock
               Console.WriteLine("Procedure = {0}", sqlEx.Procedure); // ""
               Console.WriteLine("Server    = {0}", sqlEx.Server); // .\sqlexpress
               Console.WriteLine("Source    = {0}", sqlEx.Source); // .Net SqlClient Data Provider
               Console.WriteLine("State     = {0}", sqlEx.State);    // 51
            }

            // Should be open. Deadlocks don't close the connection.
            Console.WriteLine("Connection States (Should be Open):");
            Console.WriteLine("Con1 State = {0}", con1.State);
            Console.WriteLine("Con2 State = {0}", con1.State);

            Console.WriteLine("Transaction States Before 'C' SQL Statements:");
            SqlRunner.GetDataTable(con1, tran1,
                     "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
                     .Dump("Con1 State After Exception, Before '1C'");
            SqlRunner.GetDataTable(con2, tran2,
                    "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
                    .Dump("Con2 State After Exception, Before '2C'");

            try
            {
               // ******************************************
               // THESE ARE THE IMPORTANT LINES.
               // How will they behave after the deadlock
               // ******************************************
               SqlRunner.RunSQL(con1, tran1, sql1c);
               SqlRunner.RunSQL(con2, tran2, sql2c);
            }
            finally
            {
               Console.WriteLine("Transaction States After 'C' SQL Statements:");
               SqlRunner.GetDataTable(con1, tran1,
                        "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
                        .Dump("Con1 State After Run '1C' SQL");
               SqlRunner.GetDataTable(con2, tran2,
                        "SELECT @@TRANCOUNT TranCount, XACT_STATE() TranState")
                        .Dump("Con2 State After Run '2C' SQL");
            }

            try { Console.WriteLine("Rolling Back 1"); tran1.Rollback(); }
            catch (Exception ex1) {
              Console.WriteLine("Rollback 1 Error:"); Console.WriteLine(ex1.ToString()); }
            try { Console.WriteLine("Rolling Back 2"); tran2.Rollback(); }
            catch (Exception ex2) {
              Console.WriteLine("Rollback 2 Error:"); Console.WriteLine(ex2.ToString()); }

            //try { Console.WriteLine("Committing 1"); tran1.Commit(); }
            //catch (Exception ex1) {
            //  Console.WriteLine("Commit 1 Error:"); Console.WriteLine(ex1.ToString()); }
            //try { Console.WriteLine("Committing 2"); tran2.Commit(); }
            //catch (Exception ex2) {
            //  Console.WriteLine("Commit 2 Error:"); Console.WriteLine(ex2.ToString()); }

            SqlRunner.GetDataTable(con3, null, "Select * from CUSTOMER")
                     .Dump("Data in CUSTOMER after ROLLBACK");
            SqlRunner.GetDataTable(con3, null, "Select * from ORDERS")
                     .Dump("Data in ORDERS after ROLLBACK");
            SqlRunner.GetDataTable(con3, null, "Select * from customer2")
                     .Dump("Data in CUSTOMER2 after ROLLBACK");
         }
      }
   }
}

// Define other methods and classes here


static class SqlRunner
{

   public static int RunSQL(SqlConnection con, SqlTransaction tran, string sql)
   {
      SqlCommand cmd = con.CreateCommand();
      cmd.CommandText = sql;

      if (null != tran)
         cmd.Transaction = tran;

      return cmd.ExecuteNonQuery();
   }

   public static DataTable GetDataTable(SqlConnection con, SqlTransaction tran, string sql, params object[] parameters)
   {
      var cmd = con.CreateCommand();
      cmd.CommandText = sql;

      if (null != tran)
         cmd.Transaction = tran;

      if (null != parameters && parameters.Length > 0)
      {
         for (int i = 0; i < parameters.Length; i++)
         {
            cmd.Parameters.AddWithValue("@p" + i.ToString(CultureInfo.InvariantCulture), parameters[i]);
         }
      }

      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataTable tbl = new DataTable();
      tbl.Locale = CultureInfo.CurrentCulture;
      da.Fill(tbl);
      return (tbl);
   }

}