Concurrency control (optimistic, pessimistic)

This tutorial explores concurrency control in Entity Framework Core (EF Core), focusing on optimistic and pessimistic approaches. We'll delve into their implementation, use cases, and considerations when building multi-user applications.

Introduction to Concurrency Control

Concurrency control is crucial when multiple users or processes access and modify the same data simultaneously. Without proper handling, this can lead to data corruption and inconsistencies. EF Core provides mechanisms to manage concurrency and ensure data integrity.

Two primary strategies are optimistic concurrency and pessimistic concurrency.

Optimistic Concurrency

Optimistic concurrency assumes that conflicts are rare. Instead of locking data during editing, it checks for modifications before applying changes. This approach improves performance and scalability, but requires handling potential conflicts.

Implementing Optimistic Concurrency with Row Versioning

A common method for optimistic concurrency is using a row version or timestamp column. This column is automatically updated each time the row is modified. When updating a row, the application checks if the current row version matches the original version. If they don't match, it indicates that another user has modified the row in the meantime.

Defining the Row Version Property

In your EF Core entity, add a property with the [Timestamp] attribute. This attribute tells EF Core to use this property for concurrency conflict detection. The property is typically a byte[] (row version) or a DateTime.

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }
}

Updating Data with Concurrency Handling

When you call SaveChanges(), EF Core checks if the row version of the entity being updated matches the row version in the database. If they don't match, a DbUpdateConcurrencyException is thrown.

The DbUpdateConcurrencyException provides information about the conflict, allowing you to retrieve the current database values and resolve the conflict appropriately. Common strategies include:

• Displaying the database values to the user and allowing them to choose which values to keep.
• Automatically merging the changes.
• Canceling the update.

try
{
    _context.Products.Update(product);
    _context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = entry.GetDatabaseValues();

    if (databaseValues == null)
    {
        // The record was deleted.
        Console.WriteLine("Record was deleted by another user.");
    }
    else
    {
        var databaseValuesAsObject = databaseValues.ToObject();
        Console.WriteLine("Concurrency conflict detected:");
        // Handle the conflict (e.g., display the database values to the user).
    }
}

Pessimistic Concurrency

Pessimistic concurrency assumes that conflicts are likely. It locks the data being accessed to prevent other users from modifying it until the lock is released. This approach guarantees data consistency but can negatively impact performance and scalability due to locking.

Implementing Pessimistic Concurrency with SQL Server Hints

EF Core doesn't provide built-in support for pessimistic locking. You typically achieve pessimistic concurrency by using database-specific locking mechanisms. In SQL Server, this is often done using table hints like UPDLOCK and ROWLOCK.

The UPDLOCK hint prevents other transactions from acquiring update locks on the row. ROWLOCK specifies that row-level locks should be used. This approach requires careful transaction management to avoid deadlocks.

Important: Pessimistic locking can lead to performance bottlenecks if not implemented carefully. Keep transactions short and only lock the necessary rows.

using (var transaction = _context.Database.BeginTransaction())
{
    var product = _context.Products
        .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK, ROWLOCK) WHERE ProductId = {0}", productId)
        .FirstOrDefault();

    if (product != null)
    {
        product.Price = newPrice;
        _context.SaveChanges();
        transaction.Commit();
    }
    else
    {
        transaction.Rollback();
    }
}

When to Use Optimistic vs. Pessimistic Concurrency

• Optimistic Concurrency: Use when conflicts are infrequent, and performance is a primary concern. This is suitable for most web applications where users are typically working on different data or when conflicts are resolved through user interaction.
• Pessimistic Concurrency: Use when conflicts are highly likely, and data integrity is paramount, even at the cost of performance. Examples include financial transactions or inventory management systems where accuracy is crucial.

Pros and Cons of Optimistic Concurrency

Pros:

• Improved performance and scalability.
• Reduced locking overhead.

• Requires handling concurrency exceptions and resolving conflicts.
• More complex implementation compared to no concurrency control.

Pros and Cons of Pessimistic Concurrency

Pros:

• Guaranteed data consistency.
• Simpler handling of concurrency issues (no exceptions to catch).

• Reduced performance and scalability due to locking.
• Potential for deadlocks if not implemented correctly.

Best Practices

• Choose the right concurrency strategy: Consider the frequency of conflicts, performance requirements, and data integrity needs when selecting between optimistic and pessimistic concurrency.
• Handle concurrency exceptions gracefully: Provide informative error messages to the user and offer options for resolving conflicts.
• Keep transactions short: Minimize the duration of transactions to reduce the likelihood of conflicts and deadlocks.
• Use indexes: Properly indexed tables can improve the performance of both optimistic and pessimistic concurrency control.
• Consider using retry mechanisms: If concurrency conflicts are rare, consider implementing a retry mechanism to automatically attempt the operation again.

Real-Life Use Case Section

Scenario: E-commerce Product Inventory

Imagine an e-commerce website where multiple users can purchase the same product simultaneously. If the inventory count is low, concurrent purchases could lead to overselling.

Optimistic Concurrency: The website could use optimistic concurrency with a row version on the Products table. When a user adds an item to their cart, the application records the current row version of the product. Before completing the purchase, the application checks if the row version in the database still matches the recorded version. If they match, the purchase is allowed, and the inventory is decremented. If they don't match, it means another user has purchased the product in the meantime, and the user is informed that the product is no longer available.

Pessimistic Concurrency (Less Common): Alternatively, the website could use pessimistic concurrency by locking the product record when a user adds it to their cart. This would prevent other users from purchasing the same product until the first user completes the purchase or cancels the order. However, this approach could lead to a poor user experience if the purchase process takes a long time.

Interview Tip

When discussing concurrency control in interviews, demonstrate your understanding of both optimistic and pessimistic approaches, their trade-offs, and practical scenarios where each approach is appropriate. Be prepared to discuss implementation details, potential challenges, and best practices.

Alternatives

While row versioning and pessimistic locking are common, other approaches to concurrency management exist:

• Application-level locking: Implementing locking mechanisms within the application code itself. This is less common with EF Core and database systems offering their own tools.
• Idempotent operations: Designing operations to be safe to execute multiple times without causing unintended side effects.

Memory footprint

Optimistic Concurrency: The memory footprint is minimal. It only involves storing the row version value, which is typically a small byte array or DateTime value.

Pessimistic Concurrency: Pessimistic concurrency can have a greater impact on memory due to the database engine needing to maintain locks for longer periods, potentially increasing memory usage and resource contention on the database server.