Overview Of Concurrency In LINQ To SQL

Akshay Phadke
Jan 10, 2017
5.3k
0
4
Article

In this article, we will see

Difference between optimistic and pessimistic concurrency control.
Concurrency control in LINQ to SQL.
Handling change conflict exception.
Update check property.
Using ROWVERSION or TIMESTAMP to detect concurrency.

Let’s start.

Part One

Why is concurrency control required?

Concurrency control is required to prevent two users from trying to update the same data at the same time. It can also prevent one user from seeing out-of-date data while another user is updating the same data.

Let us understand with an example here.

A and B have a joint account. Now, the balance in the account is Rs 1000. A and B visit different branches. A wants to deposit Rs. 500 and B wants to withdraw Rs. 500.

The following are the transactions at the bank branches without concurrency control.

At the end of both the transactions, the account balance must be Rs 1000 (1000 - 500 + 500), but the balance now is Rs. 1500 which is incorrect. This happened because two users updated the balance at the same time and since there is no concurrency control in place, the second update has overwritten the changes made by the first update. This is a concurrency related problem and is called as "Lost updates" problem. There are several other concurrency related problems which we will see in this article.

With the same example, let us now understand what can happen if there is some sort of concurrency control in place.

The following are the transactions at the bank branches with concurrency control in place

In this example, the account is locked while A is processing the transaction. The lock is released only after A’s transaction is finished. After the lock has been released, B can proceed with her transaction. Since we have a concurrency control in place, we prevented two users from updating the balance at the same time which also prevented lost updates. So, the balance is updated correctly as expected.

What is the difference between optimistic and pessimistic concurrency control

Pessimistic concurrency involves locking rows to prevent other users from modifying the same data at the same time. Until the lock is released by the lock owner, no other users will be able to access that data. Pessimistic locking can very easily lead to performance bottle necks in an application.
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.

So, these were the differences between Pessimistic concurrency and Optimistic concurrency. Now, in the next part, we will see how to handle concurrent updates.

Part Two

In this part, we will discuss how LINQ to SQL handles concurrent updates; i.e., when two 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’s prove this with an example. We will be creating accounts table and inserting some dummy data.

Create Table Accounts
(
AccountNumber int primary key,
AccountName nvarchar(50),
AccountBalance int
)
Go
Insert into Accounts values (1, 'Akshay Phadke', 1000)
Go

Now, let’s create an Empty Web application and give a suitable name. Add LINQ to SQL class file and drag and drop the table to the designer file.

Now, let’s create a webform and design it as below.

Webform1.aspx code

<div style=>
<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 Rs500"
onclick="btnDeposit_Click" />
</div>

Now, in code behind, double click on the deposit button to generate event handler.

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();
}
}

Write the code for GetAccount Data on page load as given below.

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();
}
}

Code for webform1.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetAccountData();
}
}
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();
}
}
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();
}
}

At this point, open SQL Profiler and run a new trace. Run the web application.

Click on Deposit Rs 500 button and observe the SQL Server profiler. 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'Akshay Phadke',@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.

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

Part3 Handling Change Conflict Exceptions

There are 3 options available to handle ChangeConflictException. RefreshMode enum values define how to handle optimistic concurrency conflicts. This enum has 3 values,

KeepCurrentValues
Keeps all the current changes made by the current user in the DataContext object. SubmitChanges() method will save all changes made by the current user, overwriting any changes made by other users after the data was loaded by the current user.
KeepChanges
Keeps the current values that have been changed, but updates the other values with the database values. SubmitChanges() method will save any changes made by the current user and will preserve any changes made by other users. If another user changed the same value as the current user, the current user's change will overwrite it.
OverwriteCurrentValues
Updates the DataContext with the current database values, which means that all changes made by the current user will be discarded.

Example

To handle the exception include the LINQ to Sql code in try/catch block. Modify the code in btnDeposit_Click () as shown below.

using (SampleDataContext db = new SampleDataContext())
{
try
{
Account account = db.Accounts.First(x => x.AccountNumber == 1);
account.AccountBalance = account.AccountBalance + 500;
db.SubmitChanges();
GetAccountData();
}
catch (ChangeConflictException)
{
db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);
foreach (ObjectChangeConflict objectChangeConflict
in db.ChangeConflicts)
{
foreach (MemberChangeConflict memberChangeConflict
in objectChangeConflict.MemberConflicts)
{
Response.Write("Current Value = " +
memberChangeConflict.CurrentValue.ToString() + "<br/>");
Response.Write("Original Value = " +
memberChangeConflict.OriginalValue.ToString() + "<br/>");
Response.Write("Database Value = " +
memberChangeConflict.DatabaseValue.ToString() + "<br/>");
}
}
db.SubmitChanges();
GetAccountData();
}

With the above changes

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 - 300
Where AccountNumber = 1

Step 4

Now, come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. Also, the balance gets overwritten with the current value.

Now, change the following line.

db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);

to the following -

db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);

Run the application again in Debug mode, and click "Deposit Rs 500" button. When the application execution stops at the break point, execute the following UPDATE statement from SQL Server Management Studio. The SQL statement has updated 2 columns (Account Name and Account Balance).

Update Accounts set AccountBalance = AccountBalance - 300,
AccountName = 'Milind Daware' Where AccountNumber = 1

Now, come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. The balance gets overwritten with the current value, but the AccountName is not. This is because RefreshMode.KeepChanges option will keep and update only the values that have been changed by the current user since the data is loaded into the DataContext. This means SubmitChanges() will save only changes made by the current user and will preserve any changes made by other users.

Now, change the following line -

db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);

to the following

db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

Run the application again in Debug mode, and click "Deposit Rs500" button. When the application excution stops at the break point, execute the following UPDATE statement from SQL Server Management Studio. The SQL statement has changed AccountName and AccountBalance.

Update Accounts set AccountBalance = AccountBalance - 300,
AccountName = 'Raghvan nadar' Where AccountNumber = 1

Now, come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. Notice that the changes made by the other user are preserved. So, RefreshMode.OverwriteCurrentValues will update the DataContext with the current database values, which means that all changes made by the current user will be discarded.

Part 4 UpdateCheck Property

UpdateCheck property of ColumnAttribute class is used to determine how LINQ to SQL detects concurrency conflicts.

To set this property

Open Sample.dbml file
Right click on AccountBalance property and select "Properties"
In the properties window set a value for the UpdateCheck property

This property can be set to one of the 3 values of the UpdateCheck enum. This enum is present in System.Data.Linq.Mapping namespace. The following are the different values of UpdateCheck enum and what they mean.

Example

Open Sample.dbml file and right click on "AccountBalance" property and select "Properties" option from the context menu.
In the properties window set, UpdateCheck = Never. At this point, open, Sample.Designer.cs file and notice that the AccountBalance property has UpdateCheck property applied.

[global::System.Data.Linq.Mapping.ColumnAttribute
(Storage="_AccountBalance", DbType="Int", UpdateCheck=UpdateCheck.Never)]
public System.Nullable<int> AccountBalance
{
get
{
return this._AccountBalance;
}
set
{
if ((this._AccountBalance != value))
{
this.OnAccountBalanceChanging(value);
this.SendPropertyChanging();
this._AccountBalance = value;
this.SendPropertyChanged("AccountBalance");
this.OnAccountBalanceChanged();
}
} }

Open SQL Profiler and run a new trace

Run the application and click "Deposit Rs500" button

Inspect the generated UPDATE SQL command.

exec sp_executesql N'UPDATE [dbo].[Accounts]
SET [AccountBalance] = @p2
WHERE ([AccountNumber] = @p0) AND ([AccountName] = @p1)',
N'@p0 int,@p1 nvarchar(4000),@p2 int',@p0=1,@p1=N'Raghvan nadar',@p2=2200

Notice, that AccountBalance is removed from the WHERE clause, which means this column is not used for detecting concurrency conflicts.

Part 5 Using ROWVERSION or TIMESTAMP to detect concurrency conflicts

How do we use ROWVERSION or TIMESTAMP columns to detect concurrency conflicts in LINQ to sql? Let us understand this with an example. We will be using the following Accounts table in this demo.

By default, LINQ to SQL uses all the columns of the table in the WHERE clause to detect concurrency conflicts. The query will look as shown below.

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'Akshay Phadke',@p2=1000,@p3=1500

This is OK if we have a few columns in the table. In real time applications, we may have tables with a large number of columns. For example, what if the table has 30 columns? The WHERE clause would be huge and it can impact the performance of the application.

In situations like this we can use ROWVERSION or TIMESTAMP columns. Here are the steps

Step 1

Add a Version column to the Accounts table. The datatype of the column must be either ROWVERSION or TIMESTAMP. The value for this column is automatically generated by the database if the row gets changed. So, this column can alone be used to detect concurrency conflicts.

ALTER TABLE Accounts
ADD [Version] ROWVERSION

Step 2

In Visual Studio, delete the Account Entity from the Sample.dbml file

Step 3

In Server Explorer window in Visual Studio, right click on Accounts table and select "Refresh".

Step 4

Drag and drop Accounts table on the Designer surface of Sample.dbml file. Notice that a Version Property is automatically added. Navigate to Sample.Designer.cs file and look at the code generated for this property. Notice that IsVersion & IsDbGenerated properties are set to true.

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Version",
AutoSync = AutoSync.Always, DbType = "rowversion NOT NULL", CanBeNull = false,
IsDbGenerated = true, IsVersion = true, UpdateCheck = UpdateCheck.Never)]
public System.Data.Linq.Binary Version
{
get
{
return this._Version;
}
set
{
if ((this._Version != value))
{
this.OnVersionChanging(value);
this.SendPropertyChanging();
this._Version = value;
this.SendPropertyChanged("Version");
this.OnVersionChanged();
}
}
}

Testing for concurrency conflicts

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 Rs500" button. The execution should stop on SubmitChanges() method.

Step 3

At this point open SQL Server Management Studio,

Execute the following SELECT query
1. Select * from Accounts where AccountNumber = 1
Notice the Value of Version column
Execute the following UPDATE query
1. Update Accounts set AccountBalance = AccountBalance - 300
2. Where AccountNumber = 1
Now, notice that the Value of Version column is automatically changed to a new value

Step 4

Open SQL Profiler and run a new trace.

Step 5

Now, come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown, as expected.

Step 6

In SQL Profiler, notice that the UPDATE query that is generated has used Version column in the WHERE clause to detect concurrency conflicts.