Race Conditions in Production: The Concurrency Pattern That Exhausts Your Database Connection Pool

Managing distributed state, preserving data integrity, and avoiding database bottlenecks under load.

The shift from building features to operating systems is often defined by concurrency.

Every engineer knows the basic definition of a race condition: a read-modify-write cycle executed by parallel processes that results in corrupt state. The textbook solution is simple: lock the row.

But under load, the textbook solution can become the next bottleneck in the system.

The familiar version looks like this: a queue worker picks up the same job twice, or two requests hit the same inventory record within milliseconds. The data corruption is obvious. The less obvious part is what happens after the fix. A row lock gets added, the incident appears closed, and a few weeks later the system starts stalling under load because too many transactions are now waiting on the same contested path.

Past a certain traffic level, concurrency stops being a narrow correctness problem. It becomes a trade-off between consistency, throughput, and recovery behavior. Here is the framework for handling it when row locks are no longer enough.

The Illusion of READ COMMITTED

Most relational databases such as PostgreSQL and MySQL default to the READ COMMITTED isolation level. This means a query only sees data that was committed before the query began.

For most application reads, this is a reasonable default. It is fast and prevents dirty reads.

But for critical mutations, such as inventory countdowns or wallet deductions, READ COMMITTED is exactly where race conditions can appear. Two parallel transactions can both read the committed value of 1, and both can blindly write 0, unaware of each other.

To fix this, the immediate instinct is to reach for the easiest tool: pessimistic locking.

The Pessimistic Locking Trap

Pessimistic locking with SELECT ... FOR UPDATE is a common defense against race conditions. It tells the database engine to lock the row during the read phase and block other transactions until the current one commits.

It gives strong protection against concurrent writes on the contested row. But it comes with a cost.

When a row is locked, a database connection stays open for the duration of the transaction. If that transaction includes a network call to a third-party service such as a payment gateway or email provider, that connection can sit open far longer than the original mutation itself.

Under sudden load, like a webhook storm or a burst of batch jobs, the application may open many simultaneous connections that end up waiting on the same lock. That is how an isolated race-condition fix can turn into connection pool pressure and Too many connections errors.

The corruption is gone. The bottleneck moves somewhere else.

Scaling Throughput with Optimistic Locking

When lock contention becomes the bigger risk, the next option is often optimistic locking.

Instead of locking the row at the database level, concurrency resolution moves up to the application layer. A version integer column is added to the table.

How Version Control Prevents Corruption:

1. Process A reads the row. Version = 1.
2. Process B reads the row. Version = 1.
3. Process A modifies the data and issues an update: UPDATE table SET ..., version = 2 WHERE id = X AND version = 1. This succeeds.
4. Process B issues the same update: UPDATE table SET ..., version = 2 WHERE id = X AND version = 1.

Because Process A already changed the version to 2, Process B affects zero rows.

The application detects the zero-row update and throws a StaleObjectException. The benefit is that the database does not need to hold the same contested lock through the whole operation. The trade-off is that the application now needs a clean retry path and explicit conflict handling.

The Boundary Problem: Idempotency

Race conditions do not stop at the database. They also appear at system boundaries.

If a user double-clicks a payment button, or a webhook provider aggressively retries a payload, the same destructive action can run twice. Neither optimistic nor pessimistic locking addresses that cleanly on its own.

At the boundary, the more reliable control is usually idempotent API design.

Implementing the Idempotency-Key Header:
Every critical mutation request includes a unique Idempotency-Key header generated by the client. The application stores this key in a fast distributed store such as Redis.

• If Request A arrives, the key is logged and the payment processes.
• If Request B arrives 10 milliseconds later with the same key, the system recognizes the duplicate and returns the cached response from Request A.

Idempotency does not remove concurrency. It contains the effects of retries and duplicate delivery.

Distributed Cache Locks

Sometimes distributed worker nodes need to execute a long-running process, and two workers cannot be allowed to run it at the same time. A database lock is usually the wrong tool here because the process may take minutes.

This is where teams often reach for a distributed cache lock such as Redis.

But a simple SETNX is not enough. If a worker acquires a lock and then crashes, the lock may remain in place and stall the system.

Whatever locking mechanism is used, it needs explicit expiry behavior, safe release semantics, and failure handling that has been tested under worker crashes.

Closing Thought

Concurrency is not a bug to patch away. It is the normal operating condition of a successful, high-traffic system.

The hard part is not picking a lock. The hard part is understanding which failure mode the lock removes, which new bottleneck it introduces, and where the boundary controls belong.

Concurrency Guardrails

• Pessimistic Locking: Best for short, tightly bounded critical sections where the lock duration is predictable.
• Optimistic Locking: Useful when contention exists but holding database locks through the full operation would be more expensive than retrying conflicts.
• Idempotency: Important at system boundaries such as APIs, webhooks, and message consumers where retries are normal behavior.
• Distributed Locks: Reserve for singleton work that genuinely cannot run twice, and only with tested expiry and crash-recovery semantics.

What has shaped your thinking on the relationship between data consistency and system availability? Share in the comments.