ADO - Optimistic and Pessimistic Concurrency Control in ADO.NET

Concurrency control is a technique used in database applications to ensure that multiple users can access and modify data without causing inconsistencies or data corruption. In multi-user environments, it is common for several users to access the same database records simultaneously. Without proper concurrency control, updates made by one user may overwrite the changes made by another, resulting in incorrect or lost information.

ADO.NET provides mechanisms to handle concurrent access to data effectively. The two primary approaches are Optimistic Concurrency Control and Pessimistic Concurrency Control. Each method has its own advantages, disadvantages, and suitable use cases.

What is Concurrency?

Concurrency refers to the situation where multiple users or applications access the same database data at the same time. Consider an online shopping application where two administrators attempt to update the price of the same product simultaneously. If there is no concurrency control, one administrator's changes may overwrite the other's without warning.

Proper concurrency management ensures that:

  • Data remains accurate.

  • Simultaneous updates do not create conflicts.

  • Users receive the latest information.

  • Database integrity is maintained.

Why is Concurrency Control Important?

Without concurrency control, database applications may experience several problems.

Lost Updates

A user modifies a record, but before saving it, another user changes the same record and saves it. When the first user saves later, the second user's changes are lost.

Dirty Reads

A transaction reads data that has been modified but not yet committed by another transaction. If that transaction is rolled back, the first transaction has read invalid data.

Non-Repeatable Reads

A record is read twice during the same transaction, but another transaction modifies it between the two reads, resulting in different values.

Phantom Reads

A query returns different sets of rows because another transaction inserts or deletes records while the query is executing.

Concurrency control minimizes these issues by coordinating database access.

Optimistic Concurrency Control

Optimistic concurrency assumes that conflicts between users are rare. Therefore, records are not locked while users are editing them.

Instead, ADO.NET checks whether another user has modified the record before saving the changes.

If a conflict is detected, the update fails, and the application informs the user.

How Optimistic Concurrency Works

  1. User A retrieves a record.

  2. User B retrieves the same record.

  3. User A modifies and saves the record.

  4. User B modifies the same record.

  5. Before saving, ADO.NET compares the original values with the current database values.

  6. Since the record has already changed, User B's update is rejected.

  7. User B must reload the latest data before making changes again.

Example

Assume a table named Employees.

EmployeeID Name Salary
101 Ravi 50000

User A opens the record.

Salary = 50000

User B also opens the same record.

Salary = 50000

User A updates the salary.

Salary = 55000

The database now contains:

EmployeeID Name Salary
101 Ravi 55000

User B tries to update the salary to 53000.

ADO.NET checks the original value.

Original Salary = 50000

Database Salary = 55000

Since they are different, the update is rejected.

The application can display a message such as:

"The record has been modified by another user. Please refresh the data."

Implementing Optimistic Concurrency

The UPDATE statement includes the original values in its WHERE clause.

UPDATE Employees
SET Salary = @NewSalary
WHERE EmployeeID = @EmployeeID
AND Salary = @OriginalSalary

If another user has already modified the salary, the WHERE condition fails.

No rows are updated.

The application detects this by checking the number of affected rows.

int rowsAffected = command.ExecuteNonQuery();

if(rowsAffected == 0)
{
    Console.WriteLine("Concurrency conflict detected.");
}

Advantages of Optimistic Concurrency

Better Performance

No database locks are held while users edit data.

Higher Scalability

Many users can access the same records simultaneously.

Less Resource Consumption

Database resources are not occupied by long-running locks.

Suitable for Web Applications

Users may keep pages open for several minutes before submitting data.

Keeping records locked during that time would reduce performance.

Disadvantages of Optimistic Concurrency

Update Failures

Users may have to re-enter changes if another user updates the record first.

Conflict Resolution Required

Applications must provide a mechanism for resolving conflicts.

Not Suitable for Highly Competitive Data

Frequent updates increase the chances of conflicts.

Pessimistic Concurrency Control

Pessimistic concurrency assumes that conflicts are likely to occur.

Therefore, when one user begins editing a record, the database immediately locks it.

Other users cannot modify the record until the first user completes the transaction.

How Pessimistic Concurrency Works

  1. User A retrieves a record.

  2. 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 changes.

  6. Lock is released.

  7. User B can now edit the record.

Example

Employee record:

EmployeeID Name Salary
101 Ravi 50000

User A starts editing.

The database locks the record.

User B attempts to edit.

Database returns:

"Record is currently locked."

User A finishes editing.

Salary becomes 55000.

The lock is removed.

User B can now edit the updated record.

Using Transactions for Pessimistic Concurrency

Locks are generally maintained within transactions.

SqlTransaction transaction = connection.BeginTransaction();

SqlCommand command = new SqlCommand(sql, connection, transaction);

command.ExecuteNonQuery();

transaction.Commit();

If an error occurs,

transaction.Rollback();

This ensures data consistency.

Advantages of Pessimistic Concurrency

Prevents Conflicts

Only one user can modify a record at a time.

High Data Integrity

No accidental overwriting of data occurs.

Suitable for Critical Systems

Ideal for banking, inventory management, reservation systems, and financial applications.

Disadvantages of Pessimistic Concurrency

Reduced Performance

Locks prevent other users from accessing the same records.

Longer Waiting Time

Users may experience delays while waiting for locks to be released.

Deadlocks

Two transactions may wait indefinitely for each other to release resources.

Example:

Transaction A locks Table X.

Transaction B locks Table Y.

Transaction A waits for Table Y.

Transaction B waits for Table X.

Neither transaction can proceed.

Comparing Optimistic and Pessimistic Concurrency

Feature Optimistic Concurrency Pessimistic Concurrency
Record Locking No Yes
Performance High Lower
Conflict Detection During update Before update
Database Locks Rare Frequent
Scalability Excellent Limited
Suitable for Web Applications Yes Usually No
Suitable for Banking Systems Less Suitable Highly Suitable
User Waiting Time Minimal Higher
Resource Usage Low High
Conflict Resolution Required Rarely Needed

When to Use Optimistic Concurrency

Optimistic concurrency is recommended when:

  • Most users only read data.

  • Record updates are infrequent.

  • High application performance is important.

  • Web applications have many concurrent users.

  • Users may keep records open for extended periods before saving.

Examples include:

  • Student information systems

  • Online learning platforms

  • Employee management systems

  • Customer relationship management applications

  • E-commerce product catalogs

When to Use Pessimistic Concurrency

Pessimistic concurrency is appropriate when:

  • Data is frequently modified.

  • Data accuracy is critical.

  • Simultaneous updates must be prevented.

  • Financial transactions are involved.

  • Data conflicts could have serious consequences.

Examples include:

  • Banking applications

  • Airline reservation systems

  • Railway ticket booking systems

  • Hospital patient record systems

  • Stock trading platforms

Best Practices

  • Use optimistic concurrency as the default strategy for most ADO.NET applications because it provides better scalability and performance.

  • Use pessimistic concurrency only when the risk and cost of conflicting updates are high.

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

  • Check the number of rows affected after executing an UPDATE or DELETE statement to detect optimistic concurrency conflicts.

  • Handle concurrency exceptions gracefully by informing users of conflicts and allowing them to refresh and retry.

  • Design SQL queries and indexes efficiently so that updates complete quickly, reducing the likelihood of contention.

  • Regularly test applications under multi-user conditions to identify concurrency issues before deployment.

Summary

Concurrency control is essential for maintaining data consistency in applications where multiple users access the same database. ADO.NET supports two primary approaches: optimistic concurrency, which allows simultaneous access and checks for conflicts during updates, and pessimistic concurrency, which prevents conflicts by locking records during editing. Optimistic concurrency offers better performance and scalability and is widely used in web and enterprise applications, while pessimistic concurrency provides stronger protection against conflicting updates and is preferred for systems where data accuracy is critical, such as banking, reservation, and financial applications. Selecting the appropriate strategy depends on the application's performance requirements, expected user activity, and the importance of maintaining data integrity.