Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Retry logic for deadlock exceptions

Suggested Videos
Part 82 - Capturing deadlocks in SQL profiler
Part 83 - SQL Server deadlock error handling
Part 84 - Handling deadlocks in ado.net



In this video we will discuss implementing retry logic for deadlock exceptions



This is continuation to Part 84. Please watch Part 84, before proceeding.

When a transaction fails due to deadlock, we can write some logic so the system can resubmit the transaction. The deadlocks usually last for a very short duration. So upon resubmitting the transaction it may complete successfully. This is much better from user experience standpoint.

To achieve this we will be using the following technologies
C#
ASP.NET
SQL Server
jQuery AJAX

Result.cs
public class Result
{
    public int AttemptsLeft { get; set; }
    public string Message { get; set; }
    public bool Success { get; set; }
}

WebForm1.aspx HTML and jQuery code
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="jquery-1.11.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var lblMessage = $('#Label1');
            var attemptsLeft;

            function updateData() {
                $.ajax({
                    url: 'WebForm1.aspx/CallStoredProcedure',
                    method: 'post',
                    contentType: 'application/json',
                    data: '{attemptsLeft:' + attemptsLeft + '}',
                    dataType: 'json',
                    success: function (data) {
                        lblMessage.text(data.d.Message);
                        attemptsLeft = data.d.AttemptsLeft;
                        if (data.d.Success) {
                            $('#btn').prop('disabled', false);
                            lblMessage.css('color','green');
                        }
                        else if(attemptsLeft > 0){
                            lblMessage.css('color', 'red');
                            updateData();
                        }
                        else {
                            lblMessage.css('color', 'red');
                            lblMessage.text('Deadlock Occurred. ZERO attempts left. Please try later');
                        }
                    },
                    error: function (err) {
                        lblMessage.css('color', 'red');
                        lblMessage.text(err.responseText);
                    }
                });
            }

            $('#btn').click(function () {
                $(this).prop('disabled', true);
                lblMessage.text('Updating....');
                attemptsLeft = 5;
                updateData();
            });
        });
    </script>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
        <input id="btn" type="button"
            value="Update Table A and then Table B" />
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label>
    </form>
</body>
</html>

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

        [System.Web.Services.WebMethod]
        public static Result CallStoredProcedure(int attemptsLeft)
        {
            Result _result = new Result();
            if (attemptsLeft > 0)
            {
                try
                {
                    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(cs))
                    {
                        SqlCommand cmd = new SqlCommand("spTransaction15", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        _result.Message = "Transaction successful";
                        _result.AttemptsLeft = 0;
                        _result.Success = true;
                    }
                }
                catch (SqlException ex)
                {
                    if (ex.Number == 1205)
                    {
                        _result.AttemptsLeft = attemptsLeft - 1;
                        _result.Message = "Deadlock occurred. Retrying. Attempts left : "
                            + _result.AttemptsLeft.ToString();
                    }
                    else
                    {
                        throw;
                    }
                    _result.Success = false;
                }
            }
            return _result;
        }
    }
}

Copy and paste the above code in WebForm2.aspx and make the required changes as described in the video.

3 comments:

  1. Hey Venkat,

    When I run this Part 85 example, the Webform1.aspx just hangs after displaying "Updating ...". I know it gets into the success anonymous function(), because I added a text message to see if it did (see snippet). It never executes the "lblMessage.text(data.d.Message);" statement, though. It just stops there. I'm running Visual Studio 2015 and jQuery 3.1.1 (I also jQuery 1.11.2, but had the same problem).

    Snippet ...

    success: function (data) {
    lblMessage.css('color', 'magenta');
    lblMessage.text('Now at the top in Webform1\'s success anonymous() function ...');
    // ***** We\'ve reached here successfully! ***** //
    lblMessage.text(data.d.Message);


    Any Ideas? Cheers,

    David

    david.bailey@technologist.com

    ReplyDelete
  2. When I click the button on Webform1 or Webform2, I get the message "Deadlock Occurred. ZERO attempts left. Please try later" immediately. It does not say "Updating....".

    ReplyDelete
  3. I finally got the program to run. I had to use this solution from the web -
    ~/App_Start/RouteConfig.cs.

    The followint line of code in this file must be changed:

    From: settings.AutoRedirectMode =RedirectMode.Permanent;

    To: settings.AutoRedirectMode = RedirectMode.Off;

    ReplyDelete

It would be great if you can help share these free resources