Saturday, June 8, 2013

Labels: ,

Optimistic Record locking using linq to resolve Concurrency in ASP.NET with C# - Example

Posted by Abhay Makadiya at 6/08/2013

Introduction:-


              Here I want to provide the Record locking concept in Linq to resolve the concurrency. First we have to know what is Optimistic Locking Model? And how it works?

Concurrency Problem

When multiple users tries to update same data at same at that time concurrency problems are arise that are as per following.

  • Lost Update
  • Dirty Read
  • Nonrepeatable read
  • Phantom reads.

But here we only discuss only Loss Update problem. In this problem at a time two users access the same record first user submit it changes before second user. But when second user submit his changes the he does not aware of the changes made by first user so at last second user will overwrite the changes of first user.

Example:

Users                           Name              Category        
Record in DB               Jhone               worker
User -A                                    Abhay              worker             (only change the name)
User -B                                    Jhone               supervisor       (only change the Category)
Record in DB               Jhone               supervisor       (the changes of user A is lost)           
There are two types of record locking models which are as per following.

Optimistic locking

The optimistic locking model, also known as optimistic concurrency control, is a concurrency control method used in relational databases that does not provide record locking. Optimistic locking model allows multiple users to update the same record without informing the users who are also attempting to update the record. The record changes are validated only when user submit the updated record. If one user successfully updates the record, the other users try to commit their concurrent updates at that time they are informed that a conflict exists.

An advantage of the optimistic locking is that it avoids the burden of locking a record. This model provides fast updates and also secure from Deadlock situation.

Pessimistic locking

In The pessimistic locking model two or more users can not edit record at the same time. When one user starts to edit a record, a lock is placed on record. Other users who try to edit that record are informed that another user has an update in progress so he/she cannot edit that record. The other users must wait until the first user has finished editing and submit the changes, then after releasing the record lock the other user can edit that record. An advantage of the pessimistic locking model is that it avoids the issue of confliction.

Pessimistic locking is a useful model when you need high level of consistency. But there is also possibility of Deadlock generation.

So, I thought that Optimistic lock is reliable.
Listen I had a requirement that when one user “Abhay” editing a record but he still not submitted the changes at that time one other user “Kashyap” edit that record and submit the changes. When Abhay try to submit his changes he will be informed someone change the data and new version of data is available for Abhay.

Here is my table design which I have created in database.

  • id                     int                    Unchecked (autogenerate)
  •  name               varchar(50)     Checked
  • category          varchar(50)     Checked
To use Linq first you have show older post. Here is the link for that. Add Linq to SQL File.

SourceSource Code (in asp.net):- 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    Namespace="System.Web.UI.WebControls" TagPrefix="asp" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Record Locking</title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <div>
        <asp:GridView ID="grdTerms" runat="server" AutoGenerateColumns="False" CellPadding="4"
            DataKeyNames="id" DataSourceID="EntityDataSource1" ForeColor="#333333" GridLines="None"
            OnRowDeleted="grdTerms_RowDeleted" OnRowUpdated="grdTerms_RowUpdated">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="ID" InsertVisible="False" ReadOnly="True">
                    <HeaderStyle HorizontalAlign="Left" Width="100px" />
                </asp:BoundField>
                <asp:BoundField DataField="fname" HeaderText="Name">
                    <HeaderStyle HorizontalAlign="Left" Width="175px" />
                </asp:BoundField>
                <asp:BoundField DataField="category" HeaderText="Category">
                    <HeaderStyle HorizontalAlign="Left" Width="100px" />
                </asp:BoundField>
                <asp:CommandField ButtonType="Button" ShowEditButton="True" />
                <asp:CommandField ButtonType="Button" CausesValidation="False" ShowDeleteButton="True" />
            </Columns>
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#EFF3FB" />
            <AlternatingRowStyle BackColor="White" />
            <EditRowStyle BackColor="#2461BF" />
        </asp:GridView>
   
        <asp:LinqDataSource ID="EntityDataSource1" runat="server" EnableDelete="True" EnableUpdate="True"
            ContextTypeName="dbLockDSDataContext" TableName="aby_temps">
        </asp:LinqDataSource>
        <br />
        To create new terms, enter the terms information and click Add New Terms<br />
        <asp:Label ID="lblError" runat="server" EnableViewState="False" ForeColor="Red">
        </asp:Label>
        <br />
        <br />
        <table>
            <tr>
                <td>
                    Name:
                </td>
                <td>
                    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtName"
                        Display="Dynamic" ErrorMessage="Description is a required field." ValidationGroup="Add"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td>
                    Category:
                </td>
                <td>
                    <asp:TextBox ID="txtCategory" runat="server" Width="50px">
                    </asp:TextBox>
                </td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtCategory"
                        Display="Dynamic" ErrorMessage="Due days is a required field." ValidationGroup="Add">
                    </asp:RequiredFieldValidator>
                   
                </td>
            </tr>
        </table>
        <br />
        <asp:Button ID="btnAdd" runat="server" Text="Add New Terms" ValidationGroup="Add"
            OnClick="btnAdd_Click" />
    </div>
    </form>
</body>
</html>


Code Behind (in C#):- 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class _Default : System.Web.UI.Page
{
    dbLockDSDataContext dbcontext = new dbLockDSDataContext();
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        
        aby_temp  tblObj= new aby_temp();
        tblObj.category=txtCategory.Text;
        tblObj.fname=txtName.Text;;
        try
        {
            dbcontext.aby_temps.InsertOnSubmit(tblObj);
            dbcontext.SubmitChanges();
            grdTerms.DataBind();
            txtName.Text = "";
            txtCategory.Text = "";
        }
        catch (Exception ex)
        {
            lblError.Text = "An error has occurred. " + ex.Message;
        }
    }
    protected void grdTerms_RowUpdated(object sender, GridViewUpdatedEventArgs e)
    {
        if (e.Exception != null)
        {
            if (e.Exception.GetType() ==  typeof(ChangeConflictException))
            {
                lblError.Text = "Another user has updated or deleted " +
                    "those terms. Please try again.";
            }
            else
            {
                lblError.Text = "A database error occurred. " +
                    e.Exception.Message;
                e.KeepInEditMode = true;
            }
            e.ExceptionHandled = true;
        }
    }
    protected void grdTerms_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {
        if (e.Exception != null)
        {
            if (e.Exception.GetType() == typeof(ChangeConflictException))
            {
                lblError.Text = "Another user has updated or deleted " +
                    "those terms. Please try again.";
            }
            else
            {
                lblError.Text = "A database error occurred. " +
                    e.Exception.Message;
            }
            e.ExceptionHandled = true;
        }
    }
}

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )