Part 17 - Concurrency in Linq to SQL

Suggested Videos
Part 14 - How to directly execute sql queries using Linq to SQL
Part 15 - Identity Cache in Linq to SQL
Part 16 - Difference between optimistic and pessimistic concurrency control



This is continuation to Part 16. Please watch Part 16 before proceeding.

In this video we will discuss how linq to sql handles concurrent updates i.e when 2 or more users try to update the same data at the same time. 



By default, linq to sql uses optimistic concurrency to handle concurrent updates. Optimistic concurrency does not involve locking rows when reading. Instead, this model checks if two users tried to update the same record at the same time. If that happens one user's changes are committed and the other user's changes are discarded and an exception will be thrown to notify the user.

Let us look at this in action with an example. We will be using the following Accounts table in this demo.


Step 1 : Create the Accounts table

Create Table Accounts

(

     AccountNumber int primary key,

     AccountName nvarchar(50),

     AccountBalance int

)

Go

Insert into Accounts values (1, 'John Mary', 1000)

Go

Step 2 : Create a new empty asp.net web application project. Name it Demo. Add a new Linq to SQL Class. Name it Sample. Drag and drop Accounts table on Sample.dbml file.

Step 3 : Add a WebForm and design it as shown below.


Here is the HTML for the web form.

<div style="font-family:Arial">

<table border="1">

    <tr>

        <td>

            <b>Account Number</b>

        </td>

        <td>

            <asp:Label ID="lblAccountNumber" runat="server"></asp:Label>

        </td>

    </tr>

    <tr>

        <td>

            <b>Account Name</b>

        </td>

        <td>

            <asp:Label ID="lblAccountName" runat="server"></asp:Label>

        </td>

    </tr>

    <tr>

        <td>

            <b>Account Balance</b>

        </td>

        <td>

            <asp:Label ID="lblAccountBalance" runat="server"></asp:Label>

        </td>

    </tr>

</table>

<br />

<asp:Button ID="btnDeposit" runat="server" Text="Deposit $500"

    onclick="btnDeposit_Click" />

</div>

Step 4 : Copy and paste the following code in WebForm1.aspx.cs

using System;

using System.Linq;

namespace Demo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                GetAccountData();

            }

        }

        private void GetAccountData()

        {

            using (SampleDataContext db = new SampleDataContext())

            {

                Account account = db.Accounts.First(x => x.AccountNumber == 1);

                lblAccountNumber.Text = account.AccountNumber.ToString();

                lblAccountName.Text = account.AccountName;

                lblAccountBalance.Text = account.AccountBalance.ToString();

            }

        }

        protected void btnDeposit_Click(object sender, EventArgs e)

        {

            using (SampleDataContext db = new SampleDataContext())

            {

                Account account = db.Accounts.First(x => x.AccountNumber == 1);

                account.AccountBalance = account.AccountBalance + 500;

                db.SubmitChanges();

                GetAccountData();

            }

        }

    }

}

At this point open SQL Profiler and run a new trace. Run the web application. Click "Deposit $500" button. Notice that the balance is updated to 1500 as expected. 

Now inspect the Update query that is generated by linq to sql

exec sp_executesql N'UPDATE [dbo].[Accounts]

SET [AccountBalance] = @p3

WHERE ([AccountNumber] = @p0) AND ([AccountName] = @p1)

AND ([AccountBalance] = @p2)',

N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 int',@p0=1,

@p1=N'John Mary',@p2=1000,@p3=1500

To update the balance of the account all we need is the New Balance and the AccountNumber that needs to be updated. In the WHERE clause of the query notice that along with AccountNumber and the New Balance, we also have AccountName and the original AccountBalance. The reason we have these is to make sure that no column values in the row have changed since we have loaded the data from the database. If any of the values have changed, then the update fails and an exception will be thrown.

Let's now simulate the scenario of 2 users updating the same record at the same time. To do this

Step 1 : Throw a break point on the line where we call db.SubmitChanges(); in btnDeposit_Click() method.

Step 2 : Run the application in Debug mode, and click "Deposit $500" button. The processing should stop just before we call SubmitChanges() method.

Step 3 : At this point open SQL Server Management Studio, and execute the following query

Update Accounts set AccountBalance = AccountBalance - 500

Where AccountNumber = 1

Step 4 : Now come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and the exception message states - Row not found or changed.