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);
}
}