Optimistic and pessimistic concurrency in EF-Core

In EF Core and database terminology, optimistic and pessimistic concurrency are different approaches to managing the possibility of multiple users accessing and modifying the same data simultaneously.

Optimistic concurrency assumes that conflicts between simultaneous updates are rare, and thus allows multiple users to work with the data simultaneously, and only checks for conflicts at the time of data updates. In this approach, a version number or timestamp is associated with each record, and when a user wants to update the record, the version number is checked to ensure that it has not been modified since it was last read. If the version number is the same, the update is allowed, otherwise, an exception is thrown and the user is asked to try again.

Pessimistic concurrency, on the other hand, assumes that conflicts between simultaneous updates are likely, and thus restricts access to the data to one user at a time. In this approach, a lock is placed on the data when it is read, and other users are prevented from accessing or modifying the data until the lock is released, EF Core, optimistic concurrency is the default approach, but it also supports pessimistic concurrency through the use of transactions and locks. The choice between optimistic and pessimistic concurrency depends on the specific needs of the application and the characteristics of the data being accessed.

Optimistic concurrency

Here are the steps to handle optimistic concurrency in EF Core:

Add a concurrency token property to the entity model that represents the data you want to update. You can do this by adding the [ConcurrencyCheck] attribute to a property that represents the column in the database that you want to use for optimistic concurrency.

public class Product

{

public int Id { get; set; }

public string Name { get; set; }

public decimal Price { get; set; }

[ConcurrencyCheck]

public byte[] RowVersion { get; set; }

}

When updating an entity, retrieve the entity from the database, update its properties, and then call the SaveChanges() method on the DbContext instance

For example:

using (var context = new MyDbContext())

{

?? var product = context.Products.Find(id);

?? product.Name = "New Product Name";

?? product.Price = 19.99m;

?? context.SaveChanges();

}

If another user has updated the same entity since it was retrieved by the current user, an exception of type DbUpdateConcurrencyException will be thrown when calling SaveChanges(). To handle this exception, catch it and then reload the entity from the database, apply the changes again, and then call SaveChanges() again.

using (var context = new MyDbContext())

{

var product = context.Products.Find(id);

product.Name = "New Product Name";

product.Price = 19.99m;

try

{

context.SaveChanges();

}

catch (DbUpdateConcurrencyException ex)

{

ex.Entries.Single().Reload();

var entry = ex.Entries.Single();

var clientValues = entry.Entity;

var databaseValues = entry.GetDatabaseValues();

entry.OriginalValues.SetValues(databaseValues);

entry.CurrentValues.SetValues(clientValues);

context.SaveChanges();

}

}

In this example, the DbUpdateConcurrencyException is caught, and the entity is reloaded from the database using the Reload() method on the DbUpdateConcurrencyException entry. The new changes are then reapplied to the entity, and SaveChanges() is called again. by using a concurrency token property and handling the DbUpdateConcurrencyException,

A timestamp (also known as rowversion) is another approach to handle optimistic concurrency in EF Core. A timestamp column in the database is automatically updated with a unique binary value each time a row is updated, and this value can be used to detect conflicts when multiple users try to update the same row concurrently.

Optimistic concurrency

In EF Core, you can use transactions and locks to implement pessimistic concurrency. begin a transaction: You can use the BeginTransaction() method of the Database object to begin a transaction.

using var transaction = dbContext.Database.BeginTransaction();

Acquire a lock on the data: You can use the SELECT ... FOR UPDATE syntax in SQL to acquire a lock on the data you want to modify. This syntax ensures that the selected rows are locked for the duration of the transaction, preventing other users from modifying them.

var entity = dbContext.Entities.Where(e => e.Id == 1).FirstOrDefault();

dbContext.Entry(entity).State = EntityState.Modified;

// Lock the selected row for the duration of the transaction

dbContext.Database.ExecuteSqlRaw("SELECT * FROM Entities WITH (UPDLOCK) WHERE Id = 1", 1); Modify the data: You can now modify the data as required, and EF Core will ensure that the changes are saved to the database within the transaction.

entity.SomeProperty = "new value";

dbContext.SaveChanges();

transaction.Commit();

Commit or rollback the transaction: After you have made the necessary changes, you can commit the transaction to save the changes to the database, or rollback the transaction if necessary.

Note that acquiring a lock on data can cause performance issues and contention in highly concurrent scenarios, so it's important to use pessimistic concurrency only when necessary, and to release the lock as soon as possible.

and you can change the isolation level of transactions in EF Core by using the

IsolationLevel enumeration. The isolation level determines how locks are acquired and released during a transaction, and how concurrent transactions access the same data.

The IsolationLevel enumeration includes the following isolation levels:

• ReadUncommitted: Allows transactions to read data that has been modified by other transactions but not yet committed. This can lead to dirty reads, non-repeatable reads, and phantom reads.
• ReadCommitted: Allows transactions to read only data that has been committed by other transactions. This prevents dirty reads but can still lead to non-repeatable reads and phantom reads.
• RepeatableRead: Allows transactions to read data that has been locked by other transactions but not yet modified. This prevents dirty reads and non-repeatable reads but can still lead to phantom reads.
• Serializable: Ensures that transactions are isolated from each other, so that no two transactions can access the same data at the same time. This prevents dirty reads, non-repeatable reads, and phantom reads, but can lead to contention and blocking.

using var transaction = dbContext.Database.BeginTransaction(

System.Data.IsolationLevel.Serializable);

try

{

?? // Perform database operations within the transaction

?? dbContext.SaveChanges();

?? // Commit the transaction

?? transaction.Commit();

}

catch (Exception ex)

{

?? // Roll back the transaction if an error occurs

? ?transaction.Rollback();

}

note that the specific behavior of each isolation level depends on the database management system you're using, so you should consult the documentation of your database for more information. Additionally, changing the isolation level can affect the performance and concurrency of your application, so you should carefully consider the trade-offs before making any changes.