ADO - Optimistic vs Pessimistic Concurrency Handling in ADO.NET

Concurrency handling refers to the process of managing simultaneous access to the same data in a database by multiple users or applications. In multi-user database systems, it is common for several users to read or modify the same record at the same time. Without proper concurrency control, data inconsistency, overwriting of information, and transaction conflicts can occur.

ADO.NET provides mechanisms to manage these situations using two major concurrency models:

  1. Optimistic Concurrency

  2. Pessimistic Concurrency

Both approaches aim to maintain data integrity, but they work differently and are suitable for different application scenarios.

What is Concurrency?

Concurrency occurs when two or more users attempt to access or update the same database record simultaneously.

Example:

Suppose an employee table contains a salary record:

EmployeeID Name Salary
101 Rahul 50000

Scenario:

  • User A opens the record and changes salary to 55000.

  • User B opens the same record and changes salary to 60000.

  • Both users save their changes.

The system must decide how to handle these conflicting updates.

This is where concurrency control becomes important.

Optimistic Concurrency

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

Instead of locking the data, the system checks whether another user has modified the record before saving changes.

If the data has changed since it was originally read, the update fails and the user is informed about the conflict.

Working of Optimistic Concurrency

The process generally works as follows:

  1. User retrieves data from the database.

  2. User modifies the data locally.

  3. Before updating:

    • ADO.NET compares the original values with the current database values.

  4. If values match:

    • Update succeeds.

  5. If values differ:

    • Concurrency conflict occurs.

Example of Optimistic Concurrency

Suppose a product table contains:

ProductID ProductName Price
1 Laptop 45000

Two users retrieve the same record.

  • User A changes price to 47000.

  • User B changes price to 48000.

User A saves first.

Database becomes:

ProductID ProductName Price
1 Laptop 47000

Now User B tries to save.

ADO.NET checks the original value:

  • Original Price = 45000

  • Current Database Price = 47000

Since the record has changed, the update is rejected.

SQL Statement Used in Optimistic Concurrency

Typically, the WHERE clause contains original values.

Example:

UPDATE Products
SET Price = 48000
WHERE ProductID = 1 AND Price = 45000

If no rows are affected, it means another user already modified the record.

Advantages of Optimistic Concurrency

Better Performance

Records are not locked for long durations.

Higher Scalability

Multiple users can access records simultaneously.

Reduced Deadlocks

Since locks are minimal, deadlock chances decrease.

Suitable for Web Applications

Web applications usually use disconnected architecture where users remain inactive for long periods.

Disadvantages of Optimistic Concurrency

Update Conflicts

Conflicts may occur during saving.

Retry Mechanism Required

Applications must handle failed updates properly.

More Validation Logic

Developers need additional checking mechanisms.

What is Pessimistic Concurrency?

Pessimistic concurrency assumes that conflicts are likely to happen.

Therefore, records are locked immediately when a user accesses them for editing.

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

Working of Pessimistic Concurrency

The process works as follows:

  1. User opens a record.

  2. Database places a lock on the record.

  3. Other users cannot update the record.

  4. First user saves changes.

  5. Lock is released.

Example of Pessimistic Concurrency

Suppose User A opens a bank account record for modification.

The system locks the row.

If User B tries to edit the same record:

  • Access is denied or delayed until User A finishes.

This prevents conflicts completely.

Types of Locks Used

Shared Lock

Allows multiple users to read data but prevents modifications.

Exclusive Lock

Only one user can read and modify the record.

Update Lock

Prevents deadlock situations during updates.

Advantages of Pessimistic Concurrency

Strong Data Consistency

Conflicts are prevented before they happen.

Safer for Critical Systems

Useful in banking, financial, and inventory systems.

No Update Collisions

Only one user can edit at a time.

Disadvantages of Pessimistic Concurrency

Reduced Performance

Locks consume resources and slow down the system.

Lower Scalability

Many users waiting for locks can reduce efficiency.

Deadlocks Can Occur

Two transactions may wait indefinitely for each other.

Comparison Between Optimistic and Pessimistic Concurrency

Feature Optimistic Concurrency Pessimistic Concurrency
Locking No immediate lock Immediate lock
Conflict Handling Detects conflicts later Prevents conflicts early
Performance Faster Slower
Scalability High Lower
Deadlock Risk Low Higher
Best For Web applications Banking systems
Resource Usage Low High
User Waiting Time Minimal More

Concurrency in ADO.NET DataSet

ADO.NET commonly uses optimistic concurrency because:

  • DataSet works in disconnected mode.

  • Users can remain disconnected for long durations.

  • Maintaining database locks during disconnection is impractical.

When DataAdapter.Update() is called:

  • ADO.NET compares original and current values.

  • Conflicts are detected automatically.

Example Using SqlDataAdapter

SqlDataAdapter da = new SqlDataAdapter();
da.UpdateCommand = new SqlCommand(
    "UPDATE Employees SET Salary=@Salary WHERE EmployeeID=@ID AND Salary=@OldSalary", conn);

da.UpdateCommand.Parameters.AddWithValue("@Salary", 60000);
da.UpdateCommand.Parameters.AddWithValue("@ID", 101);
da.UpdateCommand.Parameters.AddWithValue("@OldSalary", 50000);

Here:

  • @OldSalary represents the original value.

  • If salary changed in database, update fails.

Handling Concurrency Violations

ADO.NET throws a DBConcurrencyException when concurrency conflicts occur.

Example:

try
{
    da.Update(ds, "Employees");
}
catch(DBConcurrencyException ex)
{
    Console.WriteLine("Concurrency violation occurred.");
}

This exception helps developers notify users and reload fresh data.

Techniques for Optimistic Concurrency

Using Timestamp Columns

A timestamp or rowversion column changes automatically whenever a row is updated.

Example:

rowversion

ADO.NET checks whether timestamp values match before updating.

Comparing All Columns

The system compares every original column value.

Example:

WHERE Name='Rahul' AND Salary=50000

Comparing Selected Columns

Only important columns are checked.

This improves performance.

Real-World Applications

Optimistic Concurrency

Used in:

  • E-commerce websites

  • Social media applications

  • Online booking systems

  • Content management systems

These applications have many users but relatively fewer conflicts.

Pessimistic Concurrency

Used in:

  • Banking software

  • Stock trading systems

  • Airline reservation systems

  • Medical record systems

These systems require strict consistency.

Best Practices

Use Optimistic Concurrency When

  • Application has many users.

  • Data conflicts are rare.

  • Performance is important.

  • Web-based disconnected architecture is used.

Use Pessimistic Concurrency When

  • Data accuracy is critical.

  • Conflicts occur frequently.

  • Transactions involve money or inventory.

  • Data corruption cannot be tolerated.

Conclusion

Concurrency handling is an essential concept in database applications. ADO.NET primarily supports optimistic concurrency because of its disconnected architecture and better scalability. In optimistic concurrency, conflicts are detected during updates, whereas pessimistic concurrency prevents conflicts through locking mechanisms.

Choosing the correct concurrency model depends on the nature of the application, number of users, transaction sensitivity, and performance requirements. Optimistic concurrency is suitable for scalable applications with fewer conflicts, while pessimistic concurrency is ideal for mission-critical systems where data consistency is the highest priority.