Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement