Multiple users or services frequently attempt to read or edit the same database record or table at the same time in real-world applications.

• Who gets to provide the initial update?
• How is conflict avoided by the system?
• What happens if there are several updates at once?

In order to create dependable and consistent applications, this subject is often covered in.NET interviews. In this post, we provide a straightforward explanation of concurrency ideas and use a ticket booking scenario to demonstrate them.

Knowing Concurrency in Databases
When several processes access the same data at the same time, concurrency occurs. Race circumstances and missing updates may result from improper handling. Databases use versioning, locks, and transactions to manage concurrency. The two primary methods that databases employ to manage concurrent changes are briefly described below.

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:
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.

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.