Demonstrating a SQL deadlock with .NET

Posted by Joe Enos on May 12, 2021 · 9 mins read

 

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