Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

SqlTransaction and Deadlocks

By: nathanaw on Mar 3rd, 2012  |  syntax: C#  |  size: 9.26 KB  |  views: 45  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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. }
clone this paste RAW Paste Data