Case Study 4.1: Transactions in the Real World

Real-World Concurrency Reading Time: 15 mins

Let's cut to the chase: concurrency control and ACID guarantees aren't just theoretical exercises. They're the pragmatic solutions that keep data anomalies from turning your system into a circus.

1. Atomicity: Concert Ticket Purchases

When a major concert's tickets go live, the system is bombarded with requests. Imagine this: 2 tickets remain, and a user hits "Purchase 2 tickets."

Now, if the credit card is charged but the database connection times out before assigning the tickets, you've got a problem. The user is charged but left empty-handed.

ACID Focus: Atomicity Guarantee

Atomicity: All-or-Nothing Execution

The Problem: Payment goes through but inventory update fails → Customer charged with no tickets

ACID Solution: All 5 operations (inventory check, payment, inventory update, order creation, ticket generation) execute as one atomic unit

Guarantee: Either User1 gets both tickets and is charged, OR transaction fails completely and nothing changes

2. Isolation: High-Concurrency Ticket Scalping

Consider a policy limiting users to 7 tickets. A user with 4 tickets tries to outsmart the system by opening two browser sessions to buy 3 more tickets each.

Without isolation, both sessions read the database, see 4 tickets, and proceed, breaching the limit and allowing 10 tickets in total.

ACID Focus: Isolation Protection

Isolation: Preventing Concurrent Interference

The Problem: Without isolation, both browser tabs read "4 tickets" simultaneously and both transactions succeed, giving Sarah 10 tickets

ACID Solution: Database serializes access to Sarah's ticket count - only one transaction can read/update at a time

Guarantee: Business rule (max 7 tickets) is always enforced, even under high concurrency

Race Condition Prevented:

Tab 1 completes first → Sarah has 7 tickets
Tab 2 sees updated count → Purchase rejected (would exceed limit)

3. Consistency: Financial Transfers

In banking, it's 11:59:59 PM. A user transfers $100 while a batch job calculates interest. If the job reads the recipient's balance before the sender's is debited, interest is calculated on phantom money.

ACID Focus: Consistency Guarantee

✅ Consistency: Money Conservation Principle

The Problem: Interest calculation might read Bob's inflated balance ($200) before Alice's balance is reduced, creating money from nowhere

ACID Solution: Isolation ensures transactions don't interfere - either transfer completes fully before interest, or vice versa

Guarantee: Total money in the system always remains constant (Alice's loss = Bob's gain)

Business Rule Enforced: No money can be created or destroyed - only transferred between accounts

4. Isolation: Preventing Overselling

During a sales event, 1,000 customers click "Buy Now" for the last unit of an item. Without isolation, all transactions might see quantity >= 1, leading to overselling and chaos in inventory records.

ACID Focus: Isolation Protection

Isolation: Preventing Overselling

The Problem: 1,000 customers all see "1 available" and complete purchases, causing massive overselling

ACID Solution: Database serializes inventory checks - only one transaction can read/update quantity at a time

Guarantee: Exactly one customer gets the Labubu, remaining 999 get immediate "sold out" message

Race Condition Prevented: First transaction completes, decrements quantity to 0. All others fail the `quantity >= 1` check.

5. Consistency: Atomic Counters

In social media, a viral post can get thousands of likes in seconds. If 10,000 threads read like_count = 500 and write back 501, the post shows 501 likes, not 10,000. This "Lost Update" problem skews metrics.

ACID Focus: Consistency Guarantee

✅ Consistency: Accurate Engagement Metrics

The Problem: 10,000 simultaneous likes cause lost updates - like_count shows 8,427 but Likes table has 10,000 records

ACID Solution: Atomic execution ensures like record creation and counter increment happen together

Guarantee: Posts.like_count always equals COUNT(*) from Likes table

Business Impact: Accurate metrics for influencer payments and advertiser trust

Summary: Transaction Patterns

Common Transaction Patterns

Inventory + Payment + Booking: Risks double-selling or payment/inventory mismatch if not atomic and isolated.
Credit / Debit Transfers: Risks creating or destroying money if consistency isn't strictly enforced.
Check-Reserve-Confirm: Risks overselling if multiple threads read availability simultaneously without isolation.
Read-Validate-Update: Standard pattern for business rule enforcement across multiple tables.

Key Transaction Patterns

Check-Reserve-Confirm: Common for inventory and booking systems

Multi-Table-Atomic: Coordinating updates across related tables

Read-Validate-Update: Standard pattern for business rule enforcement

ACID Benefits

Atomicity: All operations succeed together or fail together

Consistency: Business rules always enforced

Isolation: Concurrent transactions don't interfere

Durability: Committed changes survive system failures