Back to all posts
Concurrency PostgreSQL Redis System Design

How Race Conditions Can Still Happen Even After Wrapping in a DB Transaction and Row-Level Locking

Transactions and SELECT ... FOR UPDATE solve the race condition everyone learns about first. They don't solve all of them. Here's where they fall short, and why the real fix is layering idempotency keys with cache-based cooldown locks — not picking one.

Take a fairly ordinary fintech backend: a wallet service that moves money between users. The classic race condition here is two concurrent transfer requests both reading the same wallet balance and both succeeding, overdrawing the account. The textbook fix is to wrap the debit in a transaction and take a row lock on that specific wallet row — either an explicit SELECT ... FOR UPDATE, or a single atomic UPDATE ... WHERE balance >= amount. For that specific problem — two writers racing to mutate the same, already-existing row — it works. It's also the point where a lot of developers stop thinking about concurrency, because the obvious bug is gone and the logs look clean.

Then, weeks later, production shows a duplicate transfer, a double-charged wallet, or a bonus credited twice to the same user — inside a codebase that already wraps the write in a transaction with proper row locking. The instinct is to assume the lock "didn't work." It worked exactly as designed. The race just happened somewhere the lock was never watching.

What a Transaction and Row Lock Actually Cover

SELECT ... FOR UPDATE and an atomic UPDATE guarantee one thing precisely: once a row exists, no second transaction can read-and-mutate it out from under the first. That guarantee has a boundary, and the boundary is exactly where most "impossible" race conditions in production actually live:

  • There's no row to lock yet. A row lock only protects a row that already exists. If the race is "should this row be created at all" — first-time signup bonus, first wallet-funding webhook for a reference, first transfer for an idempotency key — two concurrent transactions can both run their "does this exist?" check, both see nothing, and both proceed to INSERT. There was never a row for either of them to lock.
  • The race crosses transaction or connection boundaries. A lock held inside transaction A is invisible to a process that hasn't started transaction B yet — a queue worker, a webhook retry, a separate microservice hitting its own database. If the "same" logical operation can be triggered from more than one entry point, a lock scoped to one database connection doesn't stop the others.
  • The side effect lives outside the transaction. Calling a payment gateway, sending an SMS, or dispatching an email inside (or right after) a locked transaction means the lock protects your database row, but not the external call. If the transaction commits, the lock releases, and then a retry or a duplicate request arrives — the row is already "safe," but the external side effect can still fire twice.
  • The retry doesn't even reach the row. Flaky networks, client double-clicks, and "the response timed out so the client retried" are the most common real-world triggers — and they often show up as two separate requests hitting two separate database transactions, both perfectly well-behaved, both racing to create the same logical thing a few milliseconds apart.

The First Instinct: An Idempotency Key

This is usually where an idempotency key comes in, and it's the right instinct — a client attaches a unique key to a logical operation, the server remembers which keys it has already processed, and a retried request returns the original result instead of doing the work again. The catch: a naive implementation just moves the exact same race condition up one layer, from the wallet row to the idempotency-key row.

Idempotency check — the broken version 
// This "check, then act, then record" sequence has the
// exact same gap as the original overselling bug — it's
// just one layer further out, on the idempotency key.

const existing = await pool.query(
  'SELECT transfer_id FROM idempotency_keys WHERE key = $1',
  [key]
);

if (existing.rows.length > 0) {
  return res.json({ transferId: existing.rows[0].transfer_id });
}

// Two requests with the same key can both reach here before
// either one has written a row to idempotency_keys. Both
// proceed. Both execute the transfer.
const transfer = await transferService.processTransfer(fromWalletId, toWalletId, amount);

await pool.query(
  'INSERT INTO idempotency_keys (key, transfer_id) VALUES ($1, $2)',
  [key, transfer.id]
);

The fix for this layer is the same principle as before: collapse the check and the claim into one atomic operation, and let the database enforce uniqueness instead of your application logic. In practice that means a UNIQUE constraint on the key column, an optimistic insert, and handling the constraint violation as the "already processed" branch — not a separate read beforehand.

Idempotency check — insert-first, race-free 
// idempotency_keys.key has a UNIQUE constraint. Claiming the
// key IS the atomic operation — there is no read-then-write
// gap left for a second request to slip through.
try {
  await pool.query(
    'INSERT INTO idempotency_keys (key, status) VALUES ($1, $2)',
    [key, 'processing']
  );
} catch (err) {
  if (isUniqueViolation(err)) {
    // Someone already claimed this key — poll/return its result
    return await waitForExistingResult(key);
  }
  throw err;
}

const transfer = await transferService.processTransfer(fromWalletId, toWalletId, amount);

await pool.query(
  'UPDATE idempotency_keys SET status = $1, transfer_id = $2 WHERE key = $3',
  ['done', transfer.id, key]
);

This closes the gap for the case where the client cooperates — it generates one key per logical action and sends that same key on every retry. In the real world, that assumption breaks more often than you'd like:

  • A buggy or third-party client generates a new key on every retry instead of reusing one.
  • There's no key at all — a GET-like "claim my daily bonus" button with no client-supplied identifier.
  • A user genuinely double-clicks, or opens two tabs, firing two independent requests that were never going to share a key because they were never the same client-side attempt.
  • A flood of identical requests arrives faster than a single INSERT and its transaction commit can complete, so you still take real load on the database for every duplicate before the constraint ever gets a chance to reject one.

An idempotency key is a durability guarantee for a specific, cooperating client replaying a specific action. It isn't a general-purpose brake on request volume, and it doesn't protect you from the simplest case of all: the same user (or the same button) just being clicked more than once, with or without a key attached.

The Missing Layer: A Cache-Based Cooldown Lock

This is where a fast, cheap, outer guard earns its keep — a short-lived lock in Redis (or equivalent) that sits in front of the database entirely. The rule is deliberately simple: no matter how many requests arrive for a given action, accept the first one and reject every other one for a cooldown window, regardless of whether they carry an idempotency key, regardless of whether they're "legitimate" retries, and regardless of count — two, ten, or a thousand concurrent attempts all collapse to one winner.

The mechanism is a single atomic SET key value NX EX ttl — "set this key only if it doesn't already exist, and expire it automatically after ttl seconds." Redis executes that as one indivisible command, so there is no check-then-set gap to race against, even under genuinely simultaneous requests:

CooldownLock 
import { Redis } from 'ioredis';

export class CooldownLock {
  constructor(private readonly redis: Redis) {}

  // Returns true only for the request that wins the race.
  // Every other caller — no matter how many, no matter why —
  // gets false and must be rejected, not queued, not retried.
  async tryAcquire(scope: string, ttlSeconds = 5): Promise<boolean> {
    const lockKey = `cooldown:${scope}`;

    const result = await this.redis.set(lockKey, '1', 'EX', ttlSeconds, 'NX');
    return result === 'OK';
  }
}
Initiate transfer endpoint 
// Scope the lock to the specific user + action, not globally —
// one user's cooldown should never block another user's request.
const scope = `user:${userId}:initiate-transfer`;

if (!(await cooldownLock.tryAcquire(scope, 5))) {
  res.status(429).json({ error: 'Request already in progress, try again shortly.' });
  return;
}

// Only the single request that acquired the lock gets here.
// The DB transaction + unique constraint below are the second,
// durable line of defense — not a replacement for this one.
try {
  const result = await transferService.processTransfer(fromWalletId, toWalletId, amount);
  res.status(201).json(result);
} catch (err) {
  res.status(500).json({ error: 'Failed to process transfer.' });
}

Three details make this pattern work rather than just move the problem again:

  • Scope the key narrowly. Lock on user + action (or resource-id + action), never on the action alone — a global lock on "initiate-transfer" would serialize every user in the system against each other, which is a self-inflicted outage, not a concurrency fix.
  • Set the TTL above your worst-case processing time, not below it. If the lock expires while the original request is still mid-flight, a second request can slip in and the cooldown did nothing. Measure your p99 for the protected operation and pad it.
  • Reject, don't queue. The point of a cooldown is to shed duplicate work immediately and cheaply. Queueing rejected requests to "process later" reintroduces the same race one step downstream — it just delays it.

Layering the Defenses, Not Choosing One

None of these three mechanisms replace each other — they cover different failure modes, and production systems need all three stacked, outermost to innermost:

  1. Cache cooldown lock (edge of the request). Cheap, fast, rejects duplicate or flooding requests before they ever touch the database — regardless of whether a key was supplied, regardless of how many copies of the request arrived.
  2. Idempotency key with a unique constraint (durability boundary). Survives Redis restarts, lock expiry, and legitimate retries that arrive after the cooldown window has passed — gives a cooperating client a correct, replayable result instead of a duplicate side effect.
  3. Transaction + row-level lock (data integrity boundary). Guarantees that once work reaches the database, the actual rows being mutated can't be corrupted by a second concurrent writer — the baseline guarantee described earlier in this post.

Remove the outer layer and you're back to taking full database load for every duplicate, client bug, or double-click, relying entirely on a unique constraint to clean up after the fact. Remove the middle layer and a legitimate retry that arrives a few seconds late — after the cooldown expired — duplicates the side effect. Remove the inner layer and you're trusting application code, not the database, to serialize writes to a row that multiple processes can still reach directly.

A row lock protects a row. An idempotency key protects a cooperating client's retry. A cooldown lock protects against volume and misbehaving clients. None of the three is "the" fix for race conditions — they're different layers of the same defense, and the bugs that reach production are almost always the layer nobody added.

What I'd evaluate next

  • Whether the cooldown lock needs to survive a single Redis node failing — i.e. whether the operation is sensitive enough to warrant a multi-node consensus lock instead of a single instance.
  • Structured logging on every lock rejection, so a spike in 429s from this path is visible as a signal, not just silently dropped traffic.
  • Whether the idempotency key table needs its own TTL/cleanup job so it doesn't grow unbounded for high-volume, short-lived actions.

If you're debugging a "but it's already in a transaction" race condition in your own system, or designing the concurrency model for a new one, get in touch — happy to talk through the specific layers your case needs.

More engineering notes Contact me