What is ADO.Net Locking

In a multiuser environment where multiple users simultaneously access and modify data in a database, it is crucial to implement controls that prevent one user's modifications from negatively impacting the modifications made by other users. This system of control is commonly referred to as Concurrency Handling or Locking. It allows for the detection and resolution of conflicts that arise when multiple requests occur concurrently on the same data source.

Concurrency handling can be approached through two main strategies:

  1. Optimistic concurrency
  2. Pessimistic concurrency

What is Optimistic concurrency ?

With optimistic concurrency, the system assumes that conflicts between concurrent operations are rare. Each user retrieves a copy of the data they intend to modify, and changes are made to the local copy. When the user attempts to save the modifications back to the database, the system compares the local copy to the current state of the data in the database. If no conflicts are detected, the changes are applied successfully. However, if conflicts arise, such as another user modifying the same data in the meantime, the system can prompt the user to resolve the conflict before proceeding.

What is Pessimistic concurrency ?

Pessimistic concurrency takes a more cautious approach, assuming that conflicts are likely to occur. This strategy involves acquiring locks on the data resources before allowing any modifications. When a user accesses a piece of data, a lock is placed on it, preventing other users from simultaneously modifying the same data. Once the user completes their modifications and releases the lock, other users can access the data. Pessimistic concurrency ensures that conflicts are avoided by ensuring exclusive access to the data during modification, but it can potentially lead to increased contention and reduced concurrency.


Both optimistic and pessimistic concurrency strategies have their own advantages and considerations. The choice of which approach to use depends on the specific requirements of the application and the nature of the data being accessed. Implementing effective concurrency handling techniques ensures that data integrity is maintained, conflicts are resolved efficiently, and the overall user experience in a multiuser environment remains optimal.