The Art of Traffic Control: A Deep Dive into Database Concurrency

Imagine a busy bank with thousands of people trying to withdraw and deposit money at the exact same second. If two people access the same account at the same time, who gets the money? Does the balance stay correct?

In the world of databases, this is Concurrency Control. It’s the set of rules that ensures that even when thousands of users are “touching” the same data at once, the result is consistent — as if they were standing in a single, orderly line.

The Nightmares of Concurrency: Common Problems

Before we fix it, we need to know what goes wrong. Let’s look at the four big “anomalies” that trip up developers.

1. The Lost Update

The Example: You have $\$100$.

• User A reads $\$100$ and wants to add $\$50$.
• User B reads $\$100$ at the same time and wants to add $\$30$.
• User A saves $\$150$.
• User B saves $\$130$ a millisecond later.
• Result: The $\$50$ from User A is gone forever. This is a Lost Update.

2. Dirty Reads

The Example: User A transfers $\$50$ to User B.

• The DB subtracts $\$50$ from A (Balance: $\$50$).
• Before the transaction “Commits” (saves), User C reads User A’s balance as $\$50$.
• The transfer fails and “Rolls back.” User A’s balance is actually still $\$100$.
• Result: User C made a decision based on “dirty” (uncommitted) data that never actually happened.

Non-Repeatable Read

The Example: You check your balance twice in one minute.

• Read 1: You see $\$100$.
• While you are still looking, a background bill-pay takes out $\$20$.
• Read 2: You see $\$80$.
• Result: In the same transaction, you got two different values for the same row.

Phantom Read

The Example: You run a report for “Users with balance $>$ $\$1000$.”

• Query 1: Returns 5 people.
• While the report is generating, a new user joins with $\$2000$.
• Query 2: Suddenly returns 6 people.
• Result: The new user is a “phantom” — they appeared out of thin air mid-process.

The Solutions: How Databases Keep the Peace

1. Locks: Shared vs. Exclusive

This is the most intuitive solution.

• Shared Lock (S): “I’m just reading. Others can read too, but no one can change this.”
• Exclusive Lock (X): “I’m writing. No one else can read or write until I’m done.”

Pro Tip for Interviews: Mention Lock Granularity.

• Table-level locking locks the whole table (Great for bulk updates, bad for concurrency).
• Row-level locking locks only the specific line (High concurrency, but uses more memory/overhead).

Two-Phase Locking (2PL)

2PL ensures “Serializability” (making things happen in a safe order). It has two phases:

1. Growing Phase: The transaction acquires all the locks it needs but cannot release any.
2. Shrinking Phase: The transaction releases locks but cannot acquire any new ones.

3. Timestamp Ordering

Instead of locking, every transaction gets a unique “Timestamp” (like a ticket at a deli). If Transaction A started before Transaction B, the DB ensures A’s operations happen “logically” before B’s. If B tries to write something that A should have seen, B is aborted and restarted.

4. MVCC (Multi-Version Concurrency Control)

This is the “Golden Standard” for modern high-performance databases. Instead of locking a row, the DB creates versions of the data.

• When you update a row, the DB keeps the old version for people who are still reading.
• Benefit: Readers never block writers, and writers never block readers!

When to prefer which?

• Choose Locking/2PL when data integrity is so critical that you’d rather wait than risk a versioning conflict (e.g., highly sensitive financial clearing).
• Choose MVCC for almost all modern web apps. It allows your “Read” users (browsing products) to stay fast even while “Write” users (buying products) are updating the same tables. The Ultimate Interview Case Study: The Seat Booking Dilemma

To ace your interview, you need to show you can apply these concepts to real-world architecture. Expect a question like this:

Interviewer: “You are designing a ticket booking system like Ticketmaster or a flight reservation app. Thousands of users are hitting the system simultaneously to book the exact same front-row seat. Would you use MVCC or row-level locking?”

While MVCC is the modern standard for general web applications because it prevents readers from blocking writers, a seat booking system has a fundamentally different problem: extreme write-write conflicts.

If 50 people try to purchase Seat 4A at the same time, MVCC will create 50 different historical versions of that seat row. Eventually, 49 of those transactions will fail during the commit phase because the first one succeeded, leading to massive retries, wasted database CPU cycles, and potential race conditions.

For a movie theater, train, or flight booking system where correctness is absolutely critical and data collision is guaranteed, Pessimistic Row-Level Locking or Application-Level Optimistic Locking is the superior choice.

How to Implement It in Code

In a real interview, you can seal the deal by writing out the exact SQL strategy:

-- Step 1: Find the seat and lock the row immediately so no one else can touch it
SELECT seat_id, status 
FROM seats 
WHERE flight_id = 'FL123' AND seat_number = '4A' AND status = 'AVAILABLE'
FOR UPDATE;

-- Step 2: If the row is returned, process the payment and update the status
UPDATE seats 
SET status = 'BOOKED', user_id = 999 
WHERE flight_id = 'FL123' AND seat_number = '4A';

The Reasoning:

1. **FOR UPDATE (Pessimistic Row Locking):** The moment User 1 hits the SELECT query, the database places an exclusive row-level lock on Seat 4A.
2. The Queue: Users 2 through 50 do not get an older “version” of the seat to look at; instead, their database connections are forced to pause and wait in line until User 1’s transaction either completes (commits) or fails (rolls back).
3. The Result: Zero chance of double-booking, predictable database performance under a spike, and absolute data consistency where it matters most.

Stay tuned for more such articles……………..