DBConcurrency with EF Core

What is concurrency in the database?

Concurrency is the simultaneous processing of data by multiple users or processes. In a database environment, this can lead to conflicts if two transactions attempt to access the same data at the same time.

Why is concurrency control important?

Concurrency control is important to ensure that multiple processes or threads can run simultaneously in a system without causing unexpected problems. This is important for efficient resource utilisation, data consistency and avoiding race conditions and deadlocks. Effective concurrency control improves the overall performance of systems, especially in multi-core environments, and contributes to a stable and user-friendly experience.

Conflict detection in Entity Framework Core

Entity Framework Core offers mechanisms for recognising conflicts.

In Entity Framework Core, conflict detection is often activated by the “Timestamp” attribute or the “ConcurrencyCheck” attribute.

„Timestamp” attribute:

Purpose: The “Timestamp” attribute is used to detect conflicts with concurrent updates in Entity Framework Core.
How it works: When the Timestamp attribute is applied to a property, Entity Framework Core automatically creates a database column of type RowVersion. This column is automatically updated with every update. If two updates occur simultaneously, the system recognises a conflict based on this column.

public class Employee
{
  public int Id { get; set; }
  public string FirstName { get; set; } = string.Empty;
  public string LastName { get; set; } = string.Empty;

  public string Fullname => $"{FirstName} {LastName}";

  public string Department { get; set; } = string.Empty;
  public int EntryYear { get; set; }

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

„ConcurrencyCheck“ attribute:

• Purpose: The “ConcurrencyCheck” attribute is also used to detect conflicts during updates by ensuring that no changes were made to certain properties during the update.
• How it works: When the ConcurrencyCheck attribute is applied to a property, Entity Framework Core compares the value of that property during the update with the value stored in the database. If differences are detected, a conflict is recognised.

public class Employee
{
  public int Id { get; set; }
  public string FirstName { get; set; } = string.Empty;
  public string LastName { get; set; } = string.Empty;

  public string Fullname => $"{FirstName} {LastName}";

  public string Department { get; set; } = string.Empty;
  public int EntryYear { get; set; }

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

Both approaches serve to ensure that database updates can be carried out consistently and without conflict, even if several users access the database at the same time.

Strategies for conflict resolution

When resolving conflicts in an application, there are various strategies to ensure that simultaneous updates of data are consistent and without data inconsistencies. Here are some common strategies:

Last writer wins (lww):

• Description: the last update written to the database is considered the valid version.
• Implementation: If a conflict occurs, the update with the most recent timestamp or transaction ID is favoured.

Extended conflict resolution (Custom Conflict Resolution):

• Description: custom logic is implemented to resolve conflicts based on application logic or user preferences.
• Implementation: Developers determine how conflicts should be handled, possibly through user interaction or specific business rules.

Optimistic conflict resolution:

• Description: It is assumed that conflicts rarely occur, so that the data is updated without prior blocking. Conflicts are only checked when data is written to the database.
• Implementation: Use of mechanisms such as Timestamp or ConcurrencyCheck attributes. In the event of a conflict, an exception is raised and the application can react.

Pessimistic conflict resolution:

• Description: data is locked throughout the update process to ensure that no other updates can be performed at the same time.
• Implementation: Use of explicit locks or transaction isolation levels to ensure that no other operations can access the locked data at the same time.

Automatic merging:

• Description: In the event of conflicts, changes are automatically merged if this is possible. This is often used in distributed version control systems or collaborative tools.
• Implementation: Automatic merging algorithms are used to recognise and automatically resolve conflicts.

The choice of the appropriate strategy depends on the requirements of the application, the type of data and the expected usage patterns. It is important to choose a strategy that ensures data consistency and at the same time does not impair the performance and user-friendliness of the application.

Implementation of concurrency control in a sample application

Example:

Assume you have an “Employee” entity class with a “LastName” property and a timestamp attribute for concurrency control:

public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;

public string Fullname => $"{FirstName} {LastName}";

public string Department { get; set; } = string.Empty;
public int EntryYear { get; set; }
[Timestamp]
public byte[] Timestamp { get; set; } // Timestamp-Eigenschaft für Concurrency
}

And here’s how you can use the ConcurrencyCheck attribute for concurrency control in an entity class:

public class Employee
{
  public int Id { get; set; }
  public string FirstName { get; set; } = string.Empty;
  public string LastName { get; set; } = string.Empty;

  public string Fullname => $"{FirstName} {LastName}";

  public string Department { get; set; } = string.Empty;
  public int EntryYear { get; set; }

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

In your DbContext, you can then specify that the timestamp row acts as RowVersion:

In our example, we select the “ConcurrencyCheck”

public void Configure(EntityTypeBuilder<Employee> builder)
 {
   builder.HasKey(c => c.Id);
   builder.Property(c => c.Id).ValueGeneratedOnAdd();
   builder.Property(rv => rv.RowVersion).IsRowVersion();
 }

Conflict simulation:

To simulate a concurrency conflict, you can perform the following steps:

Assume you already have an employee in your database:

var loadedEmployee = dbContext1.Employees.Find(id);

Now we simulate a conflict by updating the lastname both in the database and in the application:

// Changes in the database by another process

var existingEmployeeInDatabase = dbContext2.Employees.Find(id);


if (loadedEmployee != null && existingEmployeeInDatabase != null)
 {
   // Change only loadedEmployee
   loadedEmployee.LastName = "NewLastName1";

   try
   {
     dbContext1.SaveChanges();  // Save changes in LoadedEmployee
   }
   catch (DbUpdateConcurrencyException ex)
   {
     Console.WriteLine($"Concurrency conflict on LoadedEmployee: {ex.Message}");
     // Here you can implement conflict handling, e.g. retry or report errors to the user interface.
   }

   // Change existingEmployeeInDatabase now
   existingEmployeeInDatabase.LastName = "NewLastName2";

   try
   {
     dbContext2.SaveChanges();  // Try to save changes in existingEmployeeInDatabase, which should throw a DbUpdateConcurrencyException
     Console.WriteLine("Records updated successfully.");
   }
   catch (DbUpdateConcurrencyException ex)
   {
     Console.WriteLine($"Concurrency conflict on existingEmployeeInDatabase: {ex.Message}");
     // Here you can implement conflict handling, e.g. retry or report errors to the user interface.
   }
 }

In this example, dbContext2.SaveChanges() will throw a DbUpdateConcurrencyException because the database version of the product no longer matches the one in your application.

You can now implement the conflict resolution logic to decide how to deal with this conflict. This can include applying strategies such as “client wins”, “database wins” or customised conflict resolution.

Here is an example of the client wins strategy:

using (var dbContext1 = Create.Context())
 using (var dbContext2 = Create.Context())
 {
   var loadedEmployee = dbContext1.Employees.Find(id);
   var existingEmployeeInDatabase = dbContext2.Employees.Find(id);

   if (loadedEmployee != null && existingEmployeeInDatabase != null)
   {
     // Change only loadedEmployee
     loadedEmployee.LastName = "NewLastName1";

     try
     {
       dbContext1.SaveChanges();  // Save changes in LoadedEmployee
     }
     catch (DbUpdateConcurrencyException ex)
     {
       Console.WriteLine($"Concurrency conflict on LoadedEmployee: {ex.Message}");

       ex.Entries.Single().Reload();
       dbContext1.SaveChanges(); 
       Console.WriteLine("Client Wins: Records updated successfully.");
     }

     // Change existingEmployeeInDatabase now
     existingEmployeeInDatabase.LastName = "NewLastName2";

     try
     {
       dbContext2.SaveChanges();  // Try to save changes in existingEmployeeInDatabase, which should throw a DbUpdateConcurrencyException
       Console.WriteLine("Records updated successfully.");
     }
     catch (DbUpdateConcurrencyException ex)
     {
       Console.WriteLine($"Concurrency conflict on existingEmployeeInDatabase: {ex.Message}");
       // Here you can implement conflict handling, e.g. retry or report errors to the user interface.
     }
   }
 }

Concurrency proves to be essential by enabling simultaneous access to database resources. Precise handling of DBConcurrency minimises potential conflicts, ensures data consistency and guarantees integrity in dynamic environments. Clever implementation of mechanisms such as transaction control and locking procedures not only optimises synchronisation, but also increases performance. In complex applications that access shared databases, the accurate handling of concurrency is crucial for a robust system architecture and smooth functioning under intensive load.
GitHub: DBConcurrency

Comments

[elanatframework]

Do you think it is wrong to use SqlDataReader despite the advantages of Entity Framework?

[Ben Witt]

It is not wrong to use SqlDataReader, as it offers advantages in certain situations, such as for very performant and specific queries. However, Entity Framework comes with many convenience and security features that make it the better choice in most cases. The decision depends on the requirements and complexity of the project.