How can you handle concurrency in LINQ to SQL?

LINQ Interview Questions

Handling Concurrency in LINQ to SQL

Handling concurrency in LINQ to SQL involves ensuring that multiple users or processes can work with the same data without causing conflicts. This is typically done using techniques like optimistic concurrency control, where changes are tracked and conflicts are resolved during data updates. Let's illustrate this with a complete source code example.

Optimistic Concurrency in LINQ to SQL

Suppose you have a simple database table called Products with columns ProductID, ProductName, and UnitsInStock. You want to allow multiple users to update the stock of a product without conflicts.

Database Table

Assume you have a database table named Products with the following structure:


	CREATE TABLE Products
	(
		ProductID INT PRIMARY KEY,
		ProductName NVARCHAR(100),
		UnitsInStock INT
	)

LINQ to SQL DataContext

Create a LINQ to SQL DataContext to interact with the database. You should add this DataContext to your project using the LINQ to SQL designer in Visual Studio.

Code for Updating Stock

Now, let's write C# code to update the stock of a product using optimistic concurrency control. We'll use LINQ to SQL for this purpose:


using System;
using System.Linq;

class Program
{
    static void Main()
    {
        // Create a DataContext instance
        MyDataContext dataContext = new MyDataContext();

        // Retrieve a product
        var product = dataContext.Products.Single(p => p.ProductID == 1);

        // Simulate another user updating the same product in the database
        Console.WriteLine("Another user updated the product stock in the database.");
        Console.WriteLine("Press Enter to continue...");
        Console.ReadLine();

        // Modify the product's stock
        product.UnitsInStock = 20;

        try
        {
            // Submit changes to the database
            dataContext.SubmitChanges();
            Console.WriteLine("Stock updated successfully.");
        }
        catch (System.Data.Linq.ChangeConflictException)
        {
            // Handle concurrency conflict
            Console.WriteLine("Concurrency conflict detected. Another user modified the product.");
            Console.WriteLine("Press Enter to reload the product and try again...");
            Console.ReadLine();

            // Refresh the product from the database
            dataContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, product);

            // Update the stock and try again
            product.UnitsInStock = 20;
            dataContext.SubmitChanges();
            Console.WriteLine("Stock updated successfully after resolving the conflict.");
        }
    }
}

In this code:

We retrieve a product from the database.
We simulate another user updating the same product in the database.
When we try to update the stock, we catch a ChangeConflictException, indicating a concurrency conflict.
We resolve the conflict by refreshing the product from the database and updating the stock again.

Output:



Another user updated the product stock in the database.
Press Enter to continue...

Concurrency conflict detected. Another user modified the product.
Press Enter to reload the product and try again...

Stock updated successfully after resolving the conflict.