Demonstrating a SQL deadlock with .NET

 

var conn1 = new SqlConnection(cs);
 conn1.Open();
 var comm1 = conn1.CreateCommand();
 comm1.CommandText = "Proc1;";
 new Thread(() => {
    try {
            Console.WriteLine("Executing comm1");
            comm1.ExecuteNonQuery();
            Console.WriteLine("comm1 done");
    } catch (Exception ex) {
            Console.WriteLine("comm1: " + ex);
    }
 }).Start();


 var conn2 = new SqlConnection(cs);
 conn2.Open();
 var comm2 = conn2.CreateCommand();
 comm2.CommandText = "Proc2";
 new Thread(() => {
         try {
                 Console.WriteLine("Executing comm2");
                 comm2.ExecuteNonQuery();
                 Console.WriteLine("comm2 done");
         } catch (Exception ex) {
                 Console.WriteLine("comm2: " + ex);
         }
 }).Start();


 var conn3 = new SqlConnection(cs);
 conn3.Open();
 var comm3 = conn3.CreateCommand();
 comm3.CommandText = "Proc3;";
 new Thread(() => {
         try {
                 Console.WriteLine("Executing comm3");
                 comm3.ExecuteNonQuery();
                 Console.WriteLine("comm3 done");
         } catch (Exception ex) {
                 Console.WriteLine("comm3: " + ex);
         }
 }).Start();


 Console.Read();

-- Window 1:

begin tran;
update dbo.Foo set name = 'aa'

-- Window 2:

select * from dbo.Foo;

-- Window 3:

begin tran;
update dbo.Bar set name = 'bb';
select * from dbo.Foo;

select * from dbo.Bar;

Database schema

   create table dbo.Bar
    (
            id int identity not null primary key
            ,name nvarchar(10)
    );
    go
    create table dbo.Foo
    (
            id int identity not null primary key
            ,name nvarchar(10)
    );
    go


    insert dbo.Bar (name) values ('a');
    insert dbo.Foo (name) values ('b');


go
    create procedure dbo.Proc1
    as
    begin
            --set transaction isolation level snapshot;
            begin tran;
                    update dbo.Foo set name = 'aa';
                    waitfor delay '00:00:05';
                    select * from dbo.Bar;
            commit tran;
    end;
    go
    create procedure dbo.Proc2
    as
    begin
            --set transaction isolation level snapshot;
            select * from dbo.Foo;
    end;
    go
    create procedure dbo.Proc3
    as
    begin
            --set transaction isolation level snapshot;
            begin tran;
                    update dbo.Bar set name = 'bb';
                    select * from dbo.Foo;
            commit tran;
    end;
    go