ADO - Optimistic vs. Pessimistic Concurrency Control in ADO.NET

Concurrency control is a technique used to manage situations where multiple users or applications try to access and modify the same data in a database simultaneously. Without proper concurrency control, data inconsistencies, conflicts, and loss of information can occur. ADO.NET provides mechanisms to handle these situations effectively by using two primary concurrency models: Optimistic Concurrency and Pessimistic Concurrency.

Understanding these models is essential for developing reliable multi-user applications such as banking systems, inventory management software, online shopping platforms, and hospital management systems.

What is Concurrency?

Concurrency refers to the ability of multiple users or processes to access and modify the same database at the same time. In modern applications, hundreds or even thousands of users may be connected to the same database simultaneously.

For example, consider an employee database where two administrators are editing the same employee's salary. If both save their changes without any concurrency control, one user's changes may overwrite the other's, resulting in inaccurate data.

Concurrency control prevents such conflicts and ensures data integrity.

Why Concurrency Control is Necessary

Concurrency control helps to:

  • Maintain data consistency.

  • Prevent accidental overwriting of data.

  • Ensure accurate transaction processing.

  • Support multiple users without conflicts.

  • Improve application reliability.

  • Preserve database integrity.

Without concurrency control, databases may suffer from:

  • Lost updates

  • Dirty reads

  • Non-repeatable reads

  • Phantom reads

  • Inconsistent data

Optimistic Concurrency

Optimistic concurrency assumes that conflicts between users are relatively rare. Instead of locking records while users are editing them, the application allows everyone to work freely. Before saving the updated data, it checks whether another user has already modified the same record.

If the data has not changed, the update proceeds normally.

If another user has modified the record, the application detects the conflict and prevents the update until the issue is resolved.

This approach improves performance because records remain unlocked during editing.

How Optimistic Concurrency Works

The process generally follows these steps:

  1. User A retrieves a record.

  2. User B retrieves the same record.

  3. User A modifies and saves the record.

  4. User B also modifies the record.

  5. Before saving User B's changes, ADO.NET compares the original values with the current values stored in the database.

  6. Since the data has changed, ADO.NET detects a concurrency conflict.

  7. User B is informed that the record has been modified by another user.

Example Scenario

Suppose a product table contains:

ProductID ProductName Price
101 Laptop 50000

User A changes the price to ₹52,000 and saves it.

Meanwhile, User B changes the price to ₹51,500.

When User B attempts to save, the application compares the original value (₹50,000) with the current database value (₹52,000).

Since the values differ, ADO.NET identifies a concurrency conflict and rejects the update until User B refreshes the data.

Implementing Optimistic Concurrency in ADO.NET

Optimistic concurrency is commonly implemented by:

Using Original Values

The UPDATE statement compares original column values before updating.

Example:

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 10
AND Salary = 55000;

If the salary has already changed, no rows are updated.

Using Timestamp or RowVersion

SQL Server provides a special column called RowVersion (formerly Timestamp).

Each time a row changes, SQL Server automatically generates a new RowVersion value.

Example:

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 10
AND RowVersion = @OldRowVersion;

If the RowVersion no longer matches, the update fails because another user has modified the record.

Advantages of Optimistic Concurrency

  • Excellent performance for applications with many users.

  • No long-term database locks.

  • Better scalability.

  • Suitable for web applications.

  • Reduces waiting time.

  • Lower resource consumption.

Disadvantages of Optimistic Concurrency

  • Update conflicts may occur.

  • Additional comparison logic is required.

  • Users may need to reload records before saving.

  • Conflict resolution can become complex.

Pessimistic Concurrency

Pessimistic concurrency assumes that conflicts are likely to occur. Therefore, whenever a user begins editing a record, the database immediately locks that record.

No other user can modify the locked record until the first user completes the transaction.

This approach guarantees that only one user modifies a record at a time.

How Pessimistic Concurrency Works

The process is:

  1. User A opens a record.

  2. The database locks the record.

  3. User B attempts to edit the same record.

  4. User B must wait until User A finishes.

  5. User A saves the changes.

  6. The database releases the lock.

  7. User B can now edit the record.

Example Scenario

Consider an online banking application.

Account Balance:

₹10,000

User A initiates a withdrawal of ₹2,000.

The account record is locked.

While User A's transaction is processing, User B attempts to transfer ₹5,000 from the same account.

Since the account is locked, User B must wait.

Once User A's transaction completes, the lock is released, and User B's transaction proceeds.

This prevents incorrect account balances.

Implementing Pessimistic Concurrency

SQL Server supports explicit locking.

Example:

BEGIN TRANSACTION

SELECT *
FROM Employees WITH (UPDLOCK)
WHERE EmployeeID = 10;

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 10;

COMMIT TRANSACTION;

The UPDLOCK hint prevents other transactions from modifying the selected row until the transaction completes.

Advantages of Pessimistic Concurrency

  • Prevents update conflicts completely.

  • Ensures maximum data consistency.

  • Suitable for highly critical transactions.

  • No possibility of lost updates.

  • Easier conflict management because updates occur sequentially.

Disadvantages of Pessimistic Concurrency

  • Records remain locked during editing.

  • Other users may experience delays.

  • Increased waiting time under heavy load.

  • Lower application scalability.

  • Higher risk of deadlocks if locks are not managed properly.

Comparison Between Optimistic and Pessimistic Concurrency

Feature Optimistic Concurrency Pessimistic Concurrency
Assumption Conflicts are rare Conflicts are common
Record Locking No Yes
Performance Faster Slower
Scalability High Lower
User Waiting Minimal Higher
Conflict Detection During update Prevented by locking
Resource Usage Low Higher
Suitable For Web applications, reporting systems Banking, reservation systems, financial software

Concurrency in ADO.NET DataSet

A DataSet is disconnected from the database. While users edit data locally, other users may also modify the same records in the database.

When the DataAdapter.Update() method is called:

  1. ADO.NET compares the original data stored in the DataSet with the current database values.

  2. If no differences exist, the update succeeds.

  3. If differences are detected, a concurrency violation occurs.

  4. The application can then notify the user and request a refresh before retrying the update.

This behavior naturally aligns with the optimistic concurrency model.

Best Practices

  • Use optimistic concurrency for most business and web applications where conflicts are infrequent.

  • Use RowVersion columns whenever possible to simplify conflict detection.

  • Reserve pessimistic concurrency for scenarios where data accuracy is critical, such as banking, airline reservations, stock trading, or payment processing.

  • Keep transactions as short as possible to reduce lock duration and improve system performance.

  • Handle concurrency exceptions gracefully by informing users, refreshing data when necessary, and allowing them to retry updates.

  • Regularly monitor locking and deadlock issues in high-traffic environments to maintain application responsiveness.

Conclusion

Optimistic and pessimistic concurrency are two fundamental approaches to managing simultaneous database access in ADO.NET. Optimistic concurrency emphasizes performance and scalability by allowing concurrent access and checking for conflicts only when data is saved. Pessimistic concurrency prioritizes data integrity by locking records during updates, ensuring that only one user can modify a record at a time. Choosing the appropriate model depends on the application's requirements, the likelihood of concurrent updates, and the balance between performance and data consistency. Proper implementation of concurrency control is essential for building secure, efficient, and reliable multi-user database applications.