How can we do pessimistic locking in Entity Framework?

In Entity Framework, you can implement pessimistic locking to prevent concurrent modifications to database records by multiple users or processes. Pessimistic locking ensures that when one user is working with a particular record, other users or processes are blocked from accessing or modifying the same record until the lock is released.

There are two common approaches to implement pessimistic locking in Entity Framework:

  1. Explicit Database Transaction with Isolation Level:
    • Use the TransactionScope class to create an explicit database transaction and specify the desired isolation level to achieve pessimistic locking.
    • The isolation level can be set to Serializable or RepeatableRead, which are the highest isolation levels and provide strong pessimistic locking.
    • While the transaction is active, any changes made to the locked record will be blocked until the transaction is completed or rolled back.
    Example:
using (var dbContext = new YourDbContext())
{
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
    {
        try
        {
            // Retrieve the entity to be locked
            var entity = dbContext.YourEntities.Find(id);

            // Lock the entity for update
            dbContext.Entry(entity).State = EntityState.Modified;

            // Perform operations and save changes (within the transaction)
            dbContext.SaveChanges();

            // Commit the transaction
            transactionScope.Complete();
        }
        catch (Exception ex)
        {
            // Handle exceptions or rollback the transaction if needed
            transactionScope.Dispose();
            // Handle the exception
        }
    }
}
  1. Using SQL Server UPDLOCK Hint:
  • You can use the UPDLOCK hint in a raw SQL query to explicitly request an update lock on a specific record during the query execution.
  • This approach is useful when you want to lock specific records and not entire transactions.

Example:

using (var dbContext = new YourDbContext())
{
    // Execute raw SQL query with UPDLOCK hint
    dbContext.Database.ExecuteSqlCommand("SELECT * FROM YourEntities WITH (UPDLOCK) WHERE Id = @id", new SqlParameter("id", entityId));

    // Retrieve the entity and make changes
    var entity = dbContext.YourEntities.Find(entityId);
    // Make changes to the entity

    // Save changes to release the lock
    dbContext.SaveChanges();
}

Please note that pessimistic locking should be used judiciously, as it can lead to contention and performance issues in highly concurrent systems. It’s essential to implement proper error handling and rollback mechanisms to avoid deadlocks and ensure data consistency. Consider the specific use case and concurrency requirements of your application before applying pessimistic locking in Entity Framework.

error: Content is protected !!