Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ to SQL: Concurrency resolution

Tags:

sql

linq

Given this LINQ to SQL:

using (var db = Database.Context)
{
    var root = (from post in db.Post
                where post.Id == rootPostId
                select post).Single();

    root.LastActivityUtc = DateTime.UtcNow;

    db.SubmitChanges();
}

What will happen if the same record is concurrently being changed by another call to the same method (where this code lives) with the same rootPostId? Will an exception be thrown?

In such an event--concurrency conflict--I'd like to handle it by simple discarding the change so that just one update to LastActivityUtc is submitted instead of both, which will probably have the same value anyway.

like image 716
core Avatar asked Jul 23 '09 20:07

core


People also ask

How can we handle concurrency in LINQ?

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.

What is the default concurrency type provided by LINQ to SQL?

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.

How does the LINQ framework resolve conflicts by default?

By default, LINQ DataContext supports optimistic concurrency. When you create entity class in LINQ to SQL, you can attribute a column with UpdateCheck as shown above. Any column attributed with UpdateCheck would be checked for optimistic concurrency. UpdateCheck is an enum with three values.


1 Answers

You can detect and resolve your concurrency issues, by catching a ChangeConflictException:

using (var db = new MyDataContext())
{
    var root = (from post in db.Post
                where post.Id == rootPostId
                select post).Single();

    root.LastActivityUtc = DateTime.UtcNow;

    try
    {
        db.SubmitChanges();
    }
    catch (ChangeConflictException)
    {
        db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
        db.SubmitChanges();
    }
}

With RefreshMode.KeepChanges you will keep all changes of your client objects, and the changes from other users on other fields will be merged.

Recommended articles:

  • Optimistic Concurrency Overview
  • LINQ To SQL Samples - Optimistic Concurrency
  • Resolve Concurrency Conflicts by Merging with Database Values (LINQ to SQL)
like image 165
Christian C. Salvadó Avatar answered Sep 30 '22 12:09

Christian C. Salvadó