When a Race Condition Caught Me Off Guard: A Real-Life Developer Story

Race conditions are tricky beasts, especially in high-transaction systems. I encountered one recently while working on a system handling user transactions, and it led to inconsistencies, failed reports, and a lot of debugging. Here’s my journey—what went wrong, what I tried, and how we finally solved it.

The Situation

We had two tables:

• Transactions - stores individual user transactions.
• Reports - aggregates transaction data for reporting.

Multiple processes were updating these tables concurrently. Sometimes the balance was deducted but reports weren’t updated, and sometimes the order was reversed, causing mismatched data. Classic race condition.

---

Initial Approach: Entity Framework Updates

Initially, we were using Entity Framework to read, modify, and save balances:

// EF Core update
var balance = _context.Accounts.Find(accountId);
balance.Amount -= transactionAmount;
await _context.SaveChangesAsync();

This worked in development, but under high concurrency, multiple threads read the same balance simultaneously, overwriting each other’s changes.

---

Method 1: Optimistic Concurrency Control

We added a RowVersion column and let EF Core detect conflicts:

// EF Core optimistic concurrency
var account = await _context.Accounts.FindAsync(accountId);
_context.Entry(account).OriginalValues["RowVersion"] = account.RowVersion;

account.Amount -= transactionAmount;

try
{
    await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
    // Conflict detected: retry or alert
}

This helped catch conflicts, but in high-traffic operations, retries became frequent, and performance degraded.

---

Method 2: Pessimistic Concurrency / Row-Level Locking

We tried database-level locks using SQL hints:

SELECT * 
FROM Accounts WITH (UPDLOCK, ROWLOCK)
WHERE AccountId = @accountId

This ensured only one transaction could modify the row at a time. It solved the conflict but reduced throughput under heavy load, as other threads had to wait.

---

Method 3: Queuing Transactions

We explored queue-based processing for high-contention updates:

• Incoming requests were queued.
• Worker service processed them sequentially.

This completely eliminated race conditions but added complexity and latency for low-priority operations.

---

Method 4: Using Stored Procedures for Balance Updates

Finally, we moved balance updates to a **database stored procedure**, combining the deduction and report update in a single atomic operation:

CREATE PROCEDURE UpdateBalanceAndReport
    @AccountId INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    BEGIN TRANSACTION;

    -- Deduct balance
    UPDATE Accounts
    SET Amount = Amount - @Amount
    WHERE AccountId = @AccountId;

    -- Insert into report
    INSERT INTO Reports(AccountId, Amount, CreatedAt)
    VALUES(@AccountId, @Amount, GETDATE());

    COMMIT TRANSACTION;
END

Now, even if multiple threads call this procedure simultaneously, the database ensures **atomicity and isolation**. The balance and report tables stay consistent without manual retries or locks in the application layer.

---

Lessons Learned

• Concurrency issues can silently corrupt data if not handled carefully.
• Optimistic concurrency works well for low-conflict scenarios but can cause retries under heavy load.
• Pessimistic locks ensure correctness but may affect performance.
• Queue-based processing is effective but adds latency and complexity.
• Stored procedures with transactions are often the most reliable method for high-volume updates.

---

Conclusion

Race conditions taught me that sometimes the best solution is **not just application logic**, but using the **right database features**. Moving critical balance updates to stored procedures ensured consistency, reliability, and performance in our system.

Handling concurrency requires understanding both **your application’s architecture** and **database capabilities**. If you’re building high-transaction systems with .NET, React, Angular, or Python, always test for race conditions under load and consider atomic operations at the database level.

Have you faced similar concurrency challenges? Share your experience in the comments below!