Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Handling deadlocks in ado.net

Suggested Videos
Part 81 - SQL Server deadlock analysis and prevention
Part 82 - Capturing deadlocks in SQL profiler
Part 83 - SQL Server deadlock error handling



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 HTML
<table>
    <tr>
        <td>
            <asp:Button ID="Button1" runat="server"
                Text="Update Table A and then Table B"
                OnClick="Button1_Click" />
        </td>
    </tr>
    <tr>
        <td>
            <asp:Label ID="Label1" runat="server"></asp:Label>
        </td>
    </tr>
</table>

WebForm1.aspx.cs code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

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 HTML
<table>
    <tr>
        <td>
            <asp:Button ID="Button1" runat="server"
                Text="Update Table B and then Table A"
                OnClick="Button1_Click" />
        </td>
    </tr>
    <tr>
        <td>
            <asp:Label ID="Label1" runat="server"></asp:Label>
        </td>
    </tr>
</table>

WebForm2.aspx.cs code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.