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

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
SQL Server
jQuery AJAX

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="">
<head runat="server">
    <script src="jquery-1.11.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var lblMessage = $('#Label1');
            var attemptsLeft;

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

            $('#btn').click(function () {
                $(this).prop('disabled', true);
                attemptsLeft = 5;
<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>

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

        public static Result CallStoredProcedure(int attemptsLeft)
            Result _result = new Result();
            if (attemptsLeft > 0)
                    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(cs))
                        SqlCommand cmd = new SqlCommand("spTransaction15", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        _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();
                    _result.Success = false;
            return _result;

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


  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! ***** //

    Any Ideas? Cheers,


  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....".

  3. I finally got the program to run. I had to use this solution from the web -

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

    From: settings.AutoRedirectMode =RedirectMode.Permanent;

    To: settings.AutoRedirectMode = RedirectMode.Off;


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