asp.net sql server deadlock
handle deadlock in asp.net
catch deadlock exception c#
In this video we will discuss how to handle deadlock errors in an ADO.NET application.
To handle deadlock errors in ADO.NET
1. Catch the SqlException object
2. Check if the error is deadlock error using the Number property of the SqlException object
Stored Procedure 1 Code
Alter procedure spTransaction1
as
Begin
Begin Tran
Update TableA Set Name = ‘Mark Transaction 1′ where Id = 1
Waitfor delay ’00:00:05’
Update TableB Set Name = ‘Mary Transaction 1’ where Id = 1
Commit Transaction
End
Stored Procedure 2 Code
Alter procedure spTransaction2
as
Begin
Begin Tran
Update TableB Set Name = ‘Mark Transaction 2′ where Id = 1
Waitfor delay ’00:00:05’
Update TableA Set Name = ‘Mary Transaction 2’ where Id = 1
Commit Transaction
End
WebForm1.aspx.cs code
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spTransaction1”, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Transaction successful”;
Label1.ForeColor = System.Drawing.Color.Green;
}
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
Label1.Text = “Deadlock. Please retry”;
}
else
{
Label1.Text = ex.Message;
}
Label1.ForeColor = System.Drawing.Color.Red;
}
}
}
}
WebForm2.aspx.cs code
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spTransaction2”, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Transaction successful”;
Label1.ForeColor = System.Drawing.Color.Green;
}
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
Label1.Text = “Deadlock. Please retry”;
}
else
{
Label1.Text = ex.Message;
}
Label1.ForeColor = System.Drawing.Color.Red;
}
}
}
}
Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
