Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET

How To Handle Concurrency in LINQ to SQL?

4.60/5 (14 votes)
19 Jul 2009CPOL5 min read 93.4K   581  
How to handle concurrency in LINQ to SQL?

Table of Contents

Introduction and Goal

In this article, we will touch base on some important concepts of handling concurrency in LINQ to SQL. We will first see how LINQ supports optimistic concurrency and then we will see what support LINQ provides to handle concurrency violation. We will then touch base on some fine tuning features provided by LINQ at field level. Finally we will end this article by discussing two important error reporting options when concurrency conflict happens.

Please feel free to download my free .NET Ebook which has 400 questions and answers in WCF,WPF,WWF,Silverlight and lot more. 

Still New to LINQ? - Some Real Quick Starters

Two Things a Good Concurrency System Should Support

A good concurrency system should at least provide two important features:

  • It should be able to detect if any concurrency violation has taken place.
  • Once concurrency violation has been detected, it should provide various ways of how to handle the concurrency violation.

Image 1

LINQ Supports Optimistic Concurrency by Default

LINQ entity forms the main heart of LINQ engine. All the data from database is fetched using datacontext and given to LINQ objects. Once the data is fetched in the LINQ objects, the LINQ objects get disconnected from the database. In other words, LINQ is a disconnected architecture. Due to disconnected architecture, the only type of locking we can support is optimistic locking.

Good news!!! LINQ supports optimistic concurrency by default.

Image 2

Let’s See If Really Optimistic Concurrency Works

Let’s do a small sample test to see whether LINQ really supports optimistic concurrency default. So we will do the following:

  • We will open a data context, fetch a record and modify the record. We will not be calling SubmitChanges, in other words we will not be making a final update to physical database.
  • We will then open a new DataContext connection, fetch the same record and update the record physically in the database. 
  • Then we will come back to the same old record to call the physical update using SubmitChanges. In this way, we will be able to simulate concurrency violation.

So below goes the code.

We first fetch the record and change the customer name but we have not called the physical database commit yet.

C#
DataContext objContext = new DataContext(strConnectionString);
clsCustomer objCustomer = objContext.GetTable<clsCustomer>().First<clsCustomer>();
objCustomer.CustomerName = "Iwanttochange";

We then call a different method which will change this record by which we can simulate concurrency simulation.

C#
SomeOneChangedData();

The SomeOneChangedData method is a simple code which fetches the same record and changes it.

C#
private void SomeOneChangedData()
{
DataContext objContext = new DataContext(strConnectionString);
clsCustomer objCustomer = objContext.GetTable<clsCustomer>().First<clsCustomer>();
// changing to some random customer name
objCustomer.CustomerName = new Random().NextDouble().ToString();
objContext.SubmitChanges();
}

We then come back to the old DataContext object and try to call the SubmitChanges method. This call will create concurrency violation as SomeOneChangedData method has already changed data.

C#
objContext.SubmitChanges();

If you are in debug mode, you should see ChangeConflictException exception thrown as shown in the below figure:

Image 3

If you want to really see the exception, you can run the code without try and catch exception block. You should see something as shown in the below figure.

Image 4

Three Ways of Handling Concurrency Violation in LINQ

When we started this article, we talked about the two important features which every good concurrency system should have. We have already seen the live demonstration of the first feature which showed how LINQ helps to detect concurrency conflicts. Now let’s see how LINQ satisfies the second feature for concurrency which is handling concurrency conflicts.

LINQ gives three ways by which we can handle concurrency conflicts. To handle concurrency conflicts, we need to wrap the LINQ to SQL code in a TRY block and catch the ChangeConflictException. We can then loop through the ChangeConflicts collection to specify how we want the conflict to be resolved.

C#
catch (ChangeConflictException ex)
{
foreach (ObjectChangeConflict objchangeconf in objContext.ChangeConflicts)
{
objchangeconf.Resolve(RefreshMode.OverwriteCurrentValues);
}
}

There are 3 ways provided by LINQ system to handle concurrency conflicts:

  • KeepCurrentValues: When this option is specified and concurrency conflicts happen, LINQ keeps calling the LINQ entity object values as it is and does not push the new values from the database into the LINQ object.
  • OverwriteCurrentValues: When this option is specified, the current LINQ object data is replaced with the database values.
  • KeepChanges: This is the most weird option, but can be helpful in some cases. When we talk about classes, it can have many properties. So properties which are changed are kept as they is but the properties which are not changed are fetched from the database and replaced.

We need to use the RefereshMode to specify which options we need as shown in the below code snippet.

Image 5

Fine Tuning Concurrency at Field Level

One of the best options provided by LINQ concurrency system is control of concurrency behavior at field level. There are three options we can specify using the UpdateCheck attribute:

  • Never: Do not use this field while checking concurrency conflicts.
  • Always: This option specifies that always use this field to check concurrency conflicts.
  • WhenChanged: Only when the member value has changed, use this field to detect concurrency conflicts.

Below is the code snippet which shows how we can use the UpdateCheck attribute to control property / field level concurrency options as specified above.

C#
[Column(DbType = "nvarchar(50)",UpdateCheck=UpdateCheck.Never)]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
}

Error Reporting Options During Concurrency Conflicts

LINQ concurrency system lets you specify how you want the conflicts to be reported. LINQ system has given two ways to report conflicts:

  • ContinueOnConflict: This option says to the LINQ engine to continue even if there are conflicts and finally return all conflicts at the end of the process.
  • FailOnFirstConflict: This option says stop as soon as the first conflict occurs and return all the conflicts at that moment. In other words, LINQ engine does not continue ahead with executing the code.
     

Image 6

Both these options can be provided as an input in SubmitChanges method using the ConflictMode enum. Below is the code snippet of how to specify conflict modes:

C#
objContext.SubmitChanges(ConflictMode.ContinueOnConflict);

History

  • 20th July, 2009: Initial post

For further reading do watch the below interview preparation videos and step by step video series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)