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

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.

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.