Handling Concurrent Database Access in .NET: Understanding Locks, Transactions, and Optimistic Concurrency

Introduction

In real-world applications, multiple users or services often try to read or update the same database row or table simultaneously.

• Who gets to update first?

• How does the system prevent conflicts?

• What happens if multiple updates occur at the same time?

This topic is frequently asked in .NET interviews and is crucial for building reliable and consistent applications. In this article, we explain concurrency concepts in plain terms and illustrate them with a ticket-booking scenario.

Understanding Concurrency in Databases

Concurrency happens when multiple processes access the same data simultaneously. Without proper handling, this can lead to race conditions and lost updates. Databases handle concurrency using transactions, locks, and versioning mechanisms.

Here is a simple breakdown of the two main approaches databases use to handle simultaneous updates.

1. Pessimistic Concurrency (The "Locking" Method)

This approach is protective. It assumes that if two people are looking at the same data, they will inevitably clash, so it takes precautions early.

• How it works: Imagine a single-person bathroom. When User A goes in, they lock the door.

• What others do: User B arrives, sees the lock, and has to wait in the hallway. They can't even look inside until User A is finished and unlocks the door.

• The Result: Only one person can touch the data at a time. It is impossible to have a conflict because everyone else is blocked.

• Best for: Very important data like bank balances or stock levels, where you can't afford a single mistake.

2. Optimistic Concurrency (The "Versioning" Method)

This approach is flexible. It assumes that most of the time, people won't try to change the exact same thing at the exact same second.

• How it works: Every row of data has a hidden Version Number (like a "Sticker").

  • User A reads the data (it's Version 1).

  • User B reads the same data (it's also Version 1).

• The Check: When User A saves, the system checks: "Is this still Version 1?" Yes. It saves the change and updates the sticker to Version 2.

• The Conflict: Now User B tries to save. The system checks: "Is this still Version 1?" No, it's now Version 2! The system rejects User B's change and says, "Sorry, someone else changed this while you were typing."

• Best for: Most websites (like Wikipedia or a profile page) where locking a page for 10 minutes while someone types would be annoying for everyone else.

Beginner-Friendly Ticket Booking Example

Imagine a movie theater with only one seat left: Seat 10.

• Alice clicks “Book” first.

• Bob clicks “Book” almost at the same time.

Without concurrency control:

• Both see the seat as available.

• Both complete booking → Seat 10 is double-booked.

With proper concurrency handling:

1. Pessimistic Concurrency (Locking):

   • Alice’s transaction locks Seat 10.

   • Bob’s transaction waits until the lock is released.

   • Alice books successfully → Bob sees seat is taken.

2. Optimistic Concurrency (Versioning):

   • Both Alice and Bob read Seat 10 simultaneously → available.

   • Alice books first → database updates the row with a version number.

   • Bob tries to book → version mismatch detected → booking fails.

Result: Only one person successfully books the seat. This prevents double booking and ensures data integrity.

Conclusion

Concurrency is a fundamental concept in database-driven applications. In this article we have seen how understanding how concurrency works is essential for building robust .NET applications.